Calculating Effective Rights For A SQL Server Principal

Louis Davidson gives us a procedure which provides effective rights for a principal:

In my blog Calculating a Security Principal’s Effective Rights. I built a view, named Utilty.EffectiveSecurity that you could query to fetch a security principal’s rights to objects in a database. In that blog I tested the code and showed how it works. Now I have taken this to the extreme and expanded the view to include all of the user’s security by finding all of their rights to all of the things that the get rights for.

The list of possible permissions you can fetch can be retrieved from:

This returns the following 26 types of things that can have permissions assigned and returned by the sys.fn_my_permissions function:

Read on for the code.

Related Posts

Orphaned Users in SQL Server

Dave Bland walks us through one way to fix an orphaned user: In my many years of working as a DBA, I have encountered many disabled logins.  However, I have never really encountered what looks to be a disabled database user account.  I didn’t even think it was possible to disable a user account in […]

Read More

Permissions Requirements for ML Services

Niels Berglund looks at the permissions required to create external libraries with SQL Server Machine Learning Services: This post is the fourth in a series about installing R packages in SQL Server Machine Learning Services (SQL Server ML Services). To see all posts in the series go to Install R Packages in SQL Server ML Services Series. […]

Read More

Categories

October 2018
MTWTFSS
« Sep Nov »
1234567
891011121314
15161718192021
22232425262728
293031