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:
- Database permissions ( sys.database_permissions )
- Schema ownerships ( sys.schemas )
- Object Ownership ( sys.objects )
- 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.
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.