This post is part of the T-SQL Tuesday blog party. This time around hosted by Bert Wagner (b|t). Each month there is a new topic everyone is invited to talk about, this occasion is about the code that you would hate to live without. It is well known that all database people have our own set of scripts or swiss tools to manage our databases and make our lives much easier.
Recently I’ve been working with Azure SQL Database and found that our beloved SSMS does not have support to show the properties of users, hence there is no graphical way to see the roles and securable permissions a database user has. On SQL Server on premises, the user’s properties window shows what roles the user is member of and what securables the user has permissions too, it is not the easiest view to the eyes, but it is what we had. If we needed to see the big picture of permissions on our database, there is nothing built-in, at least nothing that I know of.
So, I wrote the following script that returns a list of users and their corresponding roles and securables. This script has been very handy, especially for the Azure SQL Databases, but also can be used for SQL Server on premise, I have test it on SQL Server 2012 onwards.
An example of the results generated by the script is the following:
If you want to use it on lower versions the column “authentication_type_desc” should be commented. I hope this script helps you as much as it has helped me.