The main reason for creating this script was to compare user permissions when someone came to me saying “I need the same permissions as so and so,” and then I would scratch my head trying to figure out how to get this quickly and easily. Finally, I wrote a script. It’s in a state of flux with new things being added as I discover that I didn’t capture everything I wanted, but currently, it works for:
- Server level perms – this script doesn’t give you the scripts to create logins, and for that, I use sp_revlogin or dbatools. The purpose of this was more as a check to see what perms someone has and then quickly grab the script to mimic that for someone else.
- Database role perms
- Database object perms
- Schema perms
- Type perms – this really comes into play when there are user-defined types created
Here’s the script for those who just want to get started using it.
https://github.com/sqlkitty/SQLSecurity/blob/main/server-db-roles-securables-perms-all-in-one.sql
I’ll explain the bits and pieces below the script in case there are any questions about what I’m doing. Really all you need to do is set your variables for @user and @database and you are good to go. You don’t have to understand all the inner workings of this script in order for it to work for you.
Each of the queries will spit out a table with the results showing you the perms and information on the objects that the perms are granted on and the last column gives you the script to implement the perms. I’m thinking about instead printing this to the screen so you can easily copy and paste the code, but as it is it’s only five result sets so I wasn’t too worried about having to copy/paste out each result set into a query window.
This script starts by declaring some important variables like:
- @user – this can be set to NULL to get all users or it can be set to a specific user or group
- @database – this can also be set to NULL and it will get all the databases or can be set to a specific database
I may modify this to allow for setting multiple databases or excluding multiple databases. For now, the use cases are:
- one database for one user
- all databases for one user
- all databases for all users
If you are interested in getting a user’s perms when they are getting their perms from a group. then you can use the code to enumerate a group:
DECLARE @LoginName varchar(50)
SET @LoginName = 'domain\user'
exec xp_logininfo @LoginName, 'all'Then put that group into the @user variable to see the perms for that group. I’m trying to work on enumerating groups on the fly with the query, but it’s a work a progress. I’m also working on setting multiple users/databases or excluding multiple users/databases.
Then the script takes you through each of the perms that it’s gathering one by one. The server-level perms aren’t placed into a table variable because it only has to run once to get everything. The database level queries are placed into a table variable in order to loop through each database in turn.
Really all you need to do is set your variables for @user and @database and you are good to go. You don’t have to understand the inner workings of this script in order for it to work for you.