Duplicate Role Permissions by Login

I have had continual requests by people to duplicate someone’s role accross multiple databases on the same server.  It use to be a manual process to look at each user and check the roles and then compare them individually.  I finally got my script together that will create the login, the user, and assign the role if needed.  I know it can look more elegant, but I was going for brute force to get this done.


CREATE TABLE ##permissionsOriginal (
DatabaseName VARCHAR(250)
,Username VARCHAR(250)
,DBRole VARCHAR(250)
)
GO
CREATE TABLE ##permissionsNew (
DatabaseName VARCHAR(250)
,Username VARCHAR(250)
,DBRole VARCHAR(250)
)
GO
DECLARE @Original varchar(40), @New varchar(40), @OriginalSQL varchar(max), @NewSQL varchar(max)
SET @Original = 'User1'
SET @New = 'User2'

SET @OriginalSQL = 'INSERT INTO ##permissionsOriginal ( DatabaseName, UserName, DBRole )
(
SELECT ''[?]'' AS DatabaseName
,u.NAME
,CASE
WHEN (r.principal_id IS NULL)
THEN ''PUBLIC''
ELSE r.NAME
END FROM [?].sys.database_principals u LEFT JOIN (
[?].sys.database_role_members m JOIN [?].sys.database_principals r ON m.role_principal_id = r.principal_id
) ON m.member_principal_id = u.principal_id LEFT JOIN [?].sys.server_principals l ON u.sid = l.sid WHERE u.type <>''R''
AND u.NAME =''' + @Original + '''
)'
EXECUTE sp_msforeachdb @OriginalSQL

SET @NewSQL = 'INSERT INTO ##permissionsNew ( DatabaseName, UserName, DBRole )
(
SELECT ''[?]'' AS DatabaseName
,u.NAME
,CASE
WHEN (r.principal_id IS NULL)
THEN ''PUBLIC''
ELSE r.NAME
END FROM [?].sys.database_principals u LEFT JOIN (
[?].sys.database_role_members m JOIN [?].sys.database_principals r ON m.role_principal_id = r.principal_id
) ON m.member_principal_id = u.principal_id LEFT JOIN [?].sys.server_principals l ON u.sid = l.sid WHERE u.type <>''R''
AND u.NAME =''' + @New + '''
)'

EXECUTE sp_msforeachdb @NewSQL

SELECT
'USE ' + po.DatabaseName + '
GO
DECLARE @userName NVARCHAR(100)
DECLARE @roleName NVARCHAR(100)
DECLARE @cSQL NVARCHAR(max)
SET @userName = ''' + @New + '''
SET @roleName = ''' + po.DBRole + '''
IF NOT EXISTS (SELECT 1 FROM sys.server_principals WHERE NAME = @userName)
BEGIN
SET @cSQL = ''CREATE LOGIN ['' + @userName + ''] WITH PASSWORD = ''''#pkPnWyE60''''''
EXEC sp_executesql @cSQL
END
IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE NAME = @userName)
BEGIN
SET @cSQL = ''CREATE USER ['' + @userName + ''] FROM LOGIN ['' + @userName + '']''
EXEC sp_executesql @cSQL
END
IF (IS_ROLEMEMBER(@roleName, @userName) = 0) EXEC sp_addrolemember @roleName, @userName
GO' as SQLScript
--,po.DatabaseName
--,po.UserName
--,po.DBRole
--,pn.DatabaseName
--,pn.UserName
--,pn.DBRole
FROM ##permissionsOriginal po
left outer join ##permissionsNew pn
on po.DatabaseName = pn.DatabaseName
and po.DBRole = pn.DBRole
WHERE pn.DatabaseName is null
and po.DatabaseName not in ( '[tempdb]', '[model]', '[msdb]' )
GO
DROP TABLE ##permissionsOriginal
GO
DROP TABLE ##permissionsNew
GO

Posted in SQL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow SQL Uber Geek on WordPress.com
Try Audible and Get Two Free Audiobooks
%d bloggers like this: