How to Script User and Role Object Permissions in SQL Server

Script to Reverse Engineer SQL Server Object Role 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 @DatabaseRoleName [sysname]
–SET @DatabaseRoleName = ‘{Database Role Name}’
SET @DatabaseRoleName = ‘role_name_goes_here’

SET NOCOUNT ON
DECLARE
@errStatement [varchar](8000),
@msgStatement [varchar](8000),
@DatabaseRoleID [smallint],
@IsApplicationRole [bit],
@ObjectID [int],
@ObjectName [sysname]

SELECT
@DatabaseRoleID = [uid],
@IsApplicationRole = CAST([isapprole] AS bit)
FROM [dbo].[sysusers]
WHERE
[name] = @DatabaseRoleName
AND
(
[issqlrole] = 1
OR [isapprole] = 1
)
AND [name] NOT IN
(
‘public’,
‘INFORMATION_SCHEMA’,
‘db_owner’,
‘db_accessadmin’,
‘db_securityadmin’,
‘db_ddladmin’,
‘db_backupoperator’,
‘db_datareader’,
‘db_datawriter’,
‘db_denydatareader’,
‘db_denydatawriter’
)

IF @DatabaseRoleID IS NULL
BEGIN
IF @DatabaseRoleName IN 
(
‘public’,
‘INFORMATION_SCHEMA’,
‘db_owner’,
‘db_accessadmin’,
‘db_securityadmin’,
‘db_ddladmin’,
‘db_backupoperator’,
‘db_datareader’,
‘db_datawriter’,
‘db_denydatareader’,
‘db_denydatawriter’
)
SET @errStatement = ‘Role ‘ + @DatabaseRoleName + ‘ is a fixed database role and cannot be scripted.’
ELSE
SET @errStatement = ‘Role ‘ + @DatabaseRoleName + ‘ does not exist in ‘ + DB_NAME() + ‘.’ + CHAR(13) +
‘Please provide the name of a current role in ‘ + DB_NAME() + ‘ you wish to script.’

RAISERROR(@errStatement, 16, 1)
END
ELSE
BEGIN
SET @msgStatement = ‘–Security creation script for role ‘ + @DatabaseRoleName + CHAR(13) +
‘–Created At: ‘ + CONVERT(varchar, GETDATE(), 112) + REPLACE(CONVERT(varchar, GETDATE(), 108), ‘:’, ”) + CHAR(13) +
‘–Created By: ‘ + SUSER_NAME() + CHAR(13) +
‘–Add Role To Database’ + CHAR(13)
IF @IsApplicationRole = 1
SET @msgStatement = @msgStatement + ‘EXEC sp_addapprole’ + CHAR(13) +
CHAR(9) + ‘@rolename = ”’ + @DatabaseRoleName + ”” + CHAR(13) +
CHAR(9) + ‘@password = ”{Please provide the password here}”’ + CHAR(13)
ELSE
BEGIN
SET @msgStatement = @msgStatement + ‘EXEC sp_addrole’ + CHAR(13) +
CHAR(9) + ‘@rolename ”’ + @DatabaseRoleName + ”” + CHAR(13)
PRINT ‘GO’
END
SET @msgStatement = @msgStatement + ‘–Set Object Specific Permissions For Role’
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] = @DatabaseRoleID
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] = @DatabaseRoleID AND [action] = 193 AND [protecttype] = 205)
SET @msgStatement = @msgStatement + ‘SELECT,’
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseRoleID AND [action] = 195 AND [protecttype] = 205)
SET @msgStatement = @msgStatement + ‘INSERT,’
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseRoleID AND [action] = 197 AND [protecttype] = 205)
SET @msgStatement = @msgStatement + ‘UPDATE,’
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseRoleID AND [action] = 196 AND [protecttype] = 205)
SET @msgStatement = @msgStatement + ‘DELETE,’
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseRoleID AND [action] = 224 AND [protecttype] = 205)
SET @msgStatement = @msgStatement + ‘EXECUTE,’
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseRoleID 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 ‘ + @DatabaseRoleName
PRINT @msgStatement
END
SET @msgStatement = ”
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseRoleID AND [action] = 193 AND [protecttype] = 206)
SET @msgStatement = @msgStatement + ‘SELECT,’
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseRoleID AND [action] = 195 AND [protecttype] = 206)
SET @msgStatement = @msgStatement + ‘INSERT,’
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseRoleID AND [action] = 197 AND [protecttype] = 206)
SET @msgStatement = @msgStatement + ‘UPDATE,’
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseRoleID AND [action] = 196 AND [protecttype] = 206)
SET @msgStatement = @msgStatement + ‘DELETE,’
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseRoleID AND [action] = 224 AND [protecttype] = 206)
SET @msgStatement = @msgStatement + ‘EXECUTE,’
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseRoleID 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 ‘ + @DatabaseRoleName
PRINT @msgStatement
END
FETCH
NEXT
FROM _sysobjects
INTO
@ObjectID,
@ObjectName
END
CLOSE _sysobjects
DEALLOCATE _sysobjects
PRINT ‘GO’
END

Published with the express written permission of the author. Copyright 2002.

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 |