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 promise this will be the last post for a while to include a 
About
