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.