User Clone Script

One of my favorite subjects in SQL server is security, and one of my favorite security topics is permissions.  This post will cover a script I wrote to recreate a user’s permissions within a database.  I call it my User Clone Script, and use it quite heavily.

Use Cases

  • Troubleshooting Permissions
  • Cloning accounts
  • Switching service accounts
  • Copying permissions between development/test/production environments
  • Refactoring permissions to use a SQL Server Role instead of explicit user permissions
  • Refactoring permissions to use an AD group instead of explicitly named users

Highlights of the Script

This script has support for the following:

  • Most object/column permissions (includes stored procedures, views, tables, and functions)
  • Object ownerships
  • Type permissions
  • Schema permissions
  • Schema ownerships
  • Database permissions
  • Role memberships

 Implementation

In a few separate queries, I dump meta-data into a temp table from the following catalog views:

  1. Database permissions ( sys.database_permissions )
  2. Schema ownerships ( sys.schemas )
  3. Object Ownership ( sys.objects )
  4. Role Memberships ( sys.database_role_members )

Then— and this is the key to unraveling role memberships— I use a Recursive CTE to generate the final result set.

A recursive CTE takes the following form:

;with RecursiveCTE as(

--1) Base/Anchor Member
SELECT
    Columns
FROM #SomeTable
WHERE [ID]=@BaseMemberID

--2) Always a UNION/UNION ALL
UNION ALL

--3) Recursive Member
Select
    Columns
FROM #SomeTable T
INNER JOIN RecursiveCTE RCTE ON T.ParentID=RCTE.ID

)

SELECT * FROM RecursiveCTE;

More information about Recursive CTEs can be found here on TechNet.

Output Sample

The following screenshot shows the output of the script, including commands to copy the permissions of User1 to User2.

PermissionsClone

A couple of key points:

  • If the @NewUser variable is NULL, the script will generate the commands used to grant [User1] the permissions they currently have (to copy permissions to another database, for example).
    • Otherwise, it will replace [User1] with [User2].
  • In this example:
    • [User1] is an immediate and direct member of three roles: [CoolUsers], [Level2] and [Level2a].
    • [CoolUsers] is a member of the [db_owner] fixed role.
    • The [Level2] role is a member of the [Level1] role.
  • The commands output by the script will create the roles, if they do not exist, and assign them the same permissions they had before (for the case that you’re trying to copy the permissions to a different database, for example)
  • If a schema or object ownership is detected, the script will generate the command to transfer ownership to the new user, but it will be commented-out.  This is done as a precautionary measure.  As only one user can own any given schema/object, running the commands generated would possibly break existing rights for [User1].  If this is your intention, simply uncomment the ALTER AUTHORIZATION commands before running the scripts.

 The Script

The only thing you’ll need to change before running the script are the two variables (@OldUser and possibly @NewUser) in the <setable> section.

/*
* Author: Ryan Snee
* Version: 1.0
* Date 20130120
* URL: http://sqlsnee.com/r/userclone
*
* License: This work is licensed under a Creative Commons Attribution-ShareAlike 3.0 Unported License.
* License Details: http://creativecommons.org/licenses/by-sa/3.0/deed.en_US
*
* LIMITATIONS
*	Does not currently support a number of database-level securables (see list below)
*	Does not unroll AD group memberships
*
* Does handle the following permissions:
*
*		--From BOL for SQL 2012... class/class_desc columns
*		--"X" indicates that the securable is supported by this script
*		 
*		 	[X] 0 = Database
*		 	[X] 1 = Object or Column
*		 	[X] 3 = Schema
*		 	[ ] 4 = Database Principal
*		 	[ ] 5 = Assembly
*		 	[X] 6 = Type
*		 	[ ] 10 = XML Schema Collection
*		 	[ ] 15 = Message Type
*		 	[ ] 16 = Service Contract
*		 	[ ] 17 = Service
*		 	[ ] 18 = Remote Service Binding
*		 	[ ] 19 = Route
*		 	[ ] 23 = Full-Text Catalog
*		 	[ ] 24 = Symmetric Key
*		 	[ ] 25 = Certificate
*		 	[ ] 26 = Asymmetric Key
*
*  Also handles:
*		Schema/Object Ownership
*		Role Membership
*
*/

declare @sqlvers varchar(5);

--Determine which version of SQL we are running
declare @v numeric(4,2)
select @v = cast(left(cast(serverproperty('productversion') as varchar), 4) AS numeric(4,2))

select @sqlvers =CASE
	WHEN @V >=  12	THEN '120'
	WHEN @V >=  11	THEN '110'
	WHEN @V >= 10	THEN '100'
	WHEN @V >= 9 THEN '90'
	WHEN @V >= 8 THEN '80'
	ELSE 'No Version Found'
END

DECLARE
	@OldUser sysname=NULL,
	@NewUser sysname=NULL;

--<setable>
	SET	@OldUser = 'JohnDoe'
	--SET	@NewUser = 'AdminAssistant'
--</setable>

IF OBJECT_ID('tempdb..#Perms') IS NOT NULL
BEGIN	
	DROP TABLE #Perms
END

create table #Perms(
		 Class sysname
		,Principal nvarchar(300)
		,[NewPrincipal] nvarchar(300)
		,[Role] sysname					NULL
		,type_desc	nvarchar(60)		NULL
		,is_fixed_role bit				NULL
		,[State] nvarchar(60)			NULL
		,[Permission] nvarchar(128)		NULL
		,[Type] sysname					NULL
		,[Schema] sysname				NULL
		,[Object] sysname				NULL
		,[Column] sysname				NULL
		,[Cmd] nvarchar(max)
		,[SortOrder] tinyint
)

--Base Permissions
INSERT INTO #Perms
select
	dp.class_desc as [Class]
	,princ.name as [Principal]
	,case when princ.name=@OldUser
		THEN Coalesce(@NewUser,princ.name)
		ELSE princ.name
	end as [NewPrincipal]
	,NULL as [Role]
	,princ.type_desc
	,princ.is_fixed_role
	,dp.state_desc as [State]
	,dp.permission_name as [Permission]
	,CASE
		WHEN dp.class=0
			THEN 'DATABASE'
		WHEN dp.class=1
			THEN o.type_desc
		WHEN dp.class=3
			THEN 'SCHEMA'
		WHEN dp.class=6
			THEN 'TYPE_TABLE'
		ELSE ''
		END as [Type]
	,CASE
		WHEN dp.class=3
		THEN s3.name
		ELSE s.name
	END AS [Schema]

	,o.name as [Object]
	,c.name as [Column]

	,CASE
		WHEN dp.class=1 --OBJECT_OR_COLUMN level permission
			THEN
				case
					when dp.state='W'
						THEN 'GRANT'
					ELSE state_desc
				END
				+' '+cast(permission_name As varchar(max)) COLLATE database_default + ' ON '
				+quotename(s.name,']')+'.' +quotename(o.name,']')
				+CASE
					WHEN dp.minor_id=0 --Object Permission
						THEN ' '
					WHEN dp.minor_id > 0 --Column Permission
						THEN ' '+quotename(quotename(c.name,']'),')')+' '
					ELSE NULL
				END

		WHEN dp.class=0 --Database level permission
			THEN
				case
					when dp.state='W'
						THEN 'GRANT'
					ELSE state_desc
				END

				+' '+cast(permission_name As varchar(max)) COLLATE database_default 

		WHEN dp.class=6 --Types
			THEN
				case
					when dp.state='W'
						THEN 'GRANT'
					ELSE state_desc
				END

				+' '+cast(permission_name As varchar(max)) COLLATE database_default 
				+ ' ON TYPE::'
				+quotename(s2.name,']')+'.' +quotename(t.name,']')

		WHEN dp.class=3 --Schemas
			THEN
				case
					when dp.state='W'
						THEN 'GRANT'
					ELSE state_desc
				END

				+' '+cast(permission_name As varchar(max)) COLLATE database_default 
				+ ' ON SCHEMA::'
				+quotename(s3.name,']')

		ELSE '--Not yet supported by this script'

	END
		+CASE
			WHEN dp.class in(0,1,6,3)
				THEN
					' TO '
					+case
						when princ.name=@OldUser
							THEN quotename(Coalesce(@NewUser,princ.name)) 
						ELSE quotename(princ.name) 
					end 
					+CASE
						WHEN dp.state='W'
							THEN ' WITH GRANT OPTION;'
						ELSE ';'
					END
			ELSE ''
			END
	 AS [Cmd]
	,1 as [SortOrder]

from sys.database_permissions dp
	left join sys.all_objects o
		on dp.major_id=o.object_id
		and class=1
	left join sys.all_columns c
			on dp.major_id=c.object_id
			and dp.minor_id=c.column_id
	left join sys.database_principals princ
		on dp.grantee_principal_id=princ.principal_id
	left join sys.schemas s
		on o.schema_id=s.schema_id
	left join sys.extended_properties ep
		on ep.major_id = o.object_id and 
					ep.minor_id = 0 and 
					ep.class = 1 and 
					ep.name = N'microsoft_database_tools_support'
	left join sys.types t
		on dp.major_id=t.user_type_id
	left join sys.schemas s2
		on t.schema_id=s2.schema_id
	left join sys.schemas s3
		on dp.major_id=s3.schema_id
		and dp.class=3
where 1=1
	--AND (dp.class<>1 OR o.is_ms_shipped=0) --If it's an object, can't be MS_Shipped
	AND (dp.class<>6 OR t.is_user_defined=1) --If it's a type, only check if it's a  user-defined type
	and  ep.value is null --Not a Microsoft Database Tool support object

--Schema Ownerships
INSERT INTO #Perms
select 

		'Schema OWNERSHIP' as [Class]
	,dp.name as [Principal]
	,case when dp.name=@OldUser
		THEN Coalesce(@NewUser,dp.name)
		ELSE dp.name
	end as [NewPrincipal]
	,NULL as [Role]
	,dp.type_desc
	,dp.is_fixed_role
	,NULL as [State]
	,NULL as [Permission]
	,'SCHEMA' as [Type]
	,s.name AS [Schema]
	,NULL as [Object]
	,NULL as [Column]

	,'--ALTER AUTHORIZATION ON SCHEMA::['+s.name+'] TO '
		+case when dp.name=@OldUser
			THEN quotename(Coalesce(@NewUser,dp.name)) 
			ELSE quotename(dp.name) 
		end
	+'; --Warning, a schema can have only 1 owner' as [Cmd]
	,2 as [SortOrder]
	from sys.schemas s
inner join sys.database_principals dp
on s.principal_id=dp.principal_id

--Object Ownership

INSERT INTO #Perms
select 

	'Object OWNERSHIP' as [Class]
	,dp.name as [Principal]
	,case when dp.name=@OldUser
		THEN Coalesce(@NewUser,dp.name)
		ELSE dp.name
	end as [NewPrincipal]
	,NULL as [Role]
	,dp.type_desc
	,dp.is_fixed_role
	,NULL as [State]
	,NULL as [Permission]
	,'OBJECT' as [Type]
	,s.name AS [Schema]
	,o.name as [Object]
	,NULL as [Column]

	,'--ALTER AUTHORIZATION ON OBJECT::'+quotename(s.name)+'.'+quotename(o.name)+' TO '
		+case when dp.name=@OldUser
			THEN quotename(Coalesce(@NewUser,dp.name)) 
			ELSE quotename(dp.name) 
		end
	+'; --Warning, an object can have only 1 owner' as [Cmd]
	,3 as [SortOrder]
	from sys.objects o
inner join sys.database_principals dp
on o.principal_id=dp.principal_id
inner join sys.schemas s
on o.schema_id=s.schema_id
WHERE o.type IN('U','TT','FN','IF','P','TF','V')

--Role Memberships
INSERT INTO #Perms
select
'ROLE MEMBERSHIP' as Class
	,p2.name as Principal
	,case when p2.name=@OldUser
		THEN Coalesce(@NewUser,p2.name)
		ELSE p2.name
	end as [NewPrincipal]
	,p1.name as [Role]
	,p2.type_desc
	,p2.[is_fixed_role]
	,NULL as [State]
	,NULL as [Permission]
	,'ROLE' as [Type]
	,NULL as [Schema]
	,NULL as [Object]
	,NULL as [Column]
	,CASE when p1.is_fixed_role=1
	THEN ''
	ELSE
	'IF NOT EXISTS (select 1 from sys.database_principals where type=''R'' and name='''+p1.name+''') CREATE ROLE '+quotename(p1.name)+';'+char(13)+char(10)
	END
	+
	CASE
		WHEN @sqlvers >=110
			THEN 'ALTER ROLE '+quotename(p1.name,']')+' ADD MEMBER '+
			case when p2.name=@OldUser
		THEN quotename(Coalesce(@NewUser,p2.name)) 
		ELSE quotename(p2.name) 
	end 
			+';'
		ELSE 'EXEC sp_addrolemember N'''+p1.name+''', N'''+
		case when p2.name=@OldUser
		THEN Coalesce(@NewUser,p2.name)
		ELSE p2.name
	end 
		+''';'
	END as [Cmd]
	,4 as [SortOrder]
from sys.database_role_members rm
	left join sys.database_principals p1
		on rm.role_principal_id=p1.principal_id
	left join sys.database_principals p2
		on rm.member_principal_id=p2.principal_id

;with RecursiveCTE as(

	--Base Member
	SELECT
		 [Class]
		,[Principal]
		,[NewPrincipal]
		,[Role]
		,[State]
		,[Permission]
		,[Type]
		,[Schema]
		,[Object]
		,[Column]

		,[Cmd]
		,[SortOrder]
		,[is_fixed_role]
		,0 as [Level]
	 from #Perms where [Principal]=@OldUser

	UNION ALL
	Select
	 [Perms].[Class]
	,[Perms].[Principal]
	,[Perms].[NewPrincipal]
	,[Perms].[Role]
	,[Perms].[State]
	,[Perms].[Permission]
	,[Perms].[Type]
	,[Perms].[Schema]
	,[Perms].[Object]
	,[Perms].[Column]

	,[Perms].[Cmd]
	,[Perms].[SortOrder]
	,[Perms].[is_fixed_role]
	,[Level]+1

	FROM #Perms Perms
		INNER JOIN RecursiveCTE RCTE on Perms.Principal=RCTE.[Role]

 )

 SELECT 
	 [Level]
	,[Class]
	,[Principal]
	,[NewPrincipal]
	,[Role]
	,[State]
	,[Permission]
	,[Schema]
	,[Object]
	,[Column]
	,[Cmd]
	--,[Type]
	--,[SortOrder]
	--,is_fixed_role

FROM RecursiveCTE
WHERE is_fixed_role=0
ORDER BY [Level], [Principal], [SortOrder], [Class], [Type], [State] DESC, [Permission], [Schema], [Object], [Column], [Role]

IF OBJECT_ID('tempdb..#Perms') IS NOT NULL
BEGIN	
	DROP TABLE #Perms
END

Remember, like many of my scripts, this script doesn’t change anything; rather it generates TSQL commands you can choose to run.

Other Considerations

While this script does not generate a user’s effective permissions, it can help you to reverse-engineer them.  It works on the level of the database user.  This works fine if you have a named database user.  If you are inheriting your rights through a single AD group membership, you can still ascertain this information by supplying the database user for that AD group as the @OldUser.  What happens if you are a named user in the database, but are also granted certain privileges through a number of different AD groups?  Well, that’s where it gets more difficult.  One trick I like to employ is to use two catalog views, sys.login_token and sys.user_token.  Both views are similar, but the first works at the server login level, whereas the second works at the database user level.

These two views can help you to unravel your AD group memberships.  For the current user, they display the role memberships and the Windows group memberships of the user.  In the case of a domain environment, they connect to AD and actually enumerate all your domain security groups.  If a particular group actually matches a server login or database user, this view will also show you the principal_id you can use to join it back to sys.server_principals (in the case of sys.login_token) or sys.database_principals (in the case of sys.user_token).

I hope you’re beginning to see how powerful this is 🙂 .

This works great if you were to want to troubleshoot your own permissions.  How though, as a DBA, would you be able to use this to troubleshoot a different account’s permissions?  You can have a user run these queries, as themselves, and return the output to you… or, as a DBA, you can use the power of IMPERSONATE.  If you are the DBA (or otherwise have impersonation rights on another account), you can run any command as that user.  Be aware that this may violate security policies at your company, and could be ill-advised in certain security-sensitive environments.  Also, if you have any type of auditing enabled, chances are good that you will leave footprints all over your audit logs that you are running commands on behalf of another user.  This may or may not be a problem for you.  That being said, if you determine that it’s acceptable to do, you can run a command such as:

use Adventureworks;
GO

execute as login='DOMAIN\User'
	select * from sys.login_token where principal_id <> 0
	select * from sys.user_token where principal_id <> 0
revert

This will show you [DOMAIN\User]’s login token for the server and user token for the Adventureworks database.  Using this technique in conjunction with the User Clone script can go a long way to managing and troubleshooting database permissions.

 

The full script can be found here: UserClone.txt.

Please let me know if you found this script useful or if you have any suggestions to improve it.

LLAP,
Ryan


As always, please note the waive of liability published on the About page.

The text of this entire blog site (including all scripts) is under the copyright of Ryan Snee. You are free to reproduce it, as outlined by the Creative Commons Attribution-ShareAlike 3.0 Unported License.