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.

2 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!

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 )

Google photo

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

Twitter picture

You are commenting using your Twitter 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,152 other followers

Follow me on Twitter

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