Last week I announced 3 new open source SQL Server projects that I have on GitHub. One of those projects is SQL Server Permissions Manager, and today I would like to discuss it in more detail.
SQL Server Permissions Manager is a suite of scripts that allows you to take “snapshots” of users and permissions across any or all of the databases on your SQL Server. These snapshots then allow you to:
- Restore existing user permissions to a previous snapshot
- Script out permissions for an individual user or all users in a database
- Create a new user by cloning the permissions of an existing user
- Remove all users and their permissions from a database
- Remove a login from the server and all of its permissions from all databases
SQL Server Permissions Manager isn’t something that I just threw together and put out on GitHub. In fact, I’ve been using it on my production servers for several years. It has seen several iterations over the years, with enhancements and bug fixes, and even having been almost totally rewritten at least once. I consider it a very useful tool, and it’s one of the first things I install on all of my SQL Servers.
Whenever I talk about SQL Server Permissions Manager with other DBAs, I always get asked if they can see the code, or if it is available for download somewhere. So I decided to put it out on GitHub, free for anyone to use.
Why Use it
Managing permissions can be a pain for a DBA, especially with a lot of databases. Some of it can be mitigated by using AD Groups and DB Roles, but that’s not always an option. There are always some tedious tasks that have to be performed. What if you need to script out all permissions for every user in a database, or just a single user? What if you want to create a new user, and give it the same permissions as an existing user? What if you need to roll back all user’s permissions in a database to a previous point in time? All of these things (and more) can be done with SQL Server Permissions Manager.
How It Works
SQL Server Permissions Manager creates a “perms” schema in your database, and creates all of its objects there. (This doesn’t have to be added to each database, I actually recommend putting everything in a single, central database.) Tables are created for storing the permission snapshots, and stored procedures are used for gathering and using the snapshot data. Once the tables and procedures are created, you manually run or schedule a stored procedure to take the permission snapshots. Once a snapshot is created, you can then call any of the other procedures to interact with it. There is also an optional purge feature to keep the tables from getting too big.
If you are interested in working with SQL Server Permissions Manager please let me know, I am eager to get some feedback on it. I am looking forward to working with (and learning from) the SQL community on these projects!
To keep up with the latest information on SQL Server Permissions Manager, be sure to check out my most recent blog posts.