{"id":627,"date":"2016-10-18T22:17:12","date_gmt":"2016-10-18T22:17:12","guid":{"rendered":"http:\/\/oranotes.tuktaroff.com\/?page_id=627"},"modified":"2016-10-18T22:42:58","modified_gmt":"2016-10-18T22:42:58","slug":"account-permissions","status":"publish","type":"page","link":"http:\/\/oranotes.tuktaroff.com\/?page_id=627","title":{"rendered":"Account permissions"},"content":{"rendered":"<p><code><br \/>\n<script type=\"text\/javascript\"> \nfunction copy(what) {\n    window.clipboardData.setData('Text',what.value);\n}\nfunction paste(what) {\n    what.value = window.clipboardData.getData('Text');\n}\n<\/script><\/p>\n<h2>\nList account permissions in DB<br \/>\n<\/h2>\n<pre class=\"qcode\">\r\nDeclare @user_name VARCHAR(50)\r\nSET @user_name = 'USERNAME_TO_FIND'\r\n\r\nPRINT 'Listing all DB permissions for account ' + @user_name\r\n\r\nselect * from (\r\nSELECT  \r\n    [UserName] = CASE princ.[type] \r\n                    WHEN 'S' THEN princ.[name]\r\n                    WHEN 'U' THEN ulogin.[name] COLLATE Latin1_General_CI_AI\r\n                 END,\r\n    [UserType] = CASE princ.[type]\r\n                    WHEN 'S' THEN 'SQL User'\r\n                    WHEN 'U' THEN 'Windows User'\r\n                 END,  \r\n    [DatabaseUserName] = princ.[name],       \r\n    [Role] = null,      \r\n    [PermissionType] = perm.[permission_name],       \r\n    [PermissionState] = perm.[state_desc],       \r\n    [ObjectType] = obj.type_desc,--perm.[class_desc],       \r\n    [ObjectName] = OBJECT_NAME(perm.major_id),\r\n    [ColumnName] = col.[name]\r\nFROM    \r\n    --database user\r\n    sys.database_principals princ  \r\nLEFT JOIN\r\n    --Login accounts\r\n    sys.login_token ulogin on princ.[sid] = ulogin.[sid]\r\nLEFT JOIN        \r\n    --Permissions\r\n    sys.database_permissions perm ON perm.[grantee_principal_id] = princ.[principal_id]\r\nLEFT JOIN\r\n    --Table columns\r\n    sys.columns col ON col.[object_id] = perm.major_id \r\n                    AND col.[column_id] = perm.[minor_id]\r\nLEFT JOIN\r\n    sys.objects obj ON perm.[major_id] = obj.[object_id]\r\nWHERE \r\n    princ.[type] in ('S','U')\r\nUNION\r\n--List all access provisioned to a sql user or windows user\/group through a database or application role\r\nSELECT  \r\n    [UserName] = CASE memberprinc.[type] \r\n                    WHEN 'S' THEN memberprinc.[name]\r\n                    WHEN 'U' THEN ulogin.[name] COLLATE Latin1_General_CI_AI\r\n                 END,\r\n    [UserType] = CASE memberprinc.[type]\r\n                    WHEN 'S' THEN 'SQL User'\r\n                    WHEN 'U' THEN 'Windows User'\r\n                 END, \r\n    [DatabaseUserName] = memberprinc.[name],   \r\n    [Role] = roleprinc.[name],      \r\n    [PermissionType] = perm.[permission_name],       \r\n    [PermissionState] = perm.[state_desc],       \r\n    [ObjectType] = obj.type_desc,--perm.[class_desc],   \r\n    [ObjectName] = OBJECT_NAME(perm.major_id),\r\n    [ColumnName] = col.[name]\r\nFROM    \r\n    --Role\/member associations\r\n    sys.database_role_members members\r\nJOIN\r\n    --Roles\r\n    sys.database_principals roleprinc ON roleprinc.[principal_id] = members.[role_principal_id]\r\nJOIN\r\n    --Role members (database users)\r\n    sys.database_principals memberprinc ON memberprinc.[principal_id] = members.[member_principal_id]\r\nLEFT JOIN\r\n    --Login accounts\r\n    sys.login_token ulogin on memberprinc.[sid] = ulogin.[sid]\r\nLEFT JOIN        \r\n    --Permissions\r\n    sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id]\r\nLEFT JOIN\r\n    --Table columns\r\n    sys.columns col on col.[object_id] = perm.major_id \r\n                    AND col.[column_id] = perm.[minor_id]\r\nLEFT JOIN\r\n    sys.objects obj ON perm.[major_id] = obj.[object_id]\r\nUNION\r\n--List all access provisioned to the public role, which everyone gets by default\r\nSELECT  \r\n    [UserName] = '{All Users}',\r\n    [UserType] = '{All Users}', \r\n    [DatabaseUserName] = '{All Users}',       \r\n    [Role] = roleprinc.[name],      \r\n    [PermissionType] = perm.[permission_name],       \r\n    [PermissionState] = perm.[state_desc],       \r\n    [ObjectType] = obj.type_desc,--perm.[class_desc],  \r\n    [ObjectName] = OBJECT_NAME(perm.major_id),\r\n    [ColumnName] = col.[name]\r\nFROM    \r\n    --Roles\r\n    sys.database_principals roleprinc\r\nLEFT JOIN        \r\n    --Role permissions\r\n    sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id]\r\nLEFT JOIN\r\n    --Table columns\r\n    sys.columns col on col.[object_id] = perm.major_id \r\n                    AND col.[column_id] = perm.[minor_id]                   \r\nJOIN \r\n    --All objects   \r\n    sys.objects obj ON obj.[object_id] = perm.[major_id]\r\nWHERE\r\n    --Only roles\r\n    roleprinc.[type] = 'R' AND\r\n    --Only public role\r\n    roleprinc.[name] = 'public' AND\r\n    --Only objects of ours, not the MS objects\r\n    obj.is_ms_shipped = 0) a\r\nwhere a.UserName like '%' + @user_name + '%'\r\n\tor a.DatabaseUserName like '%' + @user_name + '%'\r\n<\/pre>\n<form action=\"\"><textarea name=\"data\" cols=\"50\" rows=\"10\" style=\"display:none;\"><br \/>\nDeclare @user_name VARCHAR(50)<br \/>\nSET @user_name = 'USERNAME_TO_FIND'<\/p>\n<p>PRINT 'Listing all DB permissions for account ' + @user_name<\/p>\n<p>select * from (<br \/>\nSELECT<br \/>\n    [UserName] = CASE princ.[type]<br \/>\n                    WHEN 'S' THEN princ.[name]<br \/>\n                    WHEN 'U' THEN ulogin.[name] COLLATE Latin1_General_CI_AI<br \/>\n                 END,<br \/>\n    [UserType] = CASE princ.[type]<br \/>\n                    WHEN 'S' THEN 'SQL User'<br \/>\n                    WHEN 'U' THEN 'Windows User'<br \/>\n                 END,<br \/>\n    [DatabaseUserName] = princ.[name],<br \/>\n    [Role] = null,<br \/>\n    [PermissionType] = perm.[permission_name],<br \/>\n    [PermissionState] = perm.[state_desc],<br \/>\n    [ObjectType] = obj.type_desc,--perm.[class_desc],<br \/>\n    [ObjectName] = OBJECT_NAME(perm.major_id),<br \/>\n    [ColumnName] = col.[name]<br \/>\nFROM<br \/>\n    --database user<br \/>\n    sys.database_principals princ<br \/>\nLEFT JOIN<br \/>\n    --Login accounts<br \/>\n    sys.login_token ulogin on princ.[sid] = ulogin.[sid]<br \/>\nLEFT JOIN<br \/>\n    --Permissions<br \/>\n    sys.database_permissions perm ON perm.[grantee_principal_id] = princ.[principal_id]<br \/>\nLEFT JOIN<br \/>\n    --Table columns<br \/>\n    sys.columns col ON col.[object_id] = perm.major_id<br \/>\n                    AND col.[column_id] = perm.[minor_id]<br \/>\nLEFT JOIN<br \/>\n    sys.objects obj ON perm.[major_id] = obj.[object_id]<br \/>\nWHERE<br \/>\n    princ.[type] in ('S','U')<br \/>\nUNION<br \/>\n--List all access provisioned to a sql user or windows user\/group through a database or application role<br \/>\nSELECT<br \/>\n    [UserName] = CASE memberprinc.[type]<br \/>\n                    WHEN 'S' THEN memberprinc.[name]<br \/>\n                    WHEN 'U' THEN ulogin.[name] COLLATE Latin1_General_CI_AI<br \/>\n                 END,<br \/>\n    [UserType] = CASE memberprinc.[type]<br \/>\n                    WHEN 'S' THEN 'SQL User'<br \/>\n                    WHEN 'U' THEN 'Windows User'<br \/>\n                 END,<br \/>\n    [DatabaseUserName] = memberprinc.[name],<br \/>\n    [Role] = roleprinc.[name],<br \/>\n    [PermissionType] = perm.[permission_name],<br \/>\n    [PermissionState] = perm.[state_desc],<br \/>\n    [ObjectType] = obj.type_desc,--perm.[class_desc],<br \/>\n    [ObjectName] = OBJECT_NAME(perm.major_id),<br \/>\n    [ColumnName] = col.[name]<br \/>\nFROM<br \/>\n    --Role\/member associations<br \/>\n    sys.database_role_members members<br \/>\nJOIN<br \/>\n    --Roles<br \/>\n    sys.database_principals roleprinc ON roleprinc.[principal_id] = members.[role_principal_id]<br \/>\nJOIN<br \/>\n    --Role members (database users)<br \/>\n    sys.database_principals memberprinc ON memberprinc.[principal_id] = members.[member_principal_id]<br \/>\nLEFT JOIN<br \/>\n    --Login accounts<br \/>\n    sys.login_token ulogin on memberprinc.[sid] = ulogin.[sid]<br \/>\nLEFT JOIN<br \/>\n    --Permissions<br \/>\n    sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id]<br \/>\nLEFT JOIN<br \/>\n    --Table columns<br \/>\n    sys.columns col on col.[object_id] = perm.major_id<br \/>\n                    AND col.[column_id] = perm.[minor_id]<br \/>\nLEFT JOIN<br \/>\n    sys.objects obj ON perm.[major_id] = obj.[object_id]<br \/>\nUNION<br \/>\n--List all access provisioned to the public role, which everyone gets by default<br \/>\nSELECT<br \/>\n    [UserName] = '{All Users}',<br \/>\n    [UserType] = '{All Users}',<br \/>\n    [DatabaseUserName] = '{All Users}',<br \/>\n    [Role] = roleprinc.[name],<br \/>\n    [PermissionType] = perm.[permission_name],<br \/>\n    [PermissionState] = perm.[state_desc],<br \/>\n    [ObjectType] = obj.type_desc,--perm.[class_desc],<br \/>\n    [ObjectName] = OBJECT_NAME(perm.major_id),<br \/>\n    [ColumnName] = col.[name]<br \/>\nFROM<br \/>\n    --Roles<br \/>\n    sys.database_principals roleprinc<br \/>\nLEFT JOIN<br \/>\n    --Role permissions<br \/>\n    sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id]<br \/>\nLEFT JOIN<br \/>\n    --Table columns<br \/>\n    sys.columns col on col.[object_id] = perm.major_id<br \/>\n                    AND col.[column_id] = perm.[minor_id]<br \/>\nJOIN<br \/>\n    --All objects<br \/>\n    sys.objects obj ON obj.[object_id] = perm.[major_id]<br \/>\nWHERE<br \/>\n    --Only roles<br \/>\n    roleprinc.[type] = 'R' AND<br \/>\n    --Only public role<br \/>\n    roleprinc.[name] = 'public' AND<br \/>\n    --Only objects of ours, not the MS objects<br \/>\n    obj.is_ms_shipped = 0) a<br \/>\nwhere a.UserName like '%' + @user_name + '%'<br \/>\n\tor a.DatabaseUserName like '%' + @user_name + '%'<br \/>\n<\/textarea><input type=\"button\" value=\"Clipboard\" onclick=\"copy(this.form.data)\"><\/form>\n<p><\/code><\/p>\n","protected":false},"excerpt":{"rendered":"<p>List account permissions in DB Declare @user_name VARCHAR(50) SET @user_name = &#8216;USERNAME_TO_FIND&#8217; PRINT &#8216;Listing all DB permissions for account &#8216; + @user_name select * from ( SELECT [UserName] = CASE princ.[type] WHEN &#8216;S&#8217; THEN princ.[name] WHEN &#8216;U&#8217; THEN ulogin.[name] COLLATE Latin1_General_CI_AI END, [UserType] = CASE princ.[type] WHEN &#8216;S&#8217; THEN &#8216;SQL User&#8217; WHEN &#8216;U&#8217; THEN &#8216;Windows [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":625,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"jetpack_post_was_ever_published":false,"footnotes":""},"class_list":["post-627","page","type-page","status-publish","hentry"],"jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/P2NVny-a7","_links":{"self":[{"href":"http:\/\/oranotes.tuktaroff.com\/index.php?rest_route=\/wp\/v2\/pages\/627","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/oranotes.tuktaroff.com\/index.php?rest_route=\/wp\/v2\/pages"}],"about":[{"href":"http:\/\/oranotes.tuktaroff.com\/index.php?rest_route=\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"http:\/\/oranotes.tuktaroff.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/oranotes.tuktaroff.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=627"}],"version-history":[{"count":4,"href":"http:\/\/oranotes.tuktaroff.com\/index.php?rest_route=\/wp\/v2\/pages\/627\/revisions"}],"predecessor-version":[{"id":631,"href":"http:\/\/oranotes.tuktaroff.com\/index.php?rest_route=\/wp\/v2\/pages\/627\/revisions\/631"}],"up":[{"embeddable":true,"href":"http:\/\/oranotes.tuktaroff.com\/index.php?rest_route=\/wp\/v2\/pages\/625"}],"wp:attachment":[{"href":"http:\/\/oranotes.tuktaroff.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=627"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}