Script to Reverse Engineer SQL Server Object User Permissions for all databases

Rainbow002
Rainbow002 used Ask the Experts™
on
Hi,
After restoring databases to another server as part of test migration from SQL 2000 to SQL 2008, I extracted the logins from source server using sp_helprevlogin script from microsoft. MS script apparently ignores default db roles such as db_datareader/db_datawriter etc...

So, I need a way to restore logins that get mapped to the database users exactly as they are in source server. While googling, I came across below script which does the job  from: http://www.sql-server-performance.com/articles/dba/object_permission_scripts_p1.aspx

Can you please help in tweaking it so it loops through all the databases instead of a single database?
-----------------
Script to Reverse Engineer SQL Server Object User Permissions

--Written By Bradley Morris
--In Query Analyzer be sure to go to
--Query -> Current Connection Options -> Advanced (Tab)
--and set Maximum characters per column
--to a high number, such as 10000, so
--that all the code will be displayed.
DECLARE @DatabaseUserName [sysname]
SET @DatabaseUserName = 'DBUSER123'

SET NOCOUNT ON
DECLARE
@errStatement [varchar](8000),
@msgStatement [varchar](8000),
@DatabaseUserID [smallint],
@ServerUserName [sysname],
@RoleName [varchar](8000),
@ObjectID [int],
@ObjectName [varchar](261)

SELECT
@DatabaseUserID = [sysusers].[uid],
@ServerUserName = [master].[dbo].[syslogins].[loginname]
FROM [dbo].[sysusers]
INNER JOIN [master].[dbo].[syslogins]
ON [sysusers].[sid] = [master].[dbo].[syslogins].[sid]
WHERE [sysusers].[name] = @DatabaseUserName
IF @DatabaseUserID IS NULL
BEGIN
SET @errStatement = 'User ' + @DatabaseUserName + ' does not exist in ' + DB_NAME() + CHAR(13) +
'Please provide the name of a current user in ' + DB_NAME() + ' you wish to script.'
RAISERROR(@errStatement, 16, 1)
END
ELSE
BEGIN
SET @msgStatement = '--Security creation script for user ' + @ServerUserName + CHAR(13) +
'--Created At: ' + CONVERT(varchar, GETDATE(), 112) + REPLACE(CONVERT(varchar, GETDATE(), 108), ':', '') + CHAR(13) +
'--Created By: ' + SUSER_NAME() + CHAR(13) +
'--Add User To Database' + CHAR(13) +
'USE [' + DB_NAME() + ']' + CHAR(13) +
'EXEC [sp_grantdbaccess]' + CHAR(13) +
CHAR(9) + '@loginame = ''' + @ServerUserName + ''',' + CHAR(13) +
CHAR(9) + '@name_in_db = ''' + @DatabaseUserName + '''' + CHAR(13) +
'GO' + CHAR(13) +
'--Add User To Roles'
PRINT @msgStatement
DECLARE _sysusers
CURSOR
LOCAL
FORWARD_ONLY
READ_ONLY
FOR
SELECT
[name]
FROM [dbo].[sysusers]
WHERE
[uid] IN
(
SELECT
[groupuid]
FROM [dbo].[sysmembers]
WHERE [memberuid] = @DatabaseUserID
)
OPEN _sysusers
FETCH
NEXT
FROM _sysusers
INTO @RoleName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @msgStatement = 'EXEC [sp_addrolemember]' + CHAR(13) +
CHAR(9) + '@rolename = ''' + @RoleName + ''',' + CHAR(13) +
CHAR(9) + '@membername = ''' + @DatabaseUserName + ''''
PRINT @msgStatement
FETCH
NEXT
FROM _sysusers
INTO @RoleName
END
SET @msgStatement = 'GO' + CHAR(13) +
'--Set Object Specific Permissions'
PRINT @msgStatement
DECLARE _sysobjects
CURSOR
LOCAL
FORWARD_ONLY
READ_ONLY
FOR
SELECT
DISTINCT([sysobjects].[id]),
'[' + USER_NAME([sysobjects].[uid]) + '].[' + [sysobjects].[name] + ']'
FROM [dbo].[sysprotects]
INNER JOIN [dbo].[sysobjects]
ON [sysprotects].[id] = [sysobjects].[id]
WHERE [sysprotects].[uid] = @DatabaseUserID
OPEN _sysobjects
FETCH
NEXT
FROM _sysobjects
INTO
@ObjectID,
@ObjectName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @msgStatement = ''
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 193 AND [protecttype] = 205)
SET @msgStatement = @msgStatement + 'SELECT,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 195 AND [protecttype] = 205)
SET @msgStatement = @msgStatement + 'INSERT,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 197 AND [protecttype] = 205)
SET @msgStatement = @msgStatement + 'UPDATE,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 196 AND [protecttype] = 205)
SET @msgStatement = @msgStatement + 'DELETE,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 224 AND [protecttype] = 205)
SET @msgStatement = @msgStatement + 'EXECUTE,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 26 AND [protecttype] = 205)
SET @msgStatement = @msgStatement + 'REFERENCES,'
IF LEN(@msgStatement) > 0
BEGIN
IF RIGHT(@msgStatement, 1) = ','
SET @msgStatement = LEFT(@msgStatement, LEN(@msgStatement) - 1)
SET @msgStatement = 'GRANT' + CHAR(13) +
CHAR(9) + @msgStatement + CHAR(13) +
CHAR(9) + 'ON ' + @ObjectName + CHAR(13) +
CHAR(9) + 'TO ' + @DatabaseUserName
PRINT @msgStatement
END
SET @msgStatement = ''
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 193 AND [protecttype] = 206)
SET @msgStatement = @msgStatement + 'SELECT,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 195 AND [protecttype] = 206)
SET @msgStatement = @msgStatement + 'INSERT,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 197 AND [protecttype] = 206)
SET @msgStatement = @msgStatement + 'UPDATE,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 196 AND [protecttype] = 206)
SET @msgStatement = @msgStatement + 'DELETE,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 224 AND [protecttype] = 206)
SET @msgStatement = @msgStatement + 'EXECUTE,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 26 AND [protecttype] = 206)
SET @msgStatement = @msgStatement + 'REFERENCES,'
IF LEN(@msgStatement) > 0
BEGIN
IF RIGHT(@msgStatement, 1) = ','
SET @msgStatement = LEFT(@msgStatement, LEN(@msgStatement) - 1)
SET @msgStatement = 'DENY' + CHAR(13) +
CHAR(9) + @msgStatement + CHAR(13) +
CHAR(9) + 'ON ' + @ObjectName + CHAR(13) +
CHAR(9) + 'TO ' + @DatabaseUserName
PRINT @msgStatement
END
FETCH
NEXT
FROM _sysobjects
INTO
@ObjectID,
@ObjectName
END
CLOSE _sysobjects
DEALLOCATE _sysobjects
PRINT 'GO'
END
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Save the script above as a stored proc and use sp_msforeachdb to apply it to all DBs on your server. For instance the following prints the name of all DBs on your server:
EXECUTE sp_msforeachdb 'USE ? PRINT DB_NAME()'

You could also tweack it to only apply it to certain DBs, for instance:
EXECUTE sp_msforeachdb 'USE ?
IF DB_NAME() NOT IN(''master'',''msdb'',''tempdb'',''model'')
BACKUP DATABASE ? TO DISK = ''G:?.bak, WITH INIT'''
G GodwinDatabase Administrator

Commented:
To do this, you need to connect to each database one at a time.
I usually set something like this up to use SQLCMD (formerly isql).  
This is an easy way to loop through the databases of the instance and execute the same sql code on each.  
You can pipe the output to the same file or different files as needed.
-G
G GodwinDatabase Administrator

Commented:
Did you get this going? Do you need anything else?
-G
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Ok here we go, the attached code creates a storedproc called "ShowDBUserPerms" in the master db (you can change it to another DB if you have one, specifically for admin purposes). The stored proc does exactly the same job as the original script that you have attached above - with a minor change in the following line:
@ServerUserName = [master].[dbo].[syslogins].[loginname]
which I had to change to
@ServerUserName = [master].[sys].[logins].[loginname]
to make it work with SQL Server 2008.

After you've created the storeproc, you can call it to document permissions for a given username in all DB's with a script like this - you just need to change AnyUserName with a Database Username of your choice.

sp_msforeachdb 'Use ?
if DB_Name() not in ("master", "msdb", "model", "tempdb")
Exec master.dbo.ShowDBUserPerms ''AnyUserName'''


CREATE PROCEDURE ShowDBUserPerms
  (
    @DatabaseUserName [sysname]
  )
AS 
BEGIN
--Written By Bradley Morris
--In Query Analyzer be sure to go to
--Query -> Current Connection Options -> Advanced (Tab)
--and set Maximum characters per column
--to a high number, such as 10000, so
--that all the code will be displayed.

  SET NOCOUNT ON
  DECLARE @errStatement [varchar](8000),
    @msgStatement [varchar](8000),
    @DatabaseUserID [smallint],
    @ServerUserName [sysname],
    @RoleName [varchar](8000),
    @ObjectID [int],
    @ObjectName [varchar](261)

  SELECT  @DatabaseUserID = [sysusers].[uid], @ServerUserName = [master].[sys].[syslogins].[loginname]
  FROM    [dbo].[sysusers]
          INNER JOIN [master].[dbo].[syslogins]
            ON [sysusers].[sid] = [master].[sys].[syslogins].[sid]
  WHERE   [sysusers].[name] = @DatabaseUserName

  IF @DatabaseUserID IS NULL 
    BEGIN
      SET @errStatement = 'User ' + @DatabaseUserName + ' does not exist in ' + DB_NAME() + '.'
      RETURN
    END
  ELSE 
    BEGIN
      SET @msgStatement = '--Security creation script for user ' + @ServerUserName + CHAR(13) + '--Created At: '
        + CONVERT(VARCHAR, GETDATE(), 112) + REPLACE(CONVERT(VARCHAR, GETDATE(), 108), ':', '') + CHAR(13)
        + '--Created By: ' + SUSER_NAME() + CHAR(13) + '--Add User To Database' + CHAR(13) + 'USE [' + DB_NAME() + ']'
        + CHAR(13) + 'EXEC [sp_grantdbaccess]' + CHAR(13) + CHAR(9) + '@loginame = ''' + @ServerUserName + ''','
        + CHAR(13) + CHAR(9) + '@name_in_db = ''' + @DatabaseUserName + '''' + CHAR(13) + 'GO' + CHAR(13)
        + '--Add User To Roles'
      PRINT @msgStatement
      DECLARE _sysusers CURSOR LOCAL FORWARD_ONLY READ_ONLY
        FOR SELECT  [name]
            FROM    [dbo].[sysusers]
            WHERE   [uid] IN ( SELECT [groupuid]
                               FROM   [dbo].[sysmembers]
                               WHERE  [memberuid] = @DatabaseUserID )
      OPEN _sysusers
      FETCH NEXT FROM _sysusers INTO @RoleName
      WHILE @@FETCH_STATUS = 0
        BEGIN
          SET @msgStatement = 'EXEC [sp_addrolemember]' + CHAR(13) + CHAR(9) + '@rolename = ''' + @RoleName + ''','
            + CHAR(13) + CHAR(9) + '@membername = ''' + @DatabaseUserName + ''''
          PRINT @msgStatement
          FETCH NEXT FROM _sysusers INTO @RoleName
        END
      SET @msgStatement = 'GO' + CHAR(13) + '--Set Object Specific Permissions'
      PRINT @msgStatement
      DECLARE _sysobjects CURSOR LOCAL FORWARD_ONLY READ_ONLY
        FOR SELECT DISTINCT
                    ( [sysobjects].[id] ), '[' + USER_NAME([sysobjects].[uid]) + '].[' + [sysobjects].[name] + ']'
            FROM    [dbo].[sysprotects]
                    INNER JOIN [dbo].[sysobjects]
                      ON [sysprotects].[id] = [sysobjects].[id]
            WHERE   [sysprotects].[uid] = @DatabaseUserID
      OPEN _sysobjects
      FETCH NEXT FROM _sysobjects INTO @ObjectID, @ObjectName
      WHILE @@FETCH_STATUS = 0
        BEGIN
          SET @msgStatement = ''
          IF EXISTS ( SELECT  *
                      FROM    [dbo].[sysprotects]
                      WHERE   [id] = @ObjectID
                              AND [uid] = @DatabaseUserID
                              AND [action] = 193
                              AND [protecttype] = 205 ) 
            SET @msgStatement = @msgStatement + 'SELECT,'
          IF EXISTS ( SELECT  *
                      FROM    [dbo].[sysprotects]
                      WHERE   [id] = @ObjectID
                              AND [uid] = @DatabaseUserID
                              AND [action] = 195
                              AND [protecttype] = 205 ) 
            SET @msgStatement = @msgStatement + 'INSERT,'
          IF EXISTS ( SELECT  *
                      FROM    [dbo].[sysprotects]
                      WHERE   [id] = @ObjectID
                              AND [uid] = @DatabaseUserID
                              AND [action] = 197
                              AND [protecttype] = 205 ) 
            SET @msgStatement = @msgStatement + 'UPDATE,'
          IF EXISTS ( SELECT  *
                      FROM    [dbo].[sysprotects]
                      WHERE   [id] = @ObjectID
                              AND [uid] = @DatabaseUserID
                              AND [action] = 196
                              AND [protecttype] = 205 ) 
            SET @msgStatement = @msgStatement + 'DELETE,'
          IF EXISTS ( SELECT  *
                      FROM    [dbo].[sysprotects]
                      WHERE   [id] = @ObjectID
                              AND [uid] = @DatabaseUserID
                              AND [action] = 224
                              AND [protecttype] = 205 ) 
            SET @msgStatement = @msgStatement + 'EXECUTE,'
          IF EXISTS ( SELECT  *
                      FROM    [dbo].[sysprotects]
                      WHERE   [id] = @ObjectID
                              AND [uid] = @DatabaseUserID
                              AND [action] = 26
                              AND [protecttype] = 205 ) 
            SET @msgStatement = @msgStatement + 'REFERENCES,'
          IF LEN(@msgStatement) > 0 
            BEGIN
              IF RIGHT(@msgStatement, 1) = ',' 
                SET @msgStatement = LEFT(@msgStatement, LEN(@msgStatement) - 1)
              SET @msgStatement = 'GRANT' + CHAR(13) + CHAR(9) + @msgStatement + CHAR(13) + CHAR(9) + 'ON '
                + @ObjectName + CHAR(13) + CHAR(9) + 'TO ' + @DatabaseUserName
              PRINT @msgStatement
            END
          SET @msgStatement = ''
          IF EXISTS ( SELECT  *
                      FROM    [dbo].[sysprotects]
                      WHERE   [id] = @ObjectID
                              AND [uid] = @DatabaseUserID
                              AND [action] = 193
                              AND [protecttype] = 206 ) 
            SET @msgStatement = @msgStatement + 'SELECT,'
          IF EXISTS ( SELECT  *
                      FROM    [dbo].[sysprotects]
                      WHERE   [id] = @ObjectID
                              AND [uid] = @DatabaseUserID
                              AND [action] = 195
                              AND [protecttype] = 206 ) 
            SET @msgStatement = @msgStatement + 'INSERT,'
          IF EXISTS ( SELECT  *
                      FROM    [dbo].[sysprotects]
                      WHERE   [id] = @ObjectID
                              AND [uid] = @DatabaseUserID
                              AND [action] = 197
                              AND [protecttype] = 206 ) 
            SET @msgStatement = @msgStatement + 'UPDATE,'
          IF EXISTS ( SELECT  *
                      FROM    [dbo].[sysprotects]
                      WHERE   [id] = @ObjectID
                              AND [uid] = @DatabaseUserID
                              AND [action] = 196
                              AND [protecttype] = 206 ) 
            SET @msgStatement = @msgStatement + 'DELETE,'
          IF EXISTS ( SELECT  *
                      FROM    [dbo].[sysprotects]
                      WHERE   [id] = @ObjectID
                              AND [uid] = @DatabaseUserID
                              AND [action] = 224
                              AND [protecttype] = 206 ) 
            SET @msgStatement = @msgStatement + 'EXECUTE,'
          IF EXISTS ( SELECT  *
                      FROM    [dbo].[sysprotects]
                      WHERE   [id] = @ObjectID
                              AND [uid] = @DatabaseUserID
                              AND [action] = 26
                              AND [protecttype] = 206 ) 
            SET @msgStatement = @msgStatement + 'REFERENCES,'
          IF LEN(@msgStatement) > 0 
            BEGIN
              IF RIGHT(@msgStatement, 1) = ',' 
                SET @msgStatement = LEFT(@msgStatement, LEN(@msgStatement) - 1)
              SET @msgStatement = 'DENY' + CHAR(13) + CHAR(9) + @msgStatement + CHAR(13) + CHAR(9) + 'ON ' + @ObjectName
                + CHAR(13) + CHAR(9) + 'TO ' + @DatabaseUserName
              PRINT @msgStatement
            END
          FETCH NEXT FROM _sysobjects INTO @ObjectID, @ObjectName
        END
      CLOSE _sysobjects
      DEALLOCATE _sysobjects
      PRINT 'GO'
    END 
END

Open in new window

Author

Commented:
Thanks ProjectChampion but when I creat the above SP on sql 2008 and execute: sp_msforeachdb 'Use ?
if DB_Name() not in ("master", "msdb", "model", "tempdb")
Exec master.dbo.ShowDBUserPerms ''AnyUserName'''
replacing AnyUserName with actual database username, no output is generated....execpt for "Command(s) completed successfully." can you please test?

Also, I'd like this script to work for SQL 2000 so please give me both versions...Thanks in advance!
I'd tested it beore posting and I tested it again by cutting and pasting the code above, all works fine.
The "commands" you're referring to are most likely the scripts generated to recreate the user and grant the existing permissions. Don't forget that you're reverse engineering the existing user's permissions! ; )

Author

Commented:
I'm a bit lost because the SP didn't generate any script to reassign the existing permissions ... can you please share the same script for sql 2000?
James MurrellProduct Specialist

Commented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.

Author

Commented:
Yes please award points to ProjectChampion.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial