{"id":81309,"date":"2018-10-09T18:14:16","date_gmt":"2018-10-09T18:14:16","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=81309"},"modified":"2019-01-22T16:35:13","modified_gmt":"2019-01-22T16:35:13","slug":"calculating-a-security-principals-complete-effective-rights","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/calculating-a-security-principals-complete-effective-rights\/","title":{"rendered":"Calculating a Security Principal\u2019s Complete Effective Rights"},"content":{"rendered":"

In my blog Calculating a Security Principal\u2019s Effective Rights.<\/a> I built a view, named Utilty.EffectiveSecurity that you could query to fetch a security principal\u2019s 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\u2019s security by finding all of their rights to all of the things that the get rights for.<\/p>\n

The list of possible permissions you can fetch can be retrieved from:<\/p>\n

SELECT DISTINCT class_desc \r\nFROM fn_builtin_permissions(default)  \r\nORDER BY class_desc;  <\/pre>\n
This returns the following 26 types of things that can have permissions assigned and returned by the sys.fn_my_permissions function:<\/p>\n
APPLICATION ROLE
\nASSEMBLY
\nASYMMETRIC KEY
\nAVAILABILITY GROUP
\nCERTIFICATE
\nCONTRACT
\nDATABASE
\nDATABASE SCOPED CREDENTIAL
\nENDPOINT
\nFULLTEXT CATALOG
\nFULLTEXT STOPLIST
\nLOGIN
\nMESSAGE TYPE
\nOBJECT
\nREMOTE SERVICE BINDING
\nROLE
\nROUTE
\nSCHEMA
\nSEARCH PROPERTY LIST
\nSERVER
\nSERVER ROLE
\nSERVICE
\nSYMMETRIC KEY
\nTYPE
\nUSER
\nXML SCHEMA COLLECTION<\/p>\n
In the following code, I added rows for every one of these items, passing in the name of the object from the corresponding catalog view.<\/p>\n
Note: Most SQL that I post to my blog has been tested pretty extensively. I start by testing myself, creating objects, trying all sorts of cases, etc. Then I typically put WAY too much testing code in the blog. This is going to be just the opposite. I tested things\u00a0<\/span>as much as I could to the limitations of a blog entry. There were a few that I didn’t test at all (like the availability group one), and many I just validated that the dbo user returned data for the built-in stuff.<\/em><\/p>\n
I wanted to publish this just so it is there if you want to check\u00a0 a user’s effective rights for all (or any if you just grab part of the code) of these items, it is available.<\/em><\/p>\n
CREATE SCHEMA Utility;\r\nGO\r\nCREATE OR ALTER VIEW Utility.EffectiveSecurity\r\nAS\r\nWITH objects AS (\r\n\tSELECT objects.name AS object_name,\r\n\t\t   schemas.name AS schema_name,\r\n\t\t   object_id, objects.type_desc AS object_type\r\n\tFROM   sys.objects\r\n\t\t\t JOIN sys.schemas\r\n\t\t\t\tON objects.schema_id = schemas.SCHEMA_ID\r\n\tWHERE objects.parent_object_id = 0 \r\n         --no constraints that have the parent_object_id reference or triggers\r\n\t\t)\r\n\tSELECT 'OBJECT'  AS permission_set,\r\n\t\t\tobject_type,\r\n\t\t   schema_name, \r\n\t\t   object_name ,\r\n\t\t   permissions.permission_name \r\n\tFROM objects\r\n\t        CROSS APPLY fn_my_permissions(schema_name + '.' +  OBJECT_NAME, \r\n                                                                  'Object') AS permissions   \r\n\tWHERE  permissions.subentity_name = '' --I am ignoring column level permissions. \r\n\t  --hide this object from view\r\n\t  AND  NOT (objects.schema_name = 'Utility' \r\n                    AND objects.object_name = 'EffectiveSecurity')\r\n\t\r\n\tUNION ALL\r\n\tSELECT 'ASSEMBLY',\r\n\t\t   'ASSEMBLY' AS object_type,\r\n\t\t\t'' AS schema_name,\r\n\t\t\tassemblies.name AS object_name,\r\n\t\t\tpermissions.permission_name\r\n\tFROM sys.assemblies\r\n\t\tCROSS APPLY fn_my_permissions(QUOTENAME(assemblies.name), \r\n                                                               'ASSEMBLY') AS permissions  \r\n\tUNION ALL\r\n\t\r\n\tSELECT 'APPLICATION ROLE',\r\n\t\t   'APPLICATION ROLE' AS object_type,\r\n\t\t\t'' AS schema_name,\r\n\t\t\tdatabase_principals.name AS object_name,\r\n\t\t\tpermissions.permission_name\r\n\tFROM sys.database_principals\r\n\t\tCROSS APPLY fn_my_permissions(QUOTENAME(database_principals.name), \r\n                                                      'APPLICATION ROLE') AS permissions \t\r\n\t\r\n\tUNION ALL\r\n\tSELECT 'ASYMMETRIC KEY',\r\n\t\t   'ASYMMETRIC KEY' AS object_type,\r\n\t\t\t'' AS schema_name,\r\n\t\t\tasymmetric_keys.name AS object_name,\r\n\t\t\tpermissions.permission_name\r\n\tFROM sys.asymmetric_keys\r\n\t\tCROSS APPLY fn_my_permissions(QUOTENAME(asymmetric_keys.name), \r\n                                                        'ASYMMETRIC KEY') AS permissions  \r\n\tUNION ALL\r\n\tSELECT 'AVAILABILITY GROUP',\r\n\t\t   'AVAILABILITY GROUP' AS object_type,\r\n\t\t\t'' AS schema_name,\r\n\t\t\tavailability_groups.name AS object_name,\r\n\t\t\tpermissions.permission_name\r\n\tFROM sys.availability_groups\r\n\t\tCROSS APPLY fn_my_permissions(QUOTENAME(availability_groups.name), \r\n                                                    'AVAILABILITY GROUP') AS permissions  \r\n\tUNION ALL\r\n\tSELECT 'CERTIFICATE',\r\n\t\t   'CERTIFICATE' AS object_type,\r\n\t\t\t'' AS schema_name,\r\n\t\t\tcertificates.name AS object_name,\r\n\t\t\tpermissions.permission_name\r\n\tFROM sys.certificates\r\n\t\tCROSS APPLY fn_my_permissions(QUOTENAME(certificates.name), \r\n                                                           'CERTIFICATE') AS permissions  \r\n\t\r\n\tUNION ALL\r\n\tSELECT 'CONTRACT',\r\n\t\t   'CONTRACT' AS object_type,\r\n\t\t\t'' AS schema_name,\r\n\t\t\tservice_contracts.name AS object_name,\r\n\t\t\tpermissions.permission_name\r\n\tFROM sys.service_contracts\r\n\t\tCROSS APPLY fn_my_permissions(QUOTENAME(service_contracts.name), \r\n                                                           'CONTRACT') AS permissions  \r\n\tUNION ALL\r\n\tSELECT 'DATABASE' AS permission_set,\r\n\t\t\t'' AS object_type,\r\n\t\t\t'' AS schema_name,\r\n\t\t\t'' AS object_name,\r\n\t\t\tpermissions.permission_name\r\n\tFROM    fn_my_permissions(NULL, 'DATABASE') AS permissions \r\n\tUNION ALL\r\n\tSELECT 'DATABASE SCOPED CREDENTIAL',\r\n\t\t   'DATABASE SCOPED CREDENTIAL' AS object_type,\r\n\t\t\t'' AS schema_name,\r\n\t\t\tdatabase_scoped_credentials.name AS object_name,\r\n\t\t\tpermissions.permission_name\r\n\tFROM sys.database_scoped_credentials\r\n\t\tCROSS APPLY fn_my_permissions(QUOTENAME(database_scoped_credentials.name), \r\n\t\t\t\t\t    'DATABASE SCOPED CREDENTIAL') AS permissions\r\n\tUNION ALL\r\n\tSELECT 'ENDPOINT',\r\n\t\t   'ENDPOINT' AS object_type,\r\n\t\t\t'' AS schema_name,\r\n\t\t\tendpoints.name AS object_name,\r\n\t\t\tpermissions.permission_name\r\n\tFROM sys.endpoints\r\n\t\tCROSS APPLY fn_my_permissions(QUOTENAME(endpoints.name), \r\n                                                              'ENDPOINT') AS permissions  \r\n\tUNION ALL\r\n\tSELECT 'FULLTEXT CATALOG',\r\n\t\t   'FULLTEXT CATALOG' AS object_type,\r\n\t\t\t'' AS schema_name,\r\n\t\t\tfulltext_catalogs.name AS object_name,\r\n\t\t\tpermissions.permission_name\r\n\tFROM sys.fulltext_catalogs\r\n\t\tCROSS APPLY fn_my_permissions(QUOTENAME(fulltext_catalogs.name), \r\n                                                       'FULLTEXT CATALOG') AS permissions  \r\n\tUNION ALL\r\n\tSELECT 'FULLTEXT STOPLIST',\r\n\t\t   'FULLTEXT STOPLIST' AS object_type,\r\n\t\t\t'' AS schema_name,\r\n\t\t\tfulltext_stoplists.name AS object_name,\r\n\t\t\tpermissions.permission_name\r\n\tFROM sys.fulltext_stoplists\r\n\t\tCROSS APPLY fn_my_permissions(QUOTENAME(fulltext_stoplists.name), \r\n                                                      'FULLTEXT STOPLIST') AS permissions  \r\n\t\r\n\tUNION ALL\r\n\tSELECT 'LOGIN',\r\n\t\t   'LOGIN' AS object_type,\r\n\t\t\t'' AS schema_name,\r\n\t\t\tserver_principals.name AS object_name,\r\n\t\t\tpermissions.permission_name\r\n\tFROM sys.server_principals\r\n\t\tCROSS APPLY fn_my_permissions(QUOTENAME(server_principals.name), \r\n                                                                  'LOGIN') AS permissions \r\n\tWHERE type_desc <> 'SERVER_ROLE'\r\n\tUNION ALL\r\n\tSELECT 'MESSAGE TYPE',\r\n\t\t   'MESSAGE TYPE' AS object_type,\r\n\t\t\t'' AS schema_name,\r\n\t\t\tservice_message_types.name AS object_name,\r\n\t\t\tpermissions.permission_name\r\n\tFROM sys.service_message_types\r\n\t\tCROSS APPLY fn_my_permissions(QUOTENAME(service_message_types.name), \r\n                                                           'MESSAGE TYPE') AS permissions \r\n\t\r\n\tUNION ALL\r\n\r\n\tSELECT 'REMOTE SERVICE BINDING',\r\n\t       'REMOTE SERVICE BINDING' AS object_type,\r\n\t       '' AS schema_name,\r\n               remote_service_bindings.name AS object_name,\r\n\t       permissions.permission_name\r\n\tFROM sys.remote_service_bindings\r\n\t\tCROSS APPLY fn_my_permissions(QUOTENAME(remote_service_bindings.name), \r\n                                                 'REMOTE SERVICE BINDING') AS permissions \r\n\tUNION ALL\r\n\tSELECT 'ROLE',\r\n\t\t   'ROLE' AS object_type,\r\n\t\t\t'' AS schema_name,\r\n\t\t\tdatabase_principals.name AS object_name,\r\n\t\t\tpermissions.permission_name\r\n\tFROM sys.database_principals\r\n\t\tCROSS APPLY fn_my_permissions(QUOTENAME(database_principals.name), \r\n                                                                  'ROLE') AS permissions \t\r\n\tWHERE name NOT LIKE ('db~_%') ESCAPE '~'\r\n\t  AND name <> 'public'\r\n\r\n\tUNION ALL\r\n\r\n\tSELECT 'ROUTE',\r\n\t\t   'ROUTE' AS object_type,\r\n\t\t\t'' AS schema_name,\r\n\t\t\troutes.name AS object_name,\r\n\t\t\tpermissions.permission_name\r\n\tFROM sys.routes\r\n\t\tCROSS APPLY fn_my_permissions(QUOTENAME(routes.name), \r\n                                                                 'ROUTE') AS permissions  \r\n\t\r\n\tUNION ALL\r\n\tSELECT 'SCHEMA',\r\n\t\t\t'SCHEMA' AS object_type,\r\n\t\t\tschemas.name AS schema_name,\r\n\t\t\t'' AS object_name,\r\n\t\t\tpermissions.permission_name\r\n\tFROM    sys.schemas\r\n\t           CROSS APPLY fn_my_permissions(QUOTENAME(schemas.name), \r\n                                                              'SCHEMA') AS permissions \r\n        --don't include the schemas that should not be used\r\n        WHERE\tschemas.name NOT IN ('INFORMATION_SCHEMA','sys','guest')\r\n\t  AND   schemas.name NOT LIKE ('db~_%') ESCAPE '~'\r\n\r\n\tUNION ALL\r\n\r\n\tSELECT 'SERVER' AS permission_set,\r\n\t\t\t'' AS object_type,\r\n\t\t\t'' AS schema_name,\r\n\t\t\t'' AS object_name,\r\n\t\t\tpermissions.permission_name\r\n\tFROM    fn_my_permissions(NULL, 'SERVER') AS permissions \r\n\t\r\n\tUNION ALL\r\n\r\n\tSELECT 'SERVER ROLE',\r\n\t\t   'SERVER ROLE' AS object_type,\r\n\t\t\t'' AS schema_name,\r\n\t\t\tserver_principals.name AS object_name,\r\n\t\t\tpermissions.permission_name\r\n\tFROM sys.server_principals\r\n\t\tCROSS APPLY fn_my_permissions(QUOTENAME(server_principals.name), \r\n                                                          'SERVER ROLE') AS permissions \r\n\tWHERE type_desc = 'SERVER_ROLE'\r\n\r\n\tUNION ALL\r\n\r\n\tSELECT 'SERVICE',\r\n\t\t   'SERVICE' AS object_type,\r\n\t\t\t'' AS schema_name,\r\n\t\t\t--the column is case sensitive. This could return > 1 row \r\n                        --that your query sees as one if you grouped\r\n\t\t\t--on it, but this is a very low probability\r\n\t\t\tservices.name COLLATE DATABASE_DEFAULT AS object_name,\r\n\t\t\tpermissions.permission_name\r\n\tFROM sys.services\r\n\t\tCROSS APPLY fn_my_permissions(QUOTENAME(services.name), \r\n                                                              'SERVICE') AS permissions  \r\n\tUNION ALL\r\n\tSELECT 'SYMMETRIC KEY',\r\n\t\t   'SYMMETRIC KEY' AS object_type,\r\n\t\t\t'' AS schema_name,\r\n\t\t\tsymmetric_keys.name AS object_name,\r\n\t\t\tpermissions.permission_name\r\n\tFROM sys.symmetric_keys\r\n\t\tCROSS APPLY fn_my_permissions(QUOTENAME(symmetric_keys.name), \r\n                                                        'SYMMETRIC KEY') AS permissions  \r\n\t\t\r\n\tUNION ALL\r\n\r\n\tSELECT 'TYPE',\r\n\t       'TYPE' AS object_type,\r\n\t       '' AS schema_name,\r\n\t       types.name AS object_name,\r\n\t       permissions.permission_name\r\n\tFROM sys.types\r\n\t\tCROSS APPLY fn_my_permissions(QUOTENAME(types.name), \r\n                                                                 'TYPE') AS permissions \r\n\tWHERE is_user_defined = 1\r\n\r\n\tUNION ALL\r\n\r\n\tSELECT 'USER',\r\n\t       'USER' AS object_type,\r\n\t       '' AS schema_name,\r\n\t       database_principals.name AS object_name,\r\n\t       permissions.permission_name\r\n\tFROM sys.database_principals\r\n\t\tCROSS APPLY fn_my_permissions(QUOTENAME(database_principals.name), \r\n                                                                'USER') AS permissions \t\r\n\tWHERE\tname NOT IN ('INFORMATION_SCHEMA','sys','guest')\r\n\r\n\tUNION ALL\r\n\r\n\tSELECT 'XML SCHEMA COLLECTION',\r\n\t       'XML SCHEMA COLLECTION' AS object_type,\r\n\t       '' AS schema_name,\r\n\t       xml_schema_collections.name AS object_name,\r\n\t       permissions.permission_name\r\n\tFROM sys.xml_schema_collections\r\n\t\tCROSS APPLY fn_my_permissions(QUOTENAME(xml_schema_collections.name), \r\n                                                 'XML SCHEMA COLLECTION') AS permissions  \r\nGO\r\n\r\n--let every user check their permissions\r\nGRANT SELECT ON Utility.EffectiveSecurity TO PUBLIC;\r\nGO<\/pre>\n
Note that this can return a LOT of rows, particularly for a dbo\/sa level principal in a database with a lot of objects, users, etc. \u00a0<\/p>\n
 <\/p>\n","protected":false},"excerpt":{"rendered":"
In my blog Calculating a Security Principal\u2019s Effective Rights. I built a view, named Utilty.EffectiveSecurity that you could query to fetch a security principal\u2019s 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……<\/p>\n","protected":false},"author":56085,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2],"tags":[68855],"coauthors":[19684],"class_list":["post-81309","post","type-post","status-publish","format-standard","hentry","category-blogs","tag-sql-provision"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/81309","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/56085"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=81309"}],"version-history":[{"count":4,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/81309\/revisions"}],"predecessor-version":[{"id":81313,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/81309\/revisions\/81313"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=81309"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=81309"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=81309"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=81309"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}