{"id":140,"date":"2014-01-20T14:45:26","date_gmt":"2014-01-20T20:45:26","guid":{"rendered":"http:\/\/blog.sqlsnee.com\/?p=140"},"modified":"2020-02-18T18:30:49","modified_gmt":"2020-02-19T00:30:49","slug":"user-clone-script","status":"publish","type":"post","link":"https:\/\/blog.sqlsnee.com\/?p=140","title":{"rendered":"User Clone Script"},"content":{"rendered":"<p>One of my favorite subjects in SQL server is <em>security<\/em>, and one of my favorite security topics is <em>permissions<\/em>.\u00a0 This post will cover a script I wrote to recreate a user&#8217;s permissions within a database.\u00a0 I call it my <span style=\"text-decoration: underline;\">User Clone Script<\/span>, and use it quite heavily.<\/p>\n<h3><span style=\"text-decoration: underline;\">Use Cases<\/span><\/h3>\n<ul>\n<li>Troubleshooting Permissions<\/li>\n<li>Cloning accounts<\/li>\n<li>Switching service accounts<\/li>\n<li>Copying permissions between development\/test\/production environments<\/li>\n<li>Refactoring permissions to use a SQL Server <em>Role<\/em> instead of explicit user permissions<\/li>\n<li>Refactoring permissions to use an AD group instead of explicitly named users<\/li>\n<\/ul>\n<h3><span style=\"text-decoration: underline;\">Highlights of the Script<\/span><\/h3>\n<p>This script has support for the following:<\/p>\n<ul>\n<li>Most object\/column permissions (includes stored procedures, views, tables, and functions)<\/li>\n<li>Object ownerships<\/li>\n<li>Type permissions<\/li>\n<li>Schema permissions<\/li>\n<li>Schema ownerships<\/li>\n<li>Database permissions<\/li>\n<li>Role memberships<\/li>\n<\/ul>\n<h3>\u00a0<span style=\"text-decoration: underline;\">Implementation<\/span><\/h3>\n<p>In a few separate queries, I dump meta-data into a temp table from the following catalog views:<\/p>\n<ol>\n<li>Database permissions (<span style=\"color: #008000; background-color: #ffffff;\"><strong> sys.database_permissions <\/strong><\/span>)<\/li>\n<li>Schema ownerships (<span style=\"color: #008000; background-color: #ffffff;\"><strong> sys.schemas <\/strong><\/span>)<\/li>\n<li>Object Ownership (<span style=\"color: #008000; background-color: #ffffff;\"><strong> sys.objects <\/strong><\/span>)<\/li>\n<li>Role Memberships (<span style=\"color: #008000; background-color: #ffffff;\"><strong> sys.database_role_members <\/strong><\/span>)<\/li>\n<\/ol>\n<p>Then\u2014 and this is the key to unraveling role memberships\u2014 I use a <em>Recursive CTE<\/em> to generate the final result set.<\/p>\n<p>A recursive CTE takes the following form:<\/p>\n<pre escaped=\"true\" lang=\"tsql\">;with RecursiveCTE as(\r\n\r\n--1) Base\/Anchor Member\r\nSELECT\r\n\u00a0\u00a0\u00a0 Columns\r\nFROM #SomeTable\r\nWHERE [ID]=@BaseMemberID\r\n\r\n--2) Always a UNION\/UNION ALL\r\nUNION ALL\r\n\r\n--3) Recursive Member\r\nSelect\r\n\u00a0\u00a0\u00a0 Columns\r\nFROM #SomeTable T\r\nINNER JOIN RecursiveCTE RCTE ON T.ParentID=RCTE.ID\r\n\r\n)\r\n\r\nSELECT * FROM RecursiveCTE;<\/pre>\n<p>More information about Recursive CTEs can be found here on <a title=\"technet.microsoft.com\" href=\"http:\/\/www.sqlsnee.com\/r\/tn_cte\" target=\"_blank\" rel=\"noopener noreferrer\">TechNet<\/a>.<\/p>\n<h3><span style=\"text-decoration: underline;\">Output Sample<\/span><\/h3>\n<p>The following screenshot shows the output of the script, including commands to copy the permissions of <em>User1<\/em> to <em>User2<\/em>.<\/p>\n<p style=\"padding-left: 30px;\"><a href=\"http:\/\/blog.sqlsnee.com\/wp-content\/uploads\/2014\/01\/PermissionsClone.png\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-large wp-image-171\" alt=\"PermissionsClone\" src=\"http:\/\/blog.sqlsnee.com\/wp-content\/uploads\/2014\/01\/PermissionsClone-1024x259.png\" width=\"1024\" height=\"259\" srcset=\"https:\/\/blog.sqlsnee.com\/wp-content\/uploads\/2014\/01\/PermissionsClone-1024x259.png 1024w, https:\/\/blog.sqlsnee.com\/wp-content\/uploads\/2014\/01\/PermissionsClone-300x75.png 300w, https:\/\/blog.sqlsnee.com\/wp-content\/uploads\/2014\/01\/PermissionsClone.png 1591w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/a><\/p>\n<p>A couple of key points:<\/p>\n<ul>\n<li>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).\n<ul>\n<li>Otherwise, it will replace [User1] with [User2].<\/li>\n<\/ul>\n<\/li>\n<li>In this example:\n<ul>\n<li>[User1] is an immediate and direct member of three roles: [CoolUsers], [Level2] and [Level2a].<\/li>\n<li>[CoolUsers] is a member of the [db_owner] fixed role.<\/li>\n<li>The [Level2] role is a member of the [Level1] role.<\/li>\n<\/ul>\n<\/li>\n<li>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&#8217;re trying to copy the permissions to a different database, for example)<\/li>\n<li>If a schema or object <strong>ownership<\/strong> is detected, the script will generate the command to transfer ownership to the new user, but it will be <em>commented-out<\/em>.\u00a0 This is done as a precautionary measure.\u00a0 As only one user can own any given schema\/object, running the commands generated would possibly break existing rights for [User1].\u00a0 If this is your intention, simply uncomment the ALTER AUTHORIZATION commands before running the scripts.<\/li>\n<\/ul>\n<h3>\u00a0<span style=\"text-decoration: underline;\">The Script<\/span><\/h3>\n<p>The only thing you&#8217;ll need to change before running the script are the two variables (<span style=\"color: #008080; background-color: #ffffff;\">@OldUser<\/span> and possibly <span style=\"color: #008080; background-color: #ffffff;\">@NewUser<\/span>) in the &lt;setable&gt; section.<\/p>\n<pre class=\"lang:default decode:true \">\/*\r\n* Author: Ryan Snee\r\n* Version: 1.0\r\n* Date 20130120\r\n* URL: http:\/\/sqlsnee.com\/r\/userclone\r\n*\r\n* License: This work is licensed under a Creative Commons Attribution-ShareAlike 3.0 Unported License.\r\n* License Details: http:\/\/creativecommons.org\/licenses\/by-sa\/3.0\/deed.en_US\r\n*\r\n* LIMITATIONS\r\n*\tDoes not currently support a number of database-level securables (see list below)\r\n*\tDoes not unroll AD group memberships\r\n*\r\n* Does handle the following permissions:\r\n*\r\n*\t\t--From BOL for SQL 2012... class\/class_desc columns\r\n*\t\t--\"X\" indicates that the securable is supported by this script\r\n*\t\t \r\n*\t\t \t[X] 0 = Database\r\n*\t\t \t[X] 1 = Object or Column\r\n*\t\t \t[X] 3 = Schema\r\n*\t\t \t[ ] 4 = Database Principal\r\n*\t\t \t[ ] 5 = Assembly\r\n*\t\t \t[X] 6 = Type\r\n*\t\t \t[ ] 10 = XML Schema Collection\r\n*\t\t \t[ ] 15 = Message Type\r\n*\t\t \t[ ] 16 = Service Contract\r\n*\t\t \t[ ] 17 = Service\r\n*\t\t \t[ ] 18 = Remote Service Binding\r\n*\t\t \t[ ] 19 = Route\r\n*\t\t \t[ ] 23 = Full-Text Catalog\r\n*\t\t \t[ ] 24 = Symmetric Key\r\n*\t\t \t[ ] 25 = Certificate\r\n*\t\t \t[ ] 26 = Asymmetric Key\r\n*\r\n*  Also handles:\r\n*\t\tSchema\/Object Ownership\r\n*\t\tRole Membership\r\n*\r\n*\/\r\n\r\ndeclare @sqlvers varchar(5);\r\n\r\n--Determine which version of SQL we are running\r\ndeclare @v numeric(4,2)\r\nselect @v = cast(left(cast(serverproperty('productversion') as varchar), 4) AS numeric(4,2))\r\n\r\nselect @sqlvers =CASE\r\n\tWHEN @V &gt;=  12\tTHEN '120'\r\n\tWHEN @V &gt;=  11\tTHEN '110'\r\n\tWHEN @V &gt;= 10\tTHEN '100'\r\n\tWHEN @V &gt;= 9 THEN '90'\r\n\tWHEN @V &gt;= 8 THEN '80'\r\n\tELSE 'No Version Found'\r\nEND\r\n\r\nDECLARE\r\n\t@OldUser sysname=NULL,\r\n\t@NewUser sysname=NULL;\r\n\r\n--&lt;setable&gt;\r\n\tSET\t@OldUser = 'JohnDoe'\r\n\t--SET\t@NewUser = 'AdminAssistant'\r\n--&lt;\/setable&gt;\r\n\r\nIF OBJECT_ID('tempdb..#Perms') IS NOT NULL\r\nBEGIN\t\r\n\tDROP TABLE #Perms\r\nEND\r\n\r\ncreate table #Perms(\r\n\t\t Class sysname\r\n\t\t,Principal nvarchar(300)\r\n\t\t,[NewPrincipal] nvarchar(300)\r\n\t\t,[Role] sysname\t\t\t\t\tNULL\r\n\t\t,type_desc\tnvarchar(60)\t\tNULL\r\n\t\t,is_fixed_role bit\t\t\t\tNULL\r\n\t\t,[State] nvarchar(60)\t\t\tNULL\r\n\t\t,[Permission] nvarchar(128)\t\tNULL\r\n\t\t,[Type] sysname\t\t\t\t\tNULL\r\n\t\t,[Schema] sysname\t\t\t\tNULL\r\n\t\t,[Object] sysname\t\t\t\tNULL\r\n\t\t,[Column] sysname\t\t\t\tNULL\r\n\t\t,[Cmd] nvarchar(max)\r\n\t\t,[SortOrder] tinyint\r\n)\r\n\r\n--Base Permissions\r\nINSERT INTO #Perms\r\nselect\r\n\tdp.class_desc as [Class]\r\n\t,princ.name as [Principal]\r\n\t,case when princ.name=@OldUser\r\n\t\tTHEN Coalesce(@NewUser,princ.name)\r\n\t\tELSE princ.name\r\n\tend as [NewPrincipal]\r\n\t,NULL as [Role]\r\n\t,princ.type_desc\r\n\t,princ.is_fixed_role\r\n\t,dp.state_desc as [State]\r\n\t,dp.permission_name as [Permission]\r\n\t,CASE\r\n\t\tWHEN dp.class=0\r\n\t\t\tTHEN 'DATABASE'\r\n\t\tWHEN dp.class=1\r\n\t\t\tTHEN o.type_desc\r\n\t\tWHEN dp.class=3\r\n\t\t\tTHEN 'SCHEMA'\r\n\t\tWHEN dp.class=6\r\n\t\t\tTHEN 'TYPE_TABLE'\r\n\t\tELSE ''\r\n\t\tEND as [Type]\r\n\t,CASE\r\n\t\tWHEN dp.class=3\r\n\t\tTHEN s3.name\r\n\t\tELSE s.name\r\n\tEND AS [Schema]\r\n\r\n\t,o.name as [Object]\r\n\t,c.name as [Column]\r\n\r\n\t,CASE\r\n\t\tWHEN dp.class=1 --OBJECT_OR_COLUMN level permission\r\n\t\t\tTHEN\r\n\t\t\t\tcase\r\n\t\t\t\t\twhen dp.state='W'\r\n\t\t\t\t\t\tTHEN 'GRANT'\r\n\t\t\t\t\tELSE state_desc\r\n\t\t\t\tEND\r\n\t\t\t\t+' '+cast(permission_name As varchar(max)) COLLATE database_default + ' ON '\r\n\t\t\t\t+quotename(s.name,']')+'.' +quotename(o.name,']')\r\n\t\t\t\t+CASE\r\n\t\t\t\t\tWHEN dp.minor_id=0 --Object Permission\r\n\t\t\t\t\t\tTHEN ' '\r\n\t\t\t\t\tWHEN dp.minor_id &gt; 0 --Column Permission\r\n\t\t\t\t\t\tTHEN ' '+quotename(quotename(c.name,']'),')')+' '\r\n\t\t\t\t\tELSE NULL\r\n\t\t\t\tEND\r\n\r\n\t\tWHEN dp.class=0 --Database level permission\r\n\t\t\tTHEN\r\n\t\t\t\tcase\r\n\t\t\t\t\twhen dp.state='W'\r\n\t\t\t\t\t\tTHEN 'GRANT'\r\n\t\t\t\t\tELSE state_desc\r\n\t\t\t\tEND\r\n\r\n\t\t\t\t+' '+cast(permission_name As varchar(max)) COLLATE database_default \r\n\r\n\t\tWHEN dp.class=6 --Types\r\n\t\t\tTHEN\r\n\t\t\t\tcase\r\n\t\t\t\t\twhen dp.state='W'\r\n\t\t\t\t\t\tTHEN 'GRANT'\r\n\t\t\t\t\tELSE state_desc\r\n\t\t\t\tEND\r\n\r\n\t\t\t\t+' '+cast(permission_name As varchar(max)) COLLATE database_default \r\n\t\t\t\t+ ' ON TYPE::'\r\n\t\t\t\t+quotename(s2.name,']')+'.' +quotename(t.name,']')\r\n\r\n\t\tWHEN dp.class=3 --Schemas\r\n\t\t\tTHEN\r\n\t\t\t\tcase\r\n\t\t\t\t\twhen dp.state='W'\r\n\t\t\t\t\t\tTHEN 'GRANT'\r\n\t\t\t\t\tELSE state_desc\r\n\t\t\t\tEND\r\n\r\n\t\t\t\t+' '+cast(permission_name As varchar(max)) COLLATE database_default \r\n\t\t\t\t+ ' ON SCHEMA::'\r\n\t\t\t\t+quotename(s3.name,']')\r\n\r\n\t\tELSE '--Not yet supported by this script'\r\n\r\n\tEND\r\n\t\t+CASE\r\n\t\t\tWHEN dp.class in(0,1,6,3)\r\n\t\t\t\tTHEN\r\n\t\t\t\t\t' TO '\r\n\t\t\t\t\t+case\r\n\t\t\t\t\t\twhen princ.name=@OldUser\r\n\t\t\t\t\t\t\tTHEN quotename(Coalesce(@NewUser,princ.name)) \r\n\t\t\t\t\t\tELSE quotename(princ.name) \r\n\t\t\t\t\tend \r\n\t\t\t\t\t+CASE\r\n\t\t\t\t\t\tWHEN dp.state='W'\r\n\t\t\t\t\t\t\tTHEN ' WITH GRANT OPTION;'\r\n\t\t\t\t\t\tELSE ';'\r\n\t\t\t\t\tEND\r\n\t\t\tELSE ''\r\n\t\t\tEND\r\n\t AS [Cmd]\r\n\t,1 as [SortOrder]\r\n\r\nfrom sys.database_permissions dp\r\n\tleft join sys.all_objects o\r\n\t\ton dp.major_id=o.object_id\r\n\t\tand class=1\r\n\tleft join sys.all_columns c\r\n\t\t\ton dp.major_id=c.object_id\r\n\t\t\tand dp.minor_id=c.column_id\r\n\tleft join sys.database_principals princ\r\n\t\ton dp.grantee_principal_id=princ.principal_id\r\n\tleft join sys.schemas s\r\n\t\ton o.schema_id=s.schema_id\r\n\tleft join sys.extended_properties ep\r\n\t\ton ep.major_id = o.object_id and \r\n\t\t\t\t\tep.minor_id = 0 and \r\n\t\t\t\t\tep.class = 1 and \r\n\t\t\t\t\tep.name = N'microsoft_database_tools_support'\r\n\tleft join sys.types t\r\n\t\ton dp.major_id=t.user_type_id\r\n\tleft join sys.schemas s2\r\n\t\ton t.schema_id=s2.schema_id\r\n\tleft join sys.schemas s3\r\n\t\ton dp.major_id=s3.schema_id\r\n\t\tand dp.class=3\r\nwhere 1=1\r\n\t--AND (dp.class&lt;&gt;1 OR o.is_ms_shipped=0) --If it's an object, can't be MS_Shipped\r\n\tAND (dp.class&lt;&gt;6 OR t.is_user_defined=1) --If it's a type, only check if it's a  user-defined type\r\n\tand  ep.value is null --Not a Microsoft Database Tool support object\r\n\r\n--Schema Ownerships\r\nINSERT INTO #Perms\r\nselect \r\n\r\n\t\t'Schema OWNERSHIP' as [Class]\r\n\t,dp.name as [Principal]\r\n\t,case when dp.name=@OldUser\r\n\t\tTHEN Coalesce(@NewUser,dp.name)\r\n\t\tELSE dp.name\r\n\tend as [NewPrincipal]\r\n\t,NULL as [Role]\r\n\t,dp.type_desc\r\n\t,dp.is_fixed_role\r\n\t,NULL as [State]\r\n\t,NULL as [Permission]\r\n\t,'SCHEMA' as [Type]\r\n\t,s.name AS [Schema]\r\n\t,NULL as [Object]\r\n\t,NULL as [Column]\r\n\r\n\t,'--ALTER AUTHORIZATION ON SCHEMA::['+s.name+'] TO '\r\n\t\t+case when dp.name=@OldUser\r\n\t\t\tTHEN quotename(Coalesce(@NewUser,dp.name)) \r\n\t\t\tELSE quotename(dp.name) \r\n\t\tend\r\n\t+'; --Warning, a schema can have only 1 owner' as [Cmd]\r\n\t,2 as [SortOrder]\r\n\tfrom sys.schemas s\r\ninner join sys.database_principals dp\r\non s.principal_id=dp.principal_id\r\n\r\n--Object Ownership\r\n\r\nINSERT INTO #Perms\r\nselect \r\n\r\n\t'Object OWNERSHIP' as [Class]\r\n\t,dp.name as [Principal]\r\n\t,case when dp.name=@OldUser\r\n\t\tTHEN Coalesce(@NewUser,dp.name)\r\n\t\tELSE dp.name\r\n\tend as [NewPrincipal]\r\n\t,NULL as [Role]\r\n\t,dp.type_desc\r\n\t,dp.is_fixed_role\r\n\t,NULL as [State]\r\n\t,NULL as [Permission]\r\n\t,'OBJECT' as [Type]\r\n\t,s.name AS [Schema]\r\n\t,o.name as [Object]\r\n\t,NULL as [Column]\r\n\r\n\t,'--ALTER AUTHORIZATION ON OBJECT::'+quotename(s.name)+'.'+quotename(o.name)+' TO '\r\n\t\t+case when dp.name=@OldUser\r\n\t\t\tTHEN quotename(Coalesce(@NewUser,dp.name)) \r\n\t\t\tELSE quotename(dp.name) \r\n\t\tend\r\n\t+'; --Warning, an object can have only 1 owner' as [Cmd]\r\n\t,3 as [SortOrder]\r\n\tfrom sys.objects o\r\ninner join sys.database_principals dp\r\non o.principal_id=dp.principal_id\r\ninner join sys.schemas s\r\non o.schema_id=s.schema_id\r\nWHERE o.type IN('U','TT','FN','IF','P','TF','V')\r\n\r\n--Role Memberships\r\nINSERT INTO #Perms\r\nselect\r\n'ROLE MEMBERSHIP' as Class\r\n\t,p2.name as Principal\r\n\t,case when p2.name=@OldUser\r\n\t\tTHEN Coalesce(@NewUser,p2.name)\r\n\t\tELSE p2.name\r\n\tend as [NewPrincipal]\r\n\t,p1.name as [Role]\r\n\t,p2.type_desc\r\n\t,p2.[is_fixed_role]\r\n\t,NULL as [State]\r\n\t,NULL as [Permission]\r\n\t,'ROLE' as [Type]\r\n\t,NULL as [Schema]\r\n\t,NULL as [Object]\r\n\t,NULL as [Column]\r\n\t,CASE when p1.is_fixed_role=1\r\n\tTHEN ''\r\n\tELSE\r\n\t'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)\r\n\tEND\r\n\t+\r\n\tCASE\r\n\t\tWHEN @sqlvers &gt;=110\r\n\t\t\tTHEN 'ALTER ROLE '+quotename(p1.name,']')+' ADD MEMBER '+\r\n\t\t\tcase when p2.name=@OldUser\r\n\t\tTHEN quotename(Coalesce(@NewUser,p2.name)) \r\n\t\tELSE quotename(p2.name) \r\n\tend \r\n\t\t\t+';'\r\n\t\tELSE 'EXEC sp_addrolemember N'''+p1.name+''', N'''+\r\n\t\tcase when p2.name=@OldUser\r\n\t\tTHEN Coalesce(@NewUser,p2.name)\r\n\t\tELSE p2.name\r\n\tend \r\n\t\t+''';'\r\n\tEND as [Cmd]\r\n\t,4 as [SortOrder]\r\nfrom sys.database_role_members rm\r\n\tleft join sys.database_principals p1\r\n\t\ton rm.role_principal_id=p1.principal_id\r\n\tleft join sys.database_principals p2\r\n\t\ton rm.member_principal_id=p2.principal_id\r\n\r\n;with RecursiveCTE as(\r\n\r\n\t--Base Member\r\n\tSELECT\r\n\t\t [Class]\r\n\t\t,[Principal]\r\n\t\t,[NewPrincipal]\r\n\t\t,[Role]\r\n\t\t,[State]\r\n\t\t,[Permission]\r\n\t\t,[Type]\r\n\t\t,[Schema]\r\n\t\t,[Object]\r\n\t\t,[Column]\r\n\r\n\t\t,[Cmd]\r\n\t\t,[SortOrder]\r\n\t\t,[is_fixed_role]\r\n\t\t,0 as [Level]\r\n\t from #Perms where [Principal]=@OldUser\r\n\r\n\tUNION ALL\r\n\tSelect\r\n\t [Perms].[Class]\r\n\t,[Perms].[Principal]\r\n\t,[Perms].[NewPrincipal]\r\n\t,[Perms].[Role]\r\n\t,[Perms].[State]\r\n\t,[Perms].[Permission]\r\n\t,[Perms].[Type]\r\n\t,[Perms].[Schema]\r\n\t,[Perms].[Object]\r\n\t,[Perms].[Column]\r\n\r\n\t,[Perms].[Cmd]\r\n\t,[Perms].[SortOrder]\r\n\t,[Perms].[is_fixed_role]\r\n\t,[Level]+1\r\n\r\n\tFROM #Perms Perms\r\n\t\tINNER JOIN RecursiveCTE RCTE on Perms.Principal=RCTE.[Role]\r\n\r\n )\r\n\r\n SELECT \r\n\t [Level]\r\n\t,[Class]\r\n\t,[Principal]\r\n\t,[NewPrincipal]\r\n\t,[Role]\r\n\t,[State]\r\n\t,[Permission]\r\n\t,[Schema]\r\n\t,[Object]\r\n\t,[Column]\r\n\t,[Cmd]\r\n\t--,[Type]\r\n\t--,[SortOrder]\r\n\t--,is_fixed_role\r\n\r\nFROM RecursiveCTE\r\nWHERE is_fixed_role=0\r\nORDER BY [Level], [Principal], [SortOrder], [Class], [Type], [State] DESC, [Permission], [Schema], [Object], [Column], [Role]\r\n\r\nIF OBJECT_ID('tempdb..#Perms') IS NOT NULL\r\nBEGIN\t\r\n\tDROP TABLE #Perms\r\nEND<\/pre>\n<p>Remember, like many of my scripts, this script doesn\u2019t change anything; rather it generates TSQL commands you can <em>choose<\/em> to run.<\/p>\n<h3><span style=\"text-decoration: underline;\">Other Considerations<\/span><\/h3>\n<p>While this script does not generate a user&#8217;s effective permissions, it can help you to reverse-engineer them.\u00a0 It works on the level of the <em>database user<\/em>.\u00a0 This works fine if you have a named database user.\u00a0 If you are inheriting your rights through a single AD group membership, you can still ascertain this information by supplying the database user <em>for that AD group<\/em> as the <span style=\"color: #008080; background-color: #ffffff;\">@OldUser<\/span>.\u00a0 What happens if you are a named user in the database, but are <strong>also<\/strong> granted certain privileges through a number of different AD groups?\u00a0 Well, that&#8217;s where it gets more difficult.\u00a0 One trick I like to employ is to use two catalog views, <span style=\"color: #008000; background-color: #ffffff;\">sys.login_token<\/span> and <span style=\"color: #008000; background-color: #ffffff;\">sys.user_token<\/span>.\u00a0 Both views are similar, but the first works at the server login level, whereas the second works at the database user level.<\/p>\n<p>These two views can help you to unravel your AD group memberships.\u00a0 For the current user, they display the role memberships <strong>and<\/strong> the Windows group memberships of the user.\u00a0 In the case of a domain environment, they connect to AD and actually enumerate all your domain security groups.\u00a0 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 <span style=\"color: #008000; background-color: #ffffff;\">sys.server_principals<\/span> (in the case of <span style=\"color: #008000; background-color: #ffffff;\">sys.login_token<\/span>) or <span style=\"color: #008000; background-color: #ffffff;\">sys.database_principals<\/span> (in the case of <span style=\"color: #008000; background-color: #ffffff;\">sys.user_token<\/span>).<\/p>\n<p>I hope you&#8217;re beginning to see how powerful this is \ud83d\ude42 .<\/p>\n<p>This works great if you were to want to troubleshoot your own permissions.\u00a0 How though, as a DBA, would you be able to use this to troubleshoot a <em>different<\/em> account&#8217;s permissions?\u00a0 You can have a user run these queries, as themselves, and return the output to you&#8230; or, as a DBA, you can use the power of IMPERSONATE.\u00a0 If you are the DBA (or otherwise have impersonation rights on another account), you can run any command as that user.\u00a0 <span style=\"text-decoration: underline;\"><strong>Be aware that this may violate security policies at your company, and could be ill-advised in certain security-sensitive environments.\u00a0 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.\u00a0 This may or may not be a problem for you.<\/strong><\/span>\u00a0 That being said, if you determine that it&#8217;s acceptable to do, you can run a command such as:<\/p>\n<pre escaped=\"true\" lang=\"tsql\">use Adventureworks;\r\nGO\r\n\r\nexecute as login='DOMAIN\\User'\r\n\tselect * from sys.login_token where principal_id &lt;&gt; 0\r\n\tselect * from sys.user_token where principal_id &lt;&gt; 0\r\nrevert<\/pre>\n<p>This will show you [DOMAIN\\User]&#8217;s <strong>login token<\/strong> for the server and <strong>user token<\/strong> for the Adventureworks database.\u00a0 Using this technique in conjunction with the <em>User Clone<\/em> script can go a long way to managing and troubleshooting database permissions.<\/p>\n<p>&nbsp;<\/p>\n<p>The full script can be found here: <a title=\"UserClone.txt\" href=\"http:\/\/www.sqlsnee.com\/dl\/scripts\/UserClone.txt\">UserClone.txt<\/a>.<\/p>\n<p>Please let me know if you found this script useful or if you have any suggestions to improve it.<\/p>\n<p>LLAP,<br \/>\nRyan<\/p>\n<hr \/>\n<p>As always, please note the <em>waive of liability<\/em> published on the <a title=\"Legalese\" href=\"http:\/\/www.sqlsnee.com\/r\/legalese\">About<\/a> page.<\/p>\n<p>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 <a href=\"http:\/\/www.sqlsnee.com\/r\/cclicense\" target=\"_blank\" rel=\"noopener noreferrer\">Creative Commons Attribution-ShareAlike 3.0 Unported License<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>One of my favorite subjects in SQL server is security, and one of my favorite security topics is permissions.\u00a0 This post will cover a script I wrote to recreate a user&#8217;s permissions within a database.\u00a0 I call it my User Clone Script, and use it quite heavily. Use Cases Troubleshooting \u2026 <a class=\"continue-reading-link\" href=\"https:\/\/blog.sqlsnee.com\/?p=140\"> Continue reading <span class=\"meta-nav\">&rarr; <\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[17,16],"tags":[23,18,24],"class_list":["post-140","post","type-post","status-publish","format-standard","hentry","category-permissions","category-security","tag-permissions","tag-recursive-cte","tag-security"],"_links":{"self":[{"href":"https:\/\/blog.sqlsnee.com\/index.php?rest_route=\/wp\/v2\/posts\/140","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.sqlsnee.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.sqlsnee.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.sqlsnee.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.sqlsnee.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=140"}],"version-history":[{"count":9,"href":"https:\/\/blog.sqlsnee.com\/index.php?rest_route=\/wp\/v2\/posts\/140\/revisions"}],"predecessor-version":[{"id":254,"href":"https:\/\/blog.sqlsnee.com\/index.php?rest_route=\/wp\/v2\/posts\/140\/revisions\/254"}],"wp:attachment":[{"href":"https:\/\/blog.sqlsnee.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=140"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.sqlsnee.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=140"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.sqlsnee.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=140"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}