Permissions script for Azure Synapse


October 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.

  1. A list of all of the database principals.
  2. A list of the role memberships for each principal.
  3. 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.

3 thoughts on “Permissions script for Azure Synapse

  1. reitsees says:

    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.

  2. […] 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 […]

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 3,755 other subscribers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: