We help IT Professionals succeed at work.

scripting object level permissions

Rainbow002
Rainbow002 asked
on
Hi,
When refreshing databases from different environments, how to retain object level permissions? for example, there are explicity exec permissions for certain user(s) after database is restored to another server, the user name is going to be different how to make sure all the permissions are there using different sql login? This is for SQL server 2000.
Comment
Watch Question

Try to use this:

PRINT 'GENERIC RIGHTS ASSIGNMENT SYSTEM STARTED'
DECLARE @LOGIN AS VARCHAR(100)
SET @LOGIN = 'uApp'

DECLARE @DB AS VARCHAR(100)
DECLARE @QRY AS NVARCHAR(200)
DECLARE @OBJECT AS VARCHAR(100)
DECLARE @OBJTYPE AS VARCHAR(10)

Set @DB=''
Set @QRY=''
SET @OBJECT=''
SET @OBJTYPE=''


      Declare RIGHTSOBJCSR Cursor for
      SELECT [NAME],[TYPE] FROM sys.objects WHERE  type in (N'P', N'PC',N'U')


      OPEN  RIGHTSOBJCSR
      fetch next from RIGHTSOBJCSR into @OBJECT,@OBJTYPE
      WHILE @@FETCH_STATUS=0
      BEGIN

            IF (@OBJTYPE=N'P' OR @OBJTYPE=N'PC')
                  BEGIN
                              
                              PRINT 'PROCEDURE SELCETED FOR RIGHTS ASSIGNING'
                              
                              SET  @QRY='GRANT view definition ON ' + @OBJECT + ' TO ' + @LOGIN
                              EXEC (@QRY)
                              SET @QRY=''
                              PRINT 'PROCEDURE RIGHTS GIVEN'
                              
                  END

            ELSE
                  BEGIN      
                              
                              PRINT 'TABLE SELCETED FOR RIGHTS ASSIGNING'
                              
                              SET  @QRY='GRANT view definition ON ' + @OBJECT + ' TO ' + @LOGIN
                              EXEC(@QRY)
                              SET @QRY=''
                              PRINT 'TABLE RIGHTS GIVEN'
                              
                  END

      fetch next from RIGHTSOBJCSR into @OBJECT,@OBJTYPE

      End
      
      Close RIGHTSOBJCSR
      Deallocate  RIGHTSOBJCSR


PRINT 'GENERIC RIGHTS ASSIGNMENT SYSTEM FINISHED'

Author

Commented:
Doesn't work for sql 2000. I tried replacing sys.objects to sysobjects but still plenty of compilation errors. Please assist.

Author

Commented:
Here's something closer to what I'm looking for:

sp_helprotect @username ='user'

the above lists all permissions that user has in currend database. How can I generate a script so I can change the username and run it on target server where database was restored?

Author

Commented:
More info:  the user in question is windws login which is different on both servers i.e. Server1\user123  and server2\user123 . If I could do something like below I think it would resorlve the issue:

EXEC sp_change_users_login 'Update_One', 'Server1\user123', 'server2\user123'

Author

Commented:
Didn't get additional information I requested.