How to Script User and Role Object Permissions in SQL Server

From time to time, I need the ability to “reverse engineer” SQL Server object permissions, for either users or roles, from one database, and then “move” them to another version of the database on another server. For example, I have a development database which developers use to experiment with various Visual Basic applications they write. As they write these apps, I go in and manually set the necessary permissions for these apps to work. Sometimes, this is a trial and error approach, and the permissions evolve over the design and testing of the application. At some point, the developer is ready to role out the app to production, and now I need to “move” the necessary object permissions from the development database to the production database.

While there are third party tools to do this, I didn’t have any handy, so I came up with the following two scripts to do this work for me. The first script is used to reverse engineer object permissions (based on a specific user), and the results of this script are used to recreate the same permissions in a different database. The second script is used to reverse engineer permissions (based on SQL Server database roles), and the results of this script are used to recreate the same permissions on a different 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 = ‘user_name_goes_here’

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

Continues…

Pages: 1 2




Related Articles :

  • No Related Articles Found

7 Responses to “How to Script User and Role Object Permissions in SQL Server”

  1. The script helped me alot and it saved my manual work. Thanks a lot dude.

  2. Both scripts a great.

    Can I offer a formatting suggestion? When you copy your scripts from the web site into Query Analyzer, you have to changes A LOT OF things! The quotes, minus signs, double quotes, spacing get messed up.

    But after I cleaned all of that up, scripts work great!

  3. Here is a fixed version with replacing the user to fix SID issues:

    SET NOCOUNT ON

    DECLARE
    @errStatement [varchar](8000),
    @msgStatement [varchar](8000),
    @DatabaseUserID [smallint],
    @ServerUserName [sysname],
    @RoleName [varchar](8000),
    @ObjectID [int],
    @ObjectName [varchar](261),
    @DBUserName [varchar](255),
    @DatabaseUserName [sysname]

    SET @msgStatement = ‘–Security creation script for users and roles ‘ + CHAR(13) +
    ‘–Created At: ‘ + CONVERT(varchar, GETDATE(), 112) + REPLACE(CONVERT(varchar, GETDATE(), 108), ‘:’, ”) + CHAR(13) +
    ‘–Created By: ‘ + SUSER_NAME() + CHAR(13) + CHAR(13) +
    ‘USE [' + DB_NAME() + ']‘ + CHAR(13)

    PRINT @msgStatement

    DECLARE dbUsers CURSOR
    FOR
    SELECT [name] FROM sys.database_principals WHERE type ‘R’ ORDER BY [name]

    OPEN dbUsers

    FETCH NEXT FROM dbUsers INTO @DBUserName

    WHILE @@FETCH_STATUS = 0
    BEGIN

    SET @DatabaseUserName = @DBUserName

    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 LEN(@ServerUserName) > 0 AND LEN(@DatabaseUserName) > 0
    BEGIN
    SET @msgStatement = ‘IF EXISTS (SELECT name FROM sys.database_principals WHERE name = ”’ + @DatabaseUserName + ”’)
    BEGIN
    EXEC sp_dropuser ”’ + @DatabaseUserName + ”’
    END’ + CHAR(13) + CHAR(13) +
    ‘–Add User To Database’ + CHAR(13) +
    ‘EXEC [sp_grantdbaccess]‘ + CHAR(13) +
    CHAR(9) + ‘@loginame = ”’ + @ServerUserName + ”’,’ + CHAR(13) +
    CHAR(9) + ‘@name_in_db = ”’ + @DatabaseUserName + ”” + CHAR(13) + CHAR(13) +
    ‘–Add User To Roles’
    END

    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
    IF LEN(@RoleName) > 0 AND LEN(@DatabaseUserName) > 0
    BEGIN
    SET @msgStatement = ‘EXEC [sp_addrolemember]‘ + CHAR(13) +
    CHAR(9) + ‘@rolename = ”’ + @RoleName + ”’,’ + CHAR(13) +
    CHAR(9) + ‘@membername = ”’ + @DatabaseUserName + ”” + CHAR(13) +
    CHAR(13) + ‘–Set Object Specific Permissions’

    PRINT @msgStatement
    END

    FETCH NEXT FROM _sysusers INTO @RoleName
    END

    CLOSE _sysusers
    DEALLOCATE _sysusers

    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

    FETCH NEXT FROM dbUsers INTO @DBUserName
    END

    CLOSE dbUsers
    DEALLOCATE dbUsers

  4. Useful stuff. However, in the database role script (possibly the other one as well, I didn’t use that), you need to substitute SCHEMA_NAME() for USER_NAME() when generating the cursors list of objects.

  5. This is really helpful.
    But i have a question, is there any way to get the list together if we have 100′s of users in a database because with the above script we need to enter the user name one by one.

  6. SELECT
    dp.permission_name collate latin1_general_cs_as AS Permission,
    t.TABLE_SCHEMA + ‘.’ + o.name AS Object,
    dpr.name AS Username
    FROM sys.database_permissions AS dp
    INNER JOIN sys.objects AS o ON dp.major_id=o.object_id
    INNER JOIN sys.schemas AS s ON o.schema_id = s.schema_id
    INNER JOIN sys.database_principals AS dpr ON dp.grantee_principal_id=dpr.principal_id
    INNER JOIN INFORMATION_SCHEMA.TABLES t
    ON TABLE_NAME = o.name
    WHERE dpr.name NOT IN (‘public’,’guest’)
    ORDER BY
    Permission, Object,Username

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |