Results 1 to 2 of 2

Thread: Permissions

  1. #1
    Steffi Guest

    Permissions

    When I restore a database, I lose login permission and role permissions. I end up going
    into the login and unselect and reselect the database to make sure that the login works
    with the database. Is there a way to get around this??

    One other question:: I have added 65 new tables to a database and want to give select
    only to all logins to those specific table -- is there an easy way of doing this???

  2. #2
    Adam Brown Guest

    Permissions (reply)

    Steffi,
    When you drop the user from the database you lose all the permissions associated with that user. Instead of dropping and adding all the logins and roles put this procedure in the master database (or somewhere else) and run it. It will remapp the sysusers table in the database to match the uid in the syslogins table. This will keep the permissions for the users and I believe for your roles too because they are also stored in the sysusers table.

    CREATE PROCEDURE proc_fix_database1_logins
    AS
    BEGIN
    BEGIN
    EXEC sp_configure 'allow updates', 1
    END
    BEGIN
    RECONFIGURE WITH OVERRIDE
    END

    BEGIN
    UPDATE database1.dbo.sysusers
    SET database1.dbo.sysusers.sid = sl.sid
    FROM database1.dbo.sysusers su JOIN master.dbo.syslogins sl
    ON su.name = sl.name
    END

    BEGIN
    EXEC sp_configure 'allow updates', 0
    END
    BEGIN
    RECONFIGURE WITH OVERRIDE
    END
    END

    In answer to the second part of your question I believe there is a really good article on the sp_MSforeachtable procedure that will help you grant select to a ton of tables. Or you can create a cursor by date in the sysobjects table and use that to grant select to everyone.

    Hope this helps,

    Adam

    ------------
    Steffi at 6/22/00 11:29:06 PM

    When I restore a database, I lose login permission and role permissions. I end up going
    into the login and unselect and reselect the database to make sure that the login works
    with the database. Is there a way to get around this??

    One other question:: I have added 65 new tables to a database and want to give select
    only to all logins to those specific table -- is there an easy way of doing this???

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •