update: Check new post SQL Server – Cloning User Rights – updated sp_CloneRights on GitHub
Some times it could happen, that you need to create a new database user, which will have exactly the same right as another existing user.
In ideal scenario, you will have all the necessary rights assigned to a database roles, and then when you create the new user, you simply add the user to appropriate roles to grant all the necessary rights.
This is ideal scenario, which is not always met, especially when you have to manage the server after somebody else, who didn’t used roles for granting rights.
In such scenario a below system stored stored procedure can be very handful.
USE [master]
GO
--============================================
-- Author: Pavel Pawlowski
-- Created: 2010/04/16
-- Description: Copies rights of old user to new user
--==================================================
CREATE PROCEDURE sp_CloneRights (
@oldUser sysname, --Old user from which to copy right
@newUser sysname, --New user to which copy rights
@printOnly bit = 1, --When 1 then only script is printed on screen, when 0 then also script is executed, when NULL, script is only executed and not printed
@NewLoginName sysname = NULL --When a NewLogin name is provided also a creation of user is part of the final script
)
AS
BEGIN
SET NOCOUNT ON
CREATE TABLE #output (
command nvarchar(4000)
)
DECLARE
@command nvarchar(4000),
@sql nvarchar(max),
@dbName nvarchar(128),
@msg nvarchar(max)
SELECT
@sql = N'',
@dbName = QUOTENAME(DB_NAME())
IF (NOT EXISTS(SELECT 1 FROM sys.database_principals where name = @oldUser))
BEGIN
SET @msg = 'Source user ' + QUOTENAME(@oldUser) + ' doesn''t exists in database ' + @dbName
RAISERROR(@msg, 11,1)
RETURN
END
INSERT INTO #output(command)
SELECT '--Database Context' AS command UNION ALL
SELECT 'USE' + SPACE(1) + @dbName UNION ALL
SELECT 'SET XACT_ABORT ON'
IF (ISNULL(@NewLoginName, '') <> '')
BEGIN
SET @sql = N'USE ' + @dbName + N';
IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = @newUser)
BEGIN
INSERT INTO #output(command)
SELECT ''--Create user'' AS command
INSERT INTO #output(command)
SELECT
''CREATE USER '' + QUOTENAME(@NewUser) + '' FOR LOGIN '' + QUOTENAME(@NewLoginName) +
CASE WHEN ISNULL(default_schema_name, '''') <> '''' THEN '' WITH DEFAULT_SCHEMA = '' + QUOTENAME(dp.default_schema_name)
ELSE ''''
END AS Command
FROM sys.database_principals dp
INNER JOIN sys.server_principals sp ON dp.sid = sp.sid
WHERE dp.name = @OldUser
END'
EXEC sp_executesql @sql, N'@OldUser sysname, @NewUser sysname, @NewLoginName sysname', @OldUser = @OldUser, @NewUser = @NewUser, @NewLoginName=@NewLoginName
END
INSERT INTO #output(command)
SELECT '--Cloning permissions from' + SPACE(1) + QUOTENAME(@OldUser) + SPACE(1) + 'to' + SPACE(1) + QUOTENAME(@NewUser)
INSERT INTO #output(command)
SELECT '--Role Memberships' AS command
SET @sql = N'USE ' + @dbName + N';
INSERT INTO #output(command)
SELECT ''EXEC sp_addrolemember @rolename =''
+ SPACE(1) + QUOTENAME(USER_NAME(rm.role_principal_id), '''''''') + '', @membername ='' + SPACE(1) + QUOTENAME(@NewUser, '''''''') AS command
FROM sys.database_role_members AS rm
WHERE USER_NAME(rm.member_principal_id) = @OldUser
ORDER BY rm.role_principal_id ASC'
EXEC sp_executesql @sql, N'@OldUser sysname, @NewUser sysname', @OldUser = @OldUser, @NewUser = @NewUser
INSERT INTO #output(command)
SELECT '--Object Level Permissions'
SET @sql = N'USE ' + @dbName + N';
INSERT INTO #output(command)
SELECT CASE WHEN perm.state <> ''W'' THEN perm.state_desc ELSE ''GRANT'' END
+ SPACE(1) + perm.permission_name + SPACE(1) + ''ON '' + QUOTENAME(SCHEMA_NAME(obj.schema_id)) + ''.'' + QUOTENAME(obj.name)
+ CASE WHEN cl.column_id IS NULL THEN SPACE(0) ELSE ''('' + QUOTENAME(cl.name) + '')'' END
+ SPACE(1) + ''TO'' + SPACE(1) + QUOTENAME(@NewUser) COLLATE database_default
+ CASE WHEN perm.state <> ''W'' THEN SPACE(0) ELSE SPACE(1) + ''WITH GRANT OPTION'' END
FROM sys.database_permissions AS perm
INNER JOIN
sys.objects AS obj
ON perm.major_id = obj.[object_id]
INNER JOIN
sys.database_principals AS usr
ON perm.grantee_principal_id = usr.principal_id
LEFT JOIN
sys.columns AS cl
ON cl.column_id = perm.minor_id AND cl.[object_id] = perm.major_id
WHERE usr.name = @OldUser
ORDER BY perm.permission_name ASC, perm.state_desc ASC'
EXEC sp_executesql @sql, N'@OldUser sysname, @NewUser sysname', @OldUser = @OldUser, @NewUser = @NewUser
INSERT INTO #output(command)
SELECT N'--Database Level Permissions'
SET @sql = N'USE ' + @dbName + N';
INSERT INTO #output(command)
SELECT CASE WHEN perm.state <> ''W'' THEN perm.state_desc ELSE ''GRANT'' END
+ SPACE(1) + perm.permission_name + SPACE(1)
+ SPACE(1) + ''TO'' + SPACE(1) + QUOTENAME(@NewUser) COLLATE database_default
+ CASE WHEN perm.state <> ''W'' THEN SPACE(0) ELSE SPACE(1) + ''WITH GRANT OPTION'' END
FROM sys.database_permissions AS perm
INNER JOIN
sys.database_principals AS usr
ON perm.grantee_principal_id = usr.principal_id
WHERE usr.name = @OldUser
AND perm.major_id = 0
ORDER BY perm.permission_name ASC, perm.state_desc ASC'
EXEC sp_executesql @sql, N'@OldUser sysname, @NewUser sysname', @OldUser = @OldUser, @NewUser = @NewUser
DECLARE cr CURSOR FOR
SELECT command FROM #output
OPEN cr
FETCH NEXT FROM cr INTO @command
SET @sql = ''
WHILE @@FETCH_STATUS = 0
BEGIN
IF (@printOnly IS NOT NULL)
PRINT @command
SET @sql = @sql + @command + CHAR(13) + CHAR(10)
FETCH NEXT FROM cr INTO @command
END
CLOSE cr
DEALLOCATE cr
IF (@printOnly IS NULL OR @printOnly = 0)
EXEC (@sql)
DROP TABLE #output
END
GO
EXECUTE sp_ms_marksystemobject 'dbo.sp_CloneRights'
GO
The stored procedure allows copying all the objects and database rights from the old user to a new one. It also clones roles membership for the user.
If the @NewLoginName is specified then then it also creates the @newUser in the database for the login specified and then copies the rights.
@printOnly specifies whether the script should be printed, executed automatically or both printed and executed automatically.
As the system is marked as system, it executes in the context of the database in which is executed.
It also allows copying rights among database roles. If you specify as @oldUser a database or application role name, then the rights of that role will be copied to the @newUser. Again the @newUser can be a user name or database/application role name.
For example if you have a Integration services installed and invoke a below script
USE [msdb]
GO
EXEC sp_CloneRights 'db_ssisadmin', 'NewUser'
you will receive a below script for assigning rights.
--Database Context
USE [msdb]
SET XACT_ABORT ON
--Cloning permissions from [db_ssisadmin] to [NewUser]
--Role Memberships
--Object Level Permissions
GRANT DELETE ON [dbo].[sysssislog] TO [NewUser]
GRANT EXECUTE ON [dbo].[sp_get_dtsversion] TO [NewUser]
GRANT EXECUTE ON [dbo].[sp_make_dtspackagename] TO [NewUser]
GRANT EXECUTE ON [dbo].[sp_add_dtspackage] TO [NewUser]
GRANT EXECUTE ON [dbo].[sp_drop_dtspackage] TO [NewUser]
GRANT EXECUTE ON [dbo].[sp_reassign_dtspackageowner] TO [NewUser]
GRANT EXECUTE ON [dbo].[sp_get_dtspackage] TO [NewUser]
GRANT EXECUTE ON [dbo].[sp_ssis_addlogentry] TO [NewUser]
GRANT EXECUTE ON [dbo].[sp_ssis_listpackages] TO [NewUser]
GRANT EXECUTE ON [dbo].[sp_enum_dtspackages] TO [NewUser]
GRANT EXECUTE ON [dbo].[sp_ssis_listfolders] TO [NewUser]
GRANT EXECUTE ON [dbo].[sp_ssis_deletepackage] TO [NewUser]
GRANT EXECUTE ON [dbo].[sp_ssis_deletefolder] TO [NewUser]
GRANT EXECUTE ON [dbo].[sp_ssis_getpackage] TO [NewUser]
GRANT EXECUTE ON [dbo].[sp_ssis_getfolder] TO [NewUser]
GRANT EXECUTE ON [dbo].[sp_ssis_putpackage] TO [NewUser]
GRANT EXECUTE ON [dbo].[sp_ssis_checkexists] TO [NewUser]
GRANT EXECUTE ON [dbo].[sp_ssis_addfolder] TO [NewUser]
GRANT EXECUTE ON [dbo].[sp_ssis_renamefolder] TO [NewUser]
GRANT EXECUTE ON [dbo].[sp_ssis_setpackageroles] TO [NewUser]
GRANT EXECUTE ON [dbo].[sp_ssis_getpackageroles] TO [NewUser]
GRANT EXECUTE ON [dbo].[sp_log_dtspackage_begin] TO [NewUser]
GRANT EXECUTE ON [dbo].[sp_log_dtspackage_end] TO [NewUser]
GRANT EXECUTE ON [dbo].[sp_log_dtsstep_begin] TO [NewUser]
GRANT EXECUTE ON [dbo].[sp_log_dtsstep_end] TO [NewUser]
GRANT EXECUTE ON [dbo].[sp_log_dtstask] TO [NewUser]
GRANT EXECUTE ON [dbo].[sp_enum_dtspackagelog] TO [NewUser]
GRANT EXECUTE ON [dbo].[sp_enum_dtssteplog] TO [NewUser]
GRANT EXECUTE ON [dbo].[sp_enum_dtstasklog] TO [NewUser]
GRANT EXECUTE ON [dbo].[sp_dump_dtslog_all] TO [NewUser]
GRANT EXECUTE ON [dbo].[sp_dump_dtspackagelog] TO [NewUser]
GRANT EXECUTE ON [dbo].[sp_dump_dtssteplog] TO [NewUser]
GRANT EXECUTE ON [dbo].[sp_dump_dtstasklog] TO [NewUser]
GRANT INSERT ON [dbo].[sysssislog] TO [NewUser]
GRANT REFERENCES ON [dbo].[sysssislog] TO [NewUser]
GRANT SELECT ON [dbo].[sysssislog] TO [NewUser]
GRANT UPDATE ON [dbo].[sysssislog] TO [NewUser]
--Database Level Permissions
Hope you will find this script useful and hope it will save you a lot of work when cloning rights.
It saved me several times, when I come to an existing database with several hundreds of tables with rights assigned on the object level and I had to introduce a new user with exactly the same rights as an existing one.
One thing needs to be mentioned for end. The procedure clones only object and database right. It doesn’t clone right for system objects, assemblies etc., but you can easily extend the procedure to cover also this this.
This procedure is inspired by a script I found in past somewhere on internet.
Like this:
Like Loading...
Pavel's SQL Pages 