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:

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.

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:

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.

Tagged , , . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *