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:
|
1 2 3 |
SELECT DISTINCT class_desc FROM fn_builtin_permissions(default) ORDER BY class_desc; |
This returns the following 26 types of things that can have permissions assigned and returned by the sys.fn_my_permissions function:
APPLICATION ROLE
ASSEMBLY
ASYMMETRIC KEY
AVAILABILITY GROUP
CERTIFICATE
CONTRACT
DATABASE
DATABASE SCOPED CREDENTIAL
ENDPOINT
FULLTEXT CATALOG
FULLTEXT STOPLIST
LOGIN
MESSAGE TYPE
OBJECT
REMOTE SERVICE BINDING
ROLE
ROUTE
SCHEMA
SEARCH PROPERTY LIST
SERVER
SERVER ROLE
SERVICE
SYMMETRIC KEY
TYPE
USER
XML SCHEMA COLLECTION
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.
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 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.
I wanted to publish this just so it is there if you want to check a user’s effective rights for all (or any if you just grab part of the code) of these items, it is available.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 |
CREATE SCHEMA Utility; GO CREATE OR ALTER VIEW Utility.EffectiveSecurity AS WITH objects AS ( SELECT objects.name AS object_name, schemas.name AS schema_name, object_id, objects.type_desc AS object_type FROM sys.objects JOIN sys.schemas ON objects.schema_id = schemas.SCHEMA_ID WHERE objects.parent_object_id = 0 --no constraints that have the parent_object_id reference or triggers ) SELECT 'OBJECT' AS permission_set, object_type, schema_name, object_name , permissions.permission_name FROM objects CROSS APPLY fn_my_permissions(schema_name + '.' + OBJECT_NAME, 'Object') AS permissions WHERE permissions.subentity_name = '' --I am ignoring column level permissions. --hide this object from view AND NOT (objects.schema_name = 'Utility' AND objects.object_name = 'EffectiveSecurity') UNION ALL SELECT 'ASSEMBLY', 'ASSEMBLY' AS object_type, '' AS schema_name, assemblies.name AS object_name, permissions.permission_name FROM sys.assemblies CROSS APPLY fn_my_permissions(QUOTENAME(assemblies.name), 'ASSEMBLY') AS permissions UNION ALL SELECT 'APPLICATION ROLE', 'APPLICATION ROLE' AS object_type, '' AS schema_name, database_principals.name AS object_name, permissions.permission_name FROM sys.database_principals CROSS APPLY fn_my_permissions(QUOTENAME(database_principals.name), 'APPLICATION ROLE') AS permissions UNION ALL SELECT 'ASYMMETRIC KEY', 'ASYMMETRIC KEY' AS object_type, '' AS schema_name, asymmetric_keys.name AS object_name, permissions.permission_name FROM sys.asymmetric_keys CROSS APPLY fn_my_permissions(QUOTENAME(asymmetric_keys.name), 'ASYMMETRIC KEY') AS permissions UNION ALL SELECT 'AVAILABILITY GROUP', 'AVAILABILITY GROUP' AS object_type, '' AS schema_name, availability_groups.name AS object_name, permissions.permission_name FROM sys.availability_groups CROSS APPLY fn_my_permissions(QUOTENAME(availability_groups.name), 'AVAILABILITY GROUP') AS permissions UNION ALL SELECT 'CERTIFICATE', 'CERTIFICATE' AS object_type, '' AS schema_name, certificates.name AS object_name, permissions.permission_name FROM sys.certificates CROSS APPLY fn_my_permissions(QUOTENAME(certificates.name), 'CERTIFICATE') AS permissions UNION ALL SELECT 'CONTRACT', 'CONTRACT' AS object_type, '' AS schema_name, service_contracts.name AS object_name, permissions.permission_name FROM sys.service_contracts CROSS APPLY fn_my_permissions(QUOTENAME(service_contracts.name), 'CONTRACT') AS permissions UNION ALL SELECT 'DATABASE' AS permission_set, '' AS object_type, '' AS schema_name, '' AS object_name, permissions.permission_name FROM fn_my_permissions(NULL, 'DATABASE') AS permissions UNION ALL SELECT 'DATABASE SCOPED CREDENTIAL', 'DATABASE SCOPED CREDENTIAL' AS object_type, '' AS schema_name, database_scoped_credentials.name AS object_name, permissions.permission_name FROM sys.database_scoped_credentials CROSS APPLY fn_my_permissions(QUOTENAME(database_scoped_credentials.name), 'DATABASE SCOPED CREDENTIAL') AS permissions UNION ALL SELECT 'ENDPOINT', 'ENDPOINT' AS object_type, '' AS schema_name, endpoints.name AS object_name, permissions.permission_name FROM sys.endpoints CROSS APPLY fn_my_permissions(QUOTENAME(endpoints.name), 'ENDPOINT') AS permissions UNION ALL SELECT 'FULLTEXT CATALOG', 'FULLTEXT CATALOG' AS object_type, '' AS schema_name, fulltext_catalogs.name AS object_name, permissions.permission_name FROM sys.fulltext_catalogs CROSS APPLY fn_my_permissions(QUOTENAME(fulltext_catalogs.name), 'FULLTEXT CATALOG') AS permissions UNION ALL SELECT 'FULLTEXT STOPLIST', 'FULLTEXT STOPLIST' AS object_type, '' AS schema_name, fulltext_stoplists.name AS object_name, permissions.permission_name FROM sys.fulltext_stoplists CROSS APPLY fn_my_permissions(QUOTENAME(fulltext_stoplists.name), 'FULLTEXT STOPLIST') AS permissions UNION ALL SELECT 'LOGIN', 'LOGIN' AS object_type, '' AS schema_name, server_principals.name AS object_name, permissions.permission_name FROM sys.server_principals CROSS APPLY fn_my_permissions(QUOTENAME(server_principals.name), 'LOGIN') AS permissions WHERE type_desc <> 'SERVER_ROLE' UNION ALL SELECT 'MESSAGE TYPE', 'MESSAGE TYPE' AS object_type, '' AS schema_name, service_message_types.name AS object_name, permissions.permission_name FROM sys.service_message_types CROSS APPLY fn_my_permissions(QUOTENAME(service_message_types.name), 'MESSAGE TYPE') AS permissions UNION ALL SELECT 'REMOTE SERVICE BINDING', 'REMOTE SERVICE BINDING' AS object_type, '' AS schema_name, remote_service_bindings.name AS object_name, permissions.permission_name FROM sys.remote_service_bindings CROSS APPLY fn_my_permissions(QUOTENAME(remote_service_bindings.name), 'REMOTE SERVICE BINDING') AS permissions UNION ALL SELECT 'ROLE', 'ROLE' AS object_type, '' AS schema_name, database_principals.name AS object_name, permissions.permission_name FROM sys.database_principals CROSS APPLY fn_my_permissions(QUOTENAME(database_principals.name), 'ROLE') AS permissions WHERE name NOT LIKE ('db~_%') ESCAPE '~' AND name <> 'public' UNION ALL SELECT 'ROUTE', 'ROUTE' AS object_type, '' AS schema_name, routes.name AS object_name, permissions.permission_name FROM sys.routes CROSS APPLY fn_my_permissions(QUOTENAME(routes.name), 'ROUTE') AS permissions UNION ALL SELECT 'SCHEMA', 'SCHEMA' AS object_type, schemas.name AS schema_name, '' AS object_name, permissions.permission_name FROM sys.schemas CROSS APPLY fn_my_permissions(QUOTENAME(schemas.name), 'SCHEMA') AS permissions --don't include the schemas that should not be used WHERE schemas.name NOT IN ('INFORMATION_SCHEMA','sys','guest') AND schemas.name NOT LIKE ('db~_%') ESCAPE '~' UNION ALL SELECT 'SERVER' AS permission_set, '' AS object_type, '' AS schema_name, '' AS object_name, permissions.permission_name FROM fn_my_permissions(NULL, 'SERVER') AS permissions UNION ALL SELECT 'SERVER ROLE', 'SERVER ROLE' AS object_type, '' AS schema_name, server_principals.name AS object_name, permissions.permission_name FROM sys.server_principals CROSS APPLY fn_my_permissions(QUOTENAME(server_principals.name), 'SERVER ROLE') AS permissions WHERE type_desc = 'SERVER_ROLE' UNION ALL SELECT 'SERVICE', 'SERVICE' AS object_type, '' AS schema_name, --the column is case sensitive. This could return > 1 row --that your query sees as one if you grouped --on it, but this is a very low probability services.name COLLATE DATABASE_DEFAULT AS object_name, permissions.permission_name FROM sys.services CROSS APPLY fn_my_permissions(QUOTENAME(services.name), 'SERVICE') AS permissions UNION ALL SELECT 'SYMMETRIC KEY', 'SYMMETRIC KEY' AS object_type, '' AS schema_name, symmetric_keys.name AS object_name, permissions.permission_name FROM sys.symmetric_keys CROSS APPLY fn_my_permissions(QUOTENAME(symmetric_keys.name), 'SYMMETRIC KEY') AS permissions UNION ALL SELECT 'TYPE', 'TYPE' AS object_type, '' AS schema_name, types.name AS object_name, permissions.permission_name FROM sys.types CROSS APPLY fn_my_permissions(QUOTENAME(types.name), 'TYPE') AS permissions WHERE is_user_defined = 1 UNION ALL SELECT 'USER', 'USER' AS object_type, '' AS schema_name, database_principals.name AS object_name, permissions.permission_name FROM sys.database_principals CROSS APPLY fn_my_permissions(QUOTENAME(database_principals.name), 'USER') AS permissions WHERE name NOT IN ('INFORMATION_SCHEMA','sys','guest') UNION ALL SELECT 'XML SCHEMA COLLECTION', 'XML SCHEMA COLLECTION' AS object_type, '' AS schema_name, xml_schema_collections.name AS object_name, permissions.permission_name FROM sys.xml_schema_collections CROSS APPLY fn_my_permissions(QUOTENAME(xml_schema_collections.name), 'XML SCHEMA COLLECTION') AS permissions GO --let every user check their permissions GRANT SELECT ON Utility.EffectiveSecurity TO PUBLIC; GO |
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.
Load comments