Effective Permissions In SQL Server

Daniel Hutmacher has a helper procedure to assist you in understanding who has what effective rights on a SQL Server box:

Principals, permissions and securables can all inherit each other. A principal could for instance be a group or a role, and will confer its permissions on to its group/role members. One permission can imply a number of other permissions – SELECT, for instance, requires you to also have VIEW DEFINITION rights to the object. Securables are also arranged in a hierarchy, with the server owning databases, which in turn own schemas that own objects, and so on.

To make things even more complicated, if you have multiple conflicting permissions (DENY and GRANT), the strictest rule applies, meaning that the effective permission is DENY.

Read on to get his procedure.  For my money, the best method to get these details is to query sys.fn_my_permissions() but that requires that you be able to impersonate the user whose permissions you want to see.

Related Posts

Using Have I Been Pwned In R

Maelle Salmon shows us how to use the HIBPwned library in R: The alternative title of this blog post is HIBPwned version 0.1.7 has been released! W00t!. Steph’s HIBPwned package utilises the HaveIBeenPwned.com API to check whether email addresses and/or user names have been present in any publicly disclosed data breach. In other words, this package potentially delivers bad news, but useful […]

Read More

Limitations Of Object-Level Security In Tabular Models

Teo Lachev gives us the skinny on object-level security in an Analysis Services Tabular model: Object-level security (OLS) is a frequently requested security feature when implementing semantic models. For example, a current project disallows some sales persons to see sensitive measures, such as Margin and Profit. In Multidimensional, modelers could use cell security to meet […]

Read More

Categories

September 2017
MTWTFSS
« Aug Oct »
 123
45678910
11121314151617
18192021222324
252627282930