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
Pages: 1 2
The script helped me alot and it saved my manual work. Thanks a lot dude.
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!
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
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.