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
No comments:
Post a Comment