Feb 27 2007
Scripting Permissions in SQL Server
I shalln’t tell you my exact reasons for discovering this information but believe me when I say it took about an hour of clicking several thousand checkboxes and thinking “there must be a better way of doing this”.
What I was doing was setting permissions on an MS SQL Server database that didn’t have roles set up in it. What I needed was a way to export roles and their permissions from one database and insert them into another database. The “Script Role” feature in SQL Server Management Studio on created a role, it didn’t script the effective permissions of the role.
Luckily I found the How to Script User and Role Object Permissions in SQL Server page on Sql-Server-Performance.com. That provides sql scripts that interrogates an SQL Server database and role/user and generates a script that can be used to set up that role/user in another database.
Obviously it goes without saying that you should be using roles in databases and assigning users to roles at the server level, not the database level, as when start moving databases around the roles will be moved but the users may not exists on the next server. Also, wherever possibly you should be using Windows Authentication and not SQL Server Authentication and you should not be using the SA account account.
Just in case the above site goes down I’ll include the script for export role permissions below:
--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
I have been working on similar. Your script is is a good start but I prefer doing it without all the IF statements. Additionally it needs. In needs to be modified to include the object owners for the GRANT and DENY statements incase there are objects owned by someone other than dbo(SQL 2000) and to include a section for protecttype of 204 (WITH GRANT OPTION). These instances probably do not happen often, but I like scripts to like this to try to handle everything. Just finishing up testing my script now.
That sounds interesting. Will you be posting you script online? If you do please leave a link to it.
Thanks for the script. But I’m sure there is an easier way. Have you heard about the release of security explorer with support of sql server permissions managing ? If I’m not wrong this tool can do the same. Here is a good article in eWeek about it. This solution is able to copy, paste or clone permissions beetween logins, objects and databases without any scripting as written there. I believe it’s true.