Permissions script for Azure Synapse
3October 15, 2020 by Kenneth Fisher
I was recently asked to add on to my permissions stored procedures (sp_SrvPermissions, sp_DBPermissions and sp_AzSQLDBPermissions) and create one for Azure Synapse DBs. I had intended to do this a while back but ran into a few problems. Primarily the fact that I don’t know Synapse very well and discovered that I couldn’t assign default values to parameters, which meant that I couldn’t have optional parameters. In the end I compromised. sp_AzSYNDBPermissions doesn’t have any parameters. It runs the basic queries that the other stored procedures run if you don’t pass them any parameters.
I.e. it returns the following three rowsets.
- A list of all of the database principals.
- A list of the role memberships for each principal.
- A list of the individual permissions for each principal.
As with all of my scripts it can either be found under the Free Scripts heading in my blog or my github repository.
SQL Studies 
Your script came at exactly the right time. I needed an overview of users and their rights. Boom, 1 second and i got it. Awesome work and thank you very much!
Glad you liked it :). I wish I could have done it the way I have the other SPs work since I have some nice reporting features and search features but it’s handy the way it is.
[…] procedure that will list all your users and their rights. You can find the blogpost referring to that procedure here. Thank you Kenneth! If you find his procedure usesfull, let him […]