दिलचस्प पोस्ट
कैसे iPhone पर एक रंगीन 1×1 UIImage गतिशील बनाने के लिए? शैल लिपियों में $ @ और $ * के बीच का अंतर क्या है? कमांड लाइन से शानदार टेस्ट (Win7) चेहरालेट (जेएसएफ) और एक्सएचटीएमएल के साथ ग्रहण स्वत: पूर्ण (सामग्री सहायता) अंतर्निर्मित जावास्क्रिप्ट फ़ंक्शंस के स्रोत को कैसे देखें? समूह द्वारा एक वेरिएबल के न्यूनतम मूल्य के अनुरूप पंक्ति निकालें सूची पर एंड्रॉइड स्वाइप करें किसी सूची में डुप्लिकेट ढूंढें और सूचीबद्ध करें? शून्य, शून्य और, उद्देश्य- सी में अशक्त के बीच का अंतर एकाधिक तालिकाओं में रिकॉर्ड जोड़ने के लिए फ़ॉर्म बनाएं MySQL प्रकार पाठ के लिए अधिकतम लंबाई क्या आप कई दृश्यों पर एक UIGStureRecognizer संलग्न कर सकते हैं? सभी संभव तीन अक्षर स्ट्रिंग उत्पन्न करने का सबसे अच्छा तरीका क्या है? स्विफ्ट – प्लास्ट पढ़ें स्थान उपयोगकर्ता को CLLocationManager को तेजी से कैसे प्राप्त करें?

डेटाबेस में सभी उपयोगकर्ताओं के लिए सभी अनुमतियों / एक्सेस खोजने के लिए SQL सर्वर क्वेरी

मैं एक एसक्यूएल 2008 पर एक क्वेरी लिखना चाहूंगा जो सभी उपयोगकर्ताओं की रिपोर्ट करेंगे, जिनके पास एक विशेष डाटाबेस, या डेटाबेस के अंदर ऑब्जेक्ट जैसे तालिकाओं, दृश्य और संग्रहीत कार्यविधियाँ हैं, या तो सीधे या भूमिकाओं के कारण, आदि। रिपोर्ट सुरक्षा ऑडिटिंग प्रयोजनों के लिए उपयोग की जाएगी निश्चित नहीं कि किसी की कोई ऐसी क्वेरी है जो मेरी जरूरतों को पूरी तरह फिट करे, लेकिन उम्मीद है कि मुझे एक अच्छी शुरुआत देनी होगी। या तो SQL 2008, 2005 या 2000 करना होगा, मैं संभवत: आवश्यकतानुसार परिवर्तित कर सकता हूं।

वेब के समाधान से एकत्रित समाधान "डेटाबेस में सभी उपयोगकर्ताओं के लिए सभी अनुमतियों / एक्सेस खोजने के लिए SQL सर्वर क्वेरी"

एंडोमर्स के सुझावों के आधार पर यह एक प्रश्न पर मेरा पहला दरार है यह क्वेरी उन प्रयोजनों की एक सूची प्रदान करने के लिए लक्षित है, जो किसी उपयोगकर्ता ने सीधे उपयोगकर्ता खाते में लागू की है, या उपयोगकर्ता के पास की भूमिकाओं के माध्यम से।

/* Security Audit Report 1) List all access provisioned to a sql user or windows user/group directly 2) List all access provisioned to a sql user or windows user/group through a database or application role 3) List all access provisioned to the public role Columns Returned: UserName : SQL or Windows/Active Directory user cccount. This could also be an Active Directory group. UserType : Value will be either 'SQL User' or 'Windows User'. This reflects the type of user defined for the SQL Server user account. DatabaseUserName: Name of the associated user as defined in the database user account. The database user may not be the same as the server user. Role : The role name. This will be null if the associated permissions to the object are defined at directly on the user account, otherwise this will be the name of the role that the user is a member of. PermissionType : Type of permissions the user/role has on an object. Examples could include CONNECT, EXECUTE, SELECT DELETE, INSERT, ALTER, CONTROL, TAKE OWNERSHIP, VIEW DEFINITION, etc. This value may not be populated for all roles. Some built in roles have implicit permission definitions. PermissionState : Reflects the state of the permission type, examples could include GRANT, DENY, etc. This value may not be populated for all roles. Some built in roles have implicit permission definitions. ObjectType : Type of object the user/role is assigned permissions on. Examples could include USER_TABLE, SQL_SCALAR_FUNCTION, SQL_INLINE_TABLE_VALUED_FUNCTION, SQL_STORED_PROCEDURE, VIEW, etc. This value may not be populated for all roles. Some built in roles have implicit permission definitions. ObjectName : Name of the object that the user/role is assigned permissions on. This value may not be populated for all roles. Some built in roles have implicit permission definitions. ColumnName : Name of the column of the object that the user/role is assigned permissions on. This value is only populated if the object is a table, view or a table value function. */ --List all access provisioned to a sql user or windows user/group directly SELECT [UserName] = CASE princ.[type] WHEN 'S' THEN princ.[name] WHEN 'U' THEN ulogin.[name] COLLATE Latin1_General_CI_AI END, [UserType] = CASE princ.[type] WHEN 'S' THEN 'SQL User' WHEN 'U' THEN 'Windows User' END, [DatabaseUserName] = princ.[name], [Role] = null, [PermissionType] = perm.[permission_name], [PermissionState] = perm.[state_desc], [ObjectType] = obj.type_desc,--perm.[class_desc], [ObjectName] = OBJECT_NAME(perm.major_id), [ColumnName] = col.[name] FROM --database user sys.database_principals princ LEFT JOIN --Login accounts sys.login_token ulogin on princ.[sid] = ulogin.[sid] LEFT JOIN --Permissions sys.database_permissions perm ON perm.[grantee_principal_id] = princ.[principal_id] LEFT JOIN --Table columns sys.columns col ON col.[object_id] = perm.major_id AND col.[column_id] = perm.[minor_id] LEFT JOIN sys.objects obj ON perm.[major_id] = obj.[object_id] WHERE princ.[type] in ('S','U') UNION --List all access provisioned to a sql user or windows user/group through a database or application role SELECT [UserName] = CASE memberprinc.[type] WHEN 'S' THEN memberprinc.[name] WHEN 'U' THEN ulogin.[name] COLLATE Latin1_General_CI_AI END, [UserType] = CASE memberprinc.[type] WHEN 'S' THEN 'SQL User' WHEN 'U' THEN 'Windows User' END, [DatabaseUserName] = memberprinc.[name], [Role] = roleprinc.[name], [PermissionType] = perm.[permission_name], [PermissionState] = perm.[state_desc], [ObjectType] = obj.type_desc,--perm.[class_desc], [ObjectName] = OBJECT_NAME(perm.major_id), [ColumnName] = col.[name] FROM --Role/member associations sys.database_role_members members JOIN --Roles sys.database_principals roleprinc ON roleprinc.[principal_id] = members.[role_principal_id] JOIN --Role members (database users) sys.database_principals memberprinc ON memberprinc.[principal_id] = members.[member_principal_id] LEFT JOIN --Login accounts sys.login_token ulogin on memberprinc.[sid] = ulogin.[sid] LEFT JOIN --Permissions sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id] LEFT JOIN --Table columns sys.columns col on col.[object_id] = perm.major_id AND col.[column_id] = perm.[minor_id] LEFT JOIN sys.objects obj ON perm.[major_id] = obj.[object_id] UNION --List all access provisioned to the public role, which everyone gets by default SELECT [UserName] = '{All Users}', [UserType] = '{All Users}', [DatabaseUserName] = '{All Users}', [Role] = roleprinc.[name], [PermissionType] = perm.[permission_name], [PermissionState] = perm.[state_desc], [ObjectType] = obj.type_desc,--perm.[class_desc], [ObjectName] = OBJECT_NAME(perm.major_id), [ColumnName] = col.[name] FROM --Roles sys.database_principals roleprinc LEFT JOIN --Role permissions sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id] LEFT JOIN --Table columns sys.columns col on col.[object_id] = perm.major_id AND col.[column_id] = perm.[minor_id] JOIN --All objects sys.objects obj ON obj.[object_id] = perm.[major_id] WHERE --Only roles roleprinc.[type] = 'R' AND --Only public role roleprinc.[name] = 'public' AND --Only objects of ours, not the MS objects obj.is_ms_shipped = 0 ORDER BY princ.[Name], OBJECT_NAME(perm.major_id), col.[name], perm.[permission_name], perm.[state_desc], obj.type_desc--perm.[class_desc] 

SQL सर्वर 2005 से, आप उसके लिए सिस्टम दृश्य का उपयोग कर सकते हैं। उदाहरण के लिए, यह क्वेरी एक डेटाबेस में सभी उपयोगकर्ताओं को उनके अधिकारों के साथ सूचीबद्ध करता है:

 select princ.name , princ.type_desc , perm.permission_name , perm.state_desc , perm.class_desc , object_name(perm.major_id) from sys.database_principals princ left join sys.database_permissions perm on perm.grantee_principal_id = princ.principal_id 

ध्यान रखें कि किसी उपयोगकर्ता को भूमिका के माध्यम से भी अधिकार मिल सकता है। उदाहरण के लिए, db_data_reader भूमिका अधिकांश ऑब्जेक्ट पर अधिकारों का select है।

यहां जेरेमी के अगस्त 2011 प्रश्न का एक पूर्ण संस्करण ब्रैड (अक्टूबर 2011) और आईव। कुचिन (मई 2012) द्वारा सुझाए गए परिवर्तनों के साथ शामिल किया गया है:

  1. ब्रैड: स्कीमा के लिए [ObjectType] और [ObjectType]
  2. iw.kuchin: [ObjectType] लिए केवल OBJECT_OR_COLUMN अनुमति वर्ग के लिए OBJECT_OR_COLUMN का उपयोग करना बेहतर है। अन्य सभी मामलों में उपयोग के लिए perm.[class_desc]
  3. iw.kuchin: अनुमोदन अनुमोदन अनुमतियाँ।
  4. iw.kuchin: sys.server_principals साथ sys.login_token को प्रतिस्थापित करें क्योंकि यह एसक्यूएल लॉगिन भी दिखाएगा, न केवल Windows वाले
  5. iw.kuchin: विंडोज समूह शामिल करें
  6. iw.kuchin: उपयोगकर्ताओं को sys और INFORMATION_SCHEMA को छोड़ दें

उम्मीद है कि यह किसी एक या दो घंटे के अपने जीवन बचाता है। :)

 /* Security Audit Report 1) List all access provisioned to a SQL user or Windows user/group directly 2) List all access provisioned to a SQL user or Windows user/group through a database or application role 3) List all access provisioned to the public role Columns Returned: UserType : Value will be either 'SQL User', 'Windows User', or 'Windows Group'. This reflects the type of user/group defined for the SQL Server account. DatabaseUserName: Name of the associated user as defined in the database user account. The database user may not be the same as the server user. LoginName : SQL or Windows/Active Directory user account. This could also be an Active Directory group. Role : The role name. This will be null if the associated permissions to the object are defined at directly on the user account, otherwise this will be the name of the role that the user is a member of. PermissionType : Type of permissions the user/role has on an object. Examples could include CONNECT, EXECUTE, SELECT DELETE, INSERT, ALTER, CONTROL, TAKE OWNERSHIP, VIEW DEFINITION, etc. This value may not be populated for all roles. Some built in roles have implicit permission definitions. PermissionState : Reflects the state of the permission type, examples could include GRANT, DENY, etc. This value may not be populated for all roles. Some built in roles have implicit permission definitions. ObjectType : Type of object the user/role is assigned permissions on. Examples could include USER_TABLE, SQL_SCALAR_FUNCTION, SQL_INLINE_TABLE_VALUED_FUNCTION, SQL_STORED_PROCEDURE, VIEW, etc. This value may not be populated for all roles. Some built in roles have implicit permission definitions. Schema : Name of the schema the object is in. ObjectName : Name of the object that the user/role is assigned permissions on. This value may not be populated for all roles. Some built in roles have implicit permission definitions. ColumnName : Name of the column of the object that the user/role is assigned permissions on. This value is only populated if the object is a table, view or a table value function. */ --1) List all access provisioned to a SQL user or Windows user/group directly SELECT [UserType] = CASE princ.[type] WHEN 'S' THEN 'SQL User' WHEN 'U' THEN 'Windows User' WHEN 'G' THEN 'Windows Group' END, [DatabaseUserName] = princ.[name], [LoginName] = ulogin.[name], [Role] = NULL, [PermissionType] = perm.[permission_name], [PermissionState] = perm.[state_desc], [ObjectType] = CASE perm.[class] WHEN 1 THEN obj.[type_desc] -- Schema-contained objects ELSE perm.[class_desc] -- Higher-level objects END, [Schema] = objschem.[name], [ObjectName] = CASE perm.[class] WHEN 3 THEN permschem.[name] -- Schemas WHEN 4 THEN imp.[name] -- Impersonations ELSE OBJECT_NAME(perm.[major_id]) -- General objects END, [ColumnName] = col.[name] FROM --Database user sys.database_principals AS princ --Login accounts LEFT JOIN sys.server_principals AS ulogin ON ulogin.[sid] = princ.[sid] --Permissions LEFT JOIN sys.database_permissions AS perm ON perm.[grantee_principal_id] = princ.[principal_id] LEFT JOIN sys.schemas AS permschem ON permschem.[schema_id] = perm.[major_id] LEFT JOIN sys.objects AS obj ON obj.[object_id] = perm.[major_id] LEFT JOIN sys.schemas AS objschem ON objschem.[schema_id] = obj.[schema_id] --Table columns LEFT JOIN sys.columns AS col ON col.[object_id] = perm.[major_id] AND col.[column_id] = perm.[minor_id] --Impersonations LEFT JOIN sys.database_principals AS imp ON imp.[principal_id] = perm.[major_id] WHERE princ.[type] IN ('S','U','G') -- No need for these system accounts AND princ.[name] NOT IN ('sys', 'INFORMATION_SCHEMA') UNION --2) List all access provisioned to a SQL user or Windows user/group through a database or application role SELECT [UserType] = CASE membprinc.[type] WHEN 'S' THEN 'SQL User' WHEN 'U' THEN 'Windows User' WHEN 'G' THEN 'Windows Group' END, [DatabaseUserName] = membprinc.[name], [LoginName] = ulogin.[name], [Role] = roleprinc.[name], [PermissionType] = perm.[permission_name], [PermissionState] = perm.[state_desc], [ObjectType] = CASE perm.[class] WHEN 1 THEN obj.[type_desc] -- Schema-contained objects ELSE perm.[class_desc] -- Higher-level objects END, [Schema] = objschem.[name], [ObjectName] = CASE perm.[class] WHEN 3 THEN permschem.[name] -- Schemas WHEN 4 THEN imp.[name] -- Impersonations ELSE OBJECT_NAME(perm.[major_id]) -- General objects END, [ColumnName] = col.[name] FROM --Role/member associations sys.database_role_members AS members --Roles JOIN sys.database_principals AS roleprinc ON roleprinc.[principal_id] = members.[role_principal_id] --Role members (database users) JOIN sys.database_principals AS membprinc ON membprinc.[principal_id] = members.[member_principal_id] --Login accounts LEFT JOIN sys.server_principals AS ulogin ON ulogin.[sid] = membprinc.[sid] --Permissions LEFT JOIN sys.database_permissions AS perm ON perm.[grantee_principal_id] = roleprinc.[principal_id] LEFT JOIN sys.schemas AS permschem ON permschem.[schema_id] = perm.[major_id] LEFT JOIN sys.objects AS obj ON obj.[object_id] = perm.[major_id] LEFT JOIN sys.schemas AS objschem ON objschem.[schema_id] = obj.[schema_id] --Table columns LEFT JOIN sys.columns AS col ON col.[object_id] = perm.[major_id] AND col.[column_id] = perm.[minor_id] --Impersonations LEFT JOIN sys.database_principals AS imp ON imp.[principal_id] = perm.[major_id] WHERE membprinc.[type] IN ('S','U','G') -- No need for these system accounts AND membprinc.[name] NOT IN ('sys', 'INFORMATION_SCHEMA') UNION --3) List all access provisioned to the public role, which everyone gets by default SELECT [UserType] = '{All Users}', [DatabaseUserName] = '{All Users}', [LoginName] = '{All Users}', [Role] = roleprinc.[name], [PermissionType] = perm.[permission_name], [PermissionState] = perm.[state_desc], [ObjectType] = CASE perm.[class] WHEN 1 THEN obj.[type_desc] -- Schema-contained objects ELSE perm.[class_desc] -- Higher-level objects END, [Schema] = objschem.[name], [ObjectName] = CASE perm.[class] WHEN 3 THEN permschem.[name] -- Schemas WHEN 4 THEN imp.[name] -- Impersonations ELSE OBJECT_NAME(perm.[major_id]) -- General objects END, [ColumnName] = col.[name] FROM --Roles sys.database_principals AS roleprinc --Role permissions LEFT JOIN sys.database_permissions AS perm ON perm.[grantee_principal_id] = roleprinc.[principal_id] LEFT JOIN sys.schemas AS permschem ON permschem.[schema_id] = perm.[major_id] --All objects JOIN sys.objects AS obj ON obj.[object_id] = perm.[major_id] LEFT JOIN sys.schemas AS objschem ON objschem.[schema_id] = obj.[schema_id] --Table columns LEFT JOIN sys.columns AS col ON col.[object_id] = perm.[major_id] AND col.[column_id] = perm.[minor_id] --Impersonations LEFT JOIN sys.database_principals AS imp ON imp.[principal_id] = perm.[major_id] WHERE roleprinc.[type] = 'R' AND roleprinc.[name] = 'public' AND obj.[is_ms_shipped] = 0 ORDER BY [UserType], [DatabaseUserName], [LoginName], [Role], [Schema], [ObjectName], [ColumnName], [PermissionType], [PermissionState], [ObjectType] 

स्वीकृत उत्तर पर टिप्पणी नहीं कर सकते इसलिए मैं यहां कुछ टिप्पणियां जोड़ूंगा:

  • स्कीमा मुद्दे पर मैं दूसरा ब्रैड एमएस संदर्भ sys.objects तालिका में केवल स्कीमा- sys.objects ऑब्जेक्ट्स शामिल हैं sys.objects तो "उच्च स्तर" ऑब्जेक्ट्स (यानी हमारे मामले में स्कीमा) के बारे में जानकारी प्राप्त करने के लिए आपको sys.schemas तालिका का उपयोग sys.schemas
  • [ObjectType] लिए केवल OBJECT_OR_COLUMN अनुमति वर्ग के लिए OBJECT_OR_COLUMN का उपयोग करना बेहतर है। अन्य सभी मामलों के लिए उपयोग करें perm.[class_desc]
  • एक अन्य प्रकार की अनुमति है जो इस क्वेरी के साथ इतनी अच्छी तरह से संभाली नहीं है IMPERSONATE प्रतिरूपण के बारे में जानकारी प्राप्त करने के लिए, perm.major_id = imp.principal_id पर sys.database_principals साथ perm.major_id = imp.principal_id जाना चाहिए
  • मेरे अनुभव के साथ sys.server_principals साथ sys.login_token को बदलने के लिए बेहतर है क्योंकि यह एसक्यूएल sys.login_token भी दिखाएगा, न केवल विंडोज़
  • किसी को विंडोज़ समूह की अनुमति देने के लिए अनुमत प्रमुख प्रकारों को 'G' जोड़ना चाहिए
  • साथ ही, कोई परिणामस्वरूप तालिका से उपयोगकर्ता sys और INFORMATION_SCHEMA को बाहर कर सकता है, क्योंकि ये उपयोगकर्ता केवल सेवा के लिए उपयोग किए जाते हैं

मैं सभी प्रस्तावित सुधारों के साथ स्क्रिप्ट का पहला टुकड़ा पोस्ट करूंगा, अन्य भागों को भी बदल दिया जाना चाहिए:

 SELECT [UserName] = ulogin.[name], [UserType] = CASE princ.[type] WHEN 'S' THEN 'SQL User' WHEN 'U' THEN 'Windows User' WHEN 'G' THEN 'Windows Group' END, [DatabaseUserName] = princ.[name], [Role] = null, [PermissionType] = perm.[permission_name], [PermissionState] = perm.[state_desc], [ObjectType] = CASE perm.[class] WHEN 1 THEN obj.type_desc -- Schema-contained objects ELSE perm.[class_desc] -- Higher-level objects END, [ObjectName] = CASE perm.[class] WHEN 1 THEN OBJECT_NAME(perm.major_id) -- General objects WHEN 3 THEN schem.[name] -- Schemas WHEN 4 THEN imp.[name] -- Impersonations END, [ColumnName] = col.[name] FROM --database user sys.database_principals princ LEFT JOIN --Login accounts sys.server_principals ulogin on princ.[sid] = ulogin.[sid] LEFT JOIN --Permissions sys.database_permissions perm ON perm.[grantee_principal_id] = princ.[principal_id] LEFT JOIN --Table columns sys.columns col ON col.[object_id] = perm.major_id AND col.[column_id] = perm.[minor_id] LEFT JOIN sys.objects obj ON perm.[major_id] = obj.[object_id] LEFT JOIN sys.schemas schem ON schem.[schema_id] = perm.[major_id] LEFT JOIN sys.database_principals imp ON imp.[principal_id] = perm.[major_id] WHERE princ.[type] IN ('S','U','G') AND -- No need for these system accounts princ.[name] NOT IN ('sys', 'INFORMATION_SCHEMA') 

बहुत बढ़िया स्क्रिप्ट जेरेमी और योगदानकर्ता! धन्यवाद!

मेरे पास एक एस-टन उपयोगकर्ता है, इसलिए यह चल रहा है कि सभी उपयोगकर्ताओं के लिए एक दुःस्वप्न था। मैं टिप्पणी नहीं जोड़ सकता, इसलिए मैं परिवर्तनों के साथ पूरी लिपि पोस्ट कर रहा हूं। मैंने एक वैरिएबल + जोड़ दिया है, जहां खंड को मैं उपयोगकर्ता नाम (या सभी उपयोगकर्ताओं को रिक्त छोड़ने पर सभी उपयोगकर्ता) में 5 अक्षरों से मेल खाने वाली किसी चीज़ की खोज कर सकता हूं। कुछ खास नहीं है, लेकिन मैंने सोचा कि यह कुछ उपयोग मामलों में सहायक होगा।

 DECLARE @p_userName NVARCHAR(5) = 'UName' -- Specify up to five characters here (or none for all users) /* Security Audit Report 1) List all access provisioned to a sql user or windows user/group directly 2) List all access provisioned to a sql user or windows user/group through a database or application role 3) List all access provisioned to the public role Columns Returned: UserName : SQL or Windows/Active Directory user cccount. This could also be an Active Directory group. UserType : Value will be either 'SQL User' or 'Windows User'. This reflects the type of user defined for the SQL Server user account. DatabaseUserName: Name of the associated user as defined in the database user account. The database user may not be the same as the server user. Role : The role name. This will be null if the associated permissions to the object are defined at directly on the user account, otherwise this will be the name of the role that the user is a member of. PermissionType : Type of permissions the user/role has on an object. Examples could include CONNECT, EXECUTE, SELECT, DELETE, INSERT, ALTER, CONTROL, TAKE OWNERSHIP, VIEW DEFINITION, etc. This value may not be populated for all roles. Some built in roles have implicit permission definitions. PermissionState : Reflects the state of the permission type, examples could include GRANT, DENY, etc. This value may not be populated for all roles. Some built in roles have implicit permission definitions. ObjectType : Type of object the user/role is assigned permissions on. Examples could include USER_TABLE, SQL_SCALAR_FUNCTION, SQL_INLINE_TABLE_VALUED_FUNCTION, SQL_STORED_PROCEDURE, VIEW, etc. This value may not be populated for all roles. Some built in roles have implicit permission definitions. ObjectName : Name of the object that the user/role is assigned permissions on. This value may not be populated for all roles. Some built in roles have implicit permission definitions. ColumnName : Name of the column of the object that the user/role is assigned permissions on. This value is only populated if the object is a table, view or a table value function. */ DECLARE @userName NVARCHAR(4) = @p_UserName + '%' --List all access provisioned to a sql user or windows user/group directly SELECT [UserName] = CASE princ.[type] WHEN 'S' THEN princ.[name] WHEN 'U' THEN ulogin.[name] COLLATE Latin1_General_CI_AI END, [UserType] = CASE princ.[type] WHEN 'S' THEN 'SQL User' WHEN 'U' THEN 'Windows User' END, [DatabaseUserName] = princ.[name], [Role] = null, [PermissionType] = perm.[permission_name], [PermissionState] = perm.[state_desc], [ObjectType] = obj.type_desc,--perm.[class_desc], [ObjectName] = OBJECT_NAME(perm.major_id), [ColumnName] = col.[name] FROM --database user sys.database_principals princ LEFT JOIN --Login accounts sys.login_token ulogin on princ.[sid] = ulogin.[sid] LEFT JOIN --Permissions sys.database_permissions perm ON perm.[grantee_principal_id] = princ.[principal_id] LEFT JOIN --Table columns sys.columns col ON col.[object_id] = perm.major_id AND col.[column_id] = perm.[minor_id] LEFT JOIN sys.objects obj ON perm.[major_id] = obj.[object_id] WHERE princ.[type] in ('S','U') AND princ.[name] LIKE @userName -- Added this line --CSLAGLE UNION --List all access provisioned to a sql user or windows user/group through a database or application role SELECT [UserName] = CASE memberprinc.[type] WHEN 'S' THEN memberprinc.[name] WHEN 'U' THEN ulogin.[name] COLLATE Latin1_General_CI_AI END, [UserType] = CASE memberprinc.[type] WHEN 'S' THEN 'SQL User' WHEN 'U' THEN 'Windows User' END, [DatabaseUserName] = memberprinc.[name], [Role] = roleprinc.[name], [PermissionType] = perm.[permission_name], [PermissionState] = perm.[state_desc], [ObjectType] = obj.type_desc,--perm.[class_desc], [ObjectName] = OBJECT_NAME(perm.major_id), [ColumnName] = col.[name] FROM --Role/member associations sys.database_role_members members JOIN --Roles sys.database_principals roleprinc ON roleprinc.[principal_id] = members.[role_principal_id] JOIN --Role members (database users) sys.database_principals memberprinc ON memberprinc.[principal_id] = members.[member_principal_id] LEFT JOIN --Login accounts sys.login_token ulogin on memberprinc.[sid] = ulogin.[sid] LEFT JOIN --Permissions sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id] LEFT JOIN --Table columns sys.columns col on col.[object_id] = perm.major_id AND col.[column_id] = perm.[minor_id] LEFT JOIN sys.objects obj ON perm.[major_id] = obj.[object_id] WHERE memberprinc.[name] LIKE @userName -- Added this line --CSLAGLE UNION --List all access provisioned to the public role, which everyone gets by default SELECT [UserName] = '{All Users}', [UserType] = '{All Users}', [DatabaseUserName] = '{All Users}', [Role] = roleprinc.[name], [PermissionType] = perm.[permission_name], [PermissionState] = perm.[state_desc], [ObjectType] = obj.type_desc,--perm.[class_desc], [ObjectName] = OBJECT_NAME(perm.major_id), [ColumnName] = col.[name] FROM --Roles sys.database_principals roleprinc LEFT JOIN --Role permissions sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id] LEFT JOIN --Table columns sys.columns col on col.[object_id] = perm.major_id AND col.[column_id] = perm.[minor_id] JOIN --All objects sys.objects obj ON obj.[object_id] = perm.[major_id] WHERE --Only roles roleprinc.[type] = 'R' AND --Only public role roleprinc.[name] = 'public' AND --Only objects of ours, not the MS objects obj.is_ms_shipped = 0 ORDER BY princ.[Name], OBJECT_NAME(perm.major_id), col.[name], perm.[permission_name], perm.[state_desc], obj.type_desc--perm.[class_desc] 

उपरोक्त GetPermissions संसाधित प्रक्रिया अच्छी है, लेकिन यह Sp_msforeachdb का उपयोग करता है जिसका अर्थ है कि यदि आपके एसक्यूएल इंस्टेंस के पास कोई डाटाबेस नाम है जो रिक्त स्थान या डैश और अन्य गैर-सर्वोत्तम-प्रथाओं वाले वर्णों को शामिल करता है मैंने एक संस्करण बनाया है जो Sp_msforeachdb के प्रयोग से बचा जाता है और इसमें दो कॉलम शामिल हैं, जो 1 से संकेत मिलता है – अगर लॉगिन एक सिसैडमिन लॉगिन (IsSysAdminLogin) है और 2 – अगर लॉगिन एक अनाथ उपयोगकर्ता (IsEmptyRow) है

 USE [master] ; GO IF EXISTS ( SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.uspGetPermissionsOfAllLogins_DBsOnColumns') AND [type] in (N'P',N'PC') ) BEGIN DROP PROCEDURE dbo.uspGetPermissionsOfAllLogins_DBsOnColumns ; END GO CREATE PROCEDURE dbo.uspGetPermissionsOfAllLogins_DBsOnColumns AS SET NOCOUNT ON ; BEGIN TRY IF EXISTS ( SELECT * FROM tempdb.dbo.sysobjects WHERE id = object_id(N'[tempdb].dbo.[#permission]') ) DROP TABLE #permission ; IF EXISTS ( SELECT * FROM tempdb.dbo.sysobjects WHERE id = object_id(N'[tempdb].dbo.[#userroles_kk]') ) DROP TABLE #userroles_kk ; IF EXISTS ( SELECT * FROM tempdb.dbo.sysobjects WHERE id = object_id(N'[tempdb].dbo.[#rolemember_kk]') ) DROP TABLE #rolemember_kk ; IF EXISTS ( SELECT * FROM tempdb.dbo.sysobjects WHERE id = object_id(N'[tempdb].dbo.[##db_name]') ) DROP TABLE ##db_name ; DECLARE @db_name VARCHAR(255) ,@sql_text VARCHAR(MAX) ; SET @sql_text = 'CREATE TABLE ##db_name ( LoginUserName VARCHAR(MAX) ,' ; DECLARE cursDBs CURSOR FOR SELECT [name] FROM sys.databases ORDER BY [name] ; OPEN cursDBs ; FETCH NEXT FROM cursDBs INTO @db_name WHILE @@FETCH_STATUS = 0 BEGIN SET @sql_text = @sql_text + QUOTENAME(@db_name) + ' VARCHAR(MAX) ,' FETCH NEXT FROM cursDBs INTO @db_name END CLOSE cursDBs ; SET @sql_text = @sql_text + 'IsSysAdminLogin CHAR(1) ,IsEmptyRow CHAR(1) )' --PRINT @sql_text EXEC (@sql_text) ; DEALLOCATE cursDBs ; DECLARE @RoleName VARCHAR(255) ,@UserName VARCHAR(255) ; CREATE TABLE #permission ( LoginUserName VARCHAR(255) ,databasename VARCHAR(255) ,[role] VARCHAR(255) ) ; DECLARE cursSysSrvPrinName CURSOR FOR SELECT [name] FROM sys.server_principals WHERE [type] IN ( 'S', 'U', 'G' ) AND principal_id > 4 AND [name] NOT LIKE '##%' ORDER BY [name] ; OPEN cursSysSrvPrinName ; FETCH NEXT FROM cursSysSrvPrinName INTO @UserName WHILE @@FETCH_STATUS = 0 BEGIN CREATE TABLE #userroles_kk ( databasename VARCHAR(255) ,[role] VARCHAR(255) ) ; CREATE TABLE #rolemember_kk ( dbrole VARCHAR(255) ,membername VARCHAR(255) ,membersid VARBINARY(2048) ) ; DECLARE cursDatabases CURSOR FAST_FORWARD LOCAL FOR SELECT [name] FROM sys.databases ORDER BY [name] ; OPEN cursDatabases ; DECLARE @DBN VARCHAR(255) ,@sqlText NVARCHAR(4000) ; FETCH NEXT FROM cursDatabases INTO @DBN WHILE @@FETCH_STATUS = 0 BEGIN SET @sqlText = N'USE ' + QUOTENAME(@DBN) + '; TRUNCATE TABLE #RoleMember_kk INSERT INTO #RoleMember_kk EXEC sp_helprolemember INSERT INTO #UserRoles_kk (DatabaseName,[Role]) SELECT db_name(),dbRole FROM #RoleMember_kk WHERE MemberName = ''' + @UserName + ''' ' --PRINT @sqlText ; EXEC sp_executesql @sqlText ; FETCH NEXT FROM cursDatabases INTO @DBN END CLOSE cursDatabases ; DEALLOCATE cursDatabases ; INSERT INTO #permission SELECT @UserName 'user' ,b.name ,u.[role] FROM sys.sysdatabases b LEFT JOIN #userroles_kk u ON QUOTENAME(u.databasename) = QUOTENAME(b.name) ORDER BY 1 ; DROP TABLE #userroles_kk ; DROP TABLE #rolemember_kk ; FETCH NEXT FROM cursSysSrvPrinName INTO @UserName END CLOSE cursSysSrvPrinName ; DEALLOCATE cursSysSrvPrinName ; TRUNCATE TABLE ##db_name ; DECLARE @d1 VARCHAR(MAX) ,@d2 VARCHAR(MAX) ,@d3 VARCHAR(MAX) ,@ss VARCHAR(MAX) ; DECLARE cursPermisTable CURSOR FOR SELECT * FROM #permission ORDER BY 2 DESC ; OPEN cursPermisTable ; FETCH NEXT FROM cursPermisTable INTO @d1,@d2,@d3 WHILE @@FETCH_STATUS = 0 BEGIN IF NOT EXISTS ( SELECT 1 FROM ##db_name WHERE LoginUserName = @d1 ) BEGIN SET @ss = 'INSERT INTO ##db_name(LoginUserName) VALUES (''' + @d1 + ''')' EXEC (@ss) ; SET @ss = 'UPDATE ##db_name SET ' + @d2 + ' = ''' + @d3 + ''' WHERE LoginUserName = ''' + @d1 + '''' EXEC (@ss) ; END ELSE BEGIN DECLARE @var NVARCHAR(MAX) ,@ParmDefinition NVARCHAR(MAX) ,@var1 NVARCHAR(MAX) ; SET @var = N'SELECT @var1 = ' + QUOTENAME(@d2) + ' FROM ##db_name WHERE LoginUserName = ''' + @d1 + '''' ; SET @ParmDefinition = N'@var1 NVARCHAR(600) OUTPUT ' ; EXECUTE Sp_executesql @var,@ParmDefinition,@var1 = @var1 OUTPUT ; SET @var1 = ISNULL(@var1, ' ') ; SET @var = ' UPDATE ##db_name SET ' + @d2 + '=''' + @var1 + ' ' + @d3 + ''' WHERE LoginUserName = ''' + @d1 + ''' ' ; EXEC (@var) ; END FETCH NEXT FROM cursPermisTable INTO @d1,@d2,@d3 END CLOSE cursPermisTable ; DEALLOCATE cursPermisTable ; UPDATE ##db_name SET IsSysAdminLogin = 'Y' FROM ##db_name TT INNER JOIN dbo.syslogins SL ON TT.LoginUserName = SL.[name] WHERE SL.sysadmin = 1 ; DECLARE cursDNamesAsColumns CURSOR FAST_FORWARD LOCAL FOR SELECT [name] FROM tempdb.sys.columns WHERE OBJECT_ID = OBJECT_ID('tempdb..##db_name') AND [name] NOT IN ('LoginUserName','IsEmptyRow') ORDER BY [name] ; OPEN cursDNamesAsColumns ; DECLARE @ColN VARCHAR(255) ,@tSQLText NVARCHAR(4000) ; FETCH NEXT FROM cursDNamesAsColumns INTO @ColN WHILE @@FETCH_STATUS = 0 BEGIN SET @tSQLText = N'UPDATE ##db_name SET IsEmptyRow = ''N'' WHERE IsEmptyRow IS NULL AND ' + QUOTENAME(@ColN) + ' IS NOT NULL ; ' --PRINT @tSQLText ; EXEC sp_executesql @tSQLText ; FETCH NEXT FROM cursDNamesAsColumns INTO @ColN END CLOSE cursDNamesAsColumns ; DEALLOCATE cursDNamesAsColumns ; UPDATE ##db_name SET IsEmptyRow = 'Y' WHERE IsEmptyRow IS NULL ; UPDATE ##db_name SET IsSysAdminLogin = 'N' FROM ##db_name TT INNER JOIN dbo.syslogins SL ON TT.LoginUserName = SL.[name] WHERE SL.sysadmin = 0 ; SELECT * FROM ##db_name ; DROP TABLE ##db_name ; DROP TABLE #permission ; END TRY BEGIN CATCH DECLARE @cursDBs_Status INT ,@cursSysSrvPrinName_Status INT ,@cursDatabases_Status INT ,@cursPermisTable_Status INT ,@cursDNamesAsColumns_Status INT ; SELECT @cursDBs_Status = CURSOR_STATUS('GLOBAL','cursDBs') ,@cursSysSrvPrinName_Status = CURSOR_STATUS('GLOBAL','cursSysSrvPrinName') ,@cursDatabases_Status = CURSOR_STATUS('GLOBAL','cursDatabases') ,@cursPermisTable_Status = CURSOR_STATUS('GLOBAL','cursPermisTable') ,@cursDNamesAsColumns_Status = CURSOR_STATUS('GLOBAL','cursPermisTable') ; IF @cursDBs_Status > -2 BEGIN CLOSE cursDBs ; DEALLOCATE cursDBs ; END IF @cursSysSrvPrinName_Status > -2 BEGIN CLOSE cursSysSrvPrinName ; DEALLOCATE cursSysSrvPrinName ; END IF @cursDatabases_Status > -2 BEGIN CLOSE cursDatabases ; DEALLOCATE cursDatabases ; END IF @cursPermisTable_Status > -2 BEGIN CLOSE cursPermisTable ; DEALLOCATE cursPermisTable ; END IF @cursDNamesAsColumns_Status > -2 BEGIN CLOSE cursDNamesAsColumns ; DEALLOCATE cursDNamesAsColumns ; END SELECT ErrorNum = ERROR_NUMBER(),ErrorMsg = ERROR_MESSAGE() ; END CATCH GO /* EXEC [master].dbo.uspGetPermissionsOfAllLogins_DBsOnColumns ; */ 
 CREATE PROCEDURE Get_permission AS DECLARE @db_name VARCHAR(200), @sql_text VARCHAR(max) SET @sql_text='Create table ##db_name (user_name varchar(max),' DECLARE db_cursor CURSOR FOR SELECT name FROM sys.databases OPEN db_cursor FETCH next FROM db_cursor INTO @db_name WHILE @@FETCH_STATUS = 0 BEGIN SET @sql_text=@sql_text + @db_name + ' varchar(max),' FETCH next FROM db_cursor INTO @db_name END CLOSE db_cursor SET @sql_text=@sql_text + 'Server_perm varchar(max))' EXEC (@sql_text) DEALLOCATE db_cursor DECLARE @RoleName VARCHAR(50) DECLARE @UserName VARCHAR(50) DECLARE @CMD VARCHAR(1000) CREATE TABLE #permission ( user_name VARCHAR(50), databasename VARCHAR(50), role VARCHAR(50) ) DECLARE longspcur CURSOR FOR SELECT name FROM sys.server_principals WHERE type IN ( 'S', 'U', 'G' ) AND principal_id > 4 AND name NOT LIKE '##%' AND name <> 'NT AUTHORITY\SYSTEM' AND name <> 'ONDEMAND\Administrator' AND name NOT LIKE 'steel%' OPEN longspcur FETCH next FROM longspcur INTO @UserName WHILE @@FETCH_STATUS = 0 BEGIN CREATE TABLE #userroles_kk ( databasename VARCHAR(50), role VARCHAR(50) ) CREATE TABLE #rolemember_kk ( dbrole VARCHAR(100), membername VARCHAR(100), membersid VARBINARY(2048) ) SET @CMD = 'use ? truncate table #RoleMember_kk insert into #RoleMember_kk exec sp_helprolemember insert into #UserRoles_kk (DatabaseName, Role) select db_name(), dbRole from #RoleMember_kk where MemberName = ''' + @UserName + '''' EXEC Sp_msforeachdb @CMD INSERT INTO #permission SELECT @UserName 'user', b.name, u.role FROM sys.sysdatabases b LEFT OUTER JOIN #userroles_kk u ON u.databasename = b.name --and u.Role='db_owner' ORDER BY 1 DROP TABLE #userroles_kk; DROP TABLE #rolemember_kk; FETCH next FROM longspcur INTO @UserName END CLOSE longspcur DEALLOCATE longspcur TRUNCATE TABLE ##db_name DECLARE @d1 VARCHAR(max), @d2 VARCHAR(max), @d3 VARCHAR(max), @ss VARCHAR(max) DECLARE perm_cur CURSOR FOR SELECT * FROM #permission ORDER BY 2 DESC OPEN perm_cur FETCH next FROM perm_cur INTO @d1, @d2, @d3 WHILE @@FETCH_STATUS = 0 BEGIN IF NOT EXISTS(SELECT 1 FROM ##db_name WHERE user_name = @d1) BEGIN SET @ss='insert into ##db_name(user_name) values (''' + @d1 + ''')' EXEC (@ss) SET @ss='update ##db_name set ' + @d2 + '=''' + @d3 + ''' where user_name=''' + @d1 + '''' EXEC (@ss) END ELSE BEGIN DECLARE @var NVARCHAR(max), @ParmDefinition NVARCHAR(max), @var1 NVARCHAR(max) SET @var = N'select @var1=' + @d2 + ' from ##db_name where USER_NAME=''' + @d1 + ''''; SET @ParmDefinition = N'@var1 nvarchar(300) OUTPUT'; EXECUTE Sp_executesql @var, @ParmDefinition, @var1=@var1 output; SET @var1=Isnull(@var1, ' ') SET @var= ' update ##db_name set ' + @d2 + '=''' + @var1 + ' ' + @d3 + ''' where user_name=''' + @d1 + ''' ' EXEC (@var) END FETCH next FROM perm_cur INTO @d1, @d2, @d3 END CLOSE perm_cur DEALLOCATE perm_cur SELECT * FROM ##db_name DROP TABLE ##db_name DROP TABLE #permission 

अन्य उत्तर जो मैंने डेटाबेस में संभव है कुछ अनुमतियों को याद किया है। नीचे दिए गए कोड की पहली क्वेरी डेटाबेस की प्रत्येक अनुमति के लिए अनुमति होगी जो सिस्टम ऑब्जेक्ट नहीं है। यह उचित अनुदान के रूप में भी बयान करता है दूसरी क्वेरी सभी भूमिकाओं को मिलती है।

यह प्रत्येक डेटाबेस के लिए चलाया जाना है, लेकिन sp_MSforeachdb के साथ उपयोग करने के लिए बहुत लंबा है यदि आप ऐसा करना चाहते हैं तो आपको सिस्टम को संग्रहीत कार्यविधि के रूप में मास्टर डाटाबेस में जोड़ना होगा।

सभी संभावनाओं को कवर करने के लिए आपको एक स्क्रिप्ट भी होनी चाहिए जो सर्वर स्तर की अनुमतियों की जांच करता है

 SELECT DB_NAME() AS database_name , class , class_desc , major_id , minor_id , grantee_principal_id , grantor_principal_id , databasepermissions.type , permission_name , STATE , state_desc , granteedatabaseprincipal.name AS grantee_name , granteedatabaseprincipal.type_desc AS grantee_type_desc , granteeserverprincipal.name AS grantee_principal_name , granteeserverprincipal.type_desc AS grantee_principal_type_desc , grantor.name AS grantor_name , granted_on_name , permissionstatement + N' TO ' + QUOTENAME(granteedatabaseprincipal.name) + CASE WHEN STATE = N'W' THEN N' WITH GRANT OPTION' ELSE N'' END AS permissionstatement FROM ( SELECT sys.database_permissions.class , sys.database_permissions.class_desc , sys.database_permissions.major_id , sys.database_permissions.minor_id , sys.database_permissions.grantee_principal_id , sys.database_permissions.grantor_principal_id , sys.database_permissions.type , sys.database_permissions.permission_name , sys.database_permissions.state , sys.database_permissions.state_desc , QUOTENAME(CONVERT(NVARCHAR(MAX), DB_NAME())) AS granted_on_name , CASE WHEN sys.database_permissions.state = N'W' THEN N'GRANT' ELSE sys.database_permissions.state_desc END + N' ' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS AS permissionstatement FROM sys.database_permissions WHERE (sys.database_permissions.class = 0) UNION ALL SELECT sys.database_permissions.class , sys.database_permissions.class_desc , sys.database_permissions.major_id , sys.database_permissions.minor_id , sys.database_permissions.grantee_principal_id , sys.database_permissions.grantor_principal_id , sys.database_permissions.type , sys.database_permissions.permission_name , sys.database_permissions.state , sys.database_permissions.state_desc , QUOTENAME(sys.schemas.name) + N'.' + QUOTENAME(sys.objects.name) AS granted_on_name , CASE WHEN sys.database_permissions.state = N'W' THEN N'GRANT' ELSE sys.database_permissions.state_desc END + N' ' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N' ON ' + QUOTENAME(sys.schemas.name) + N'.' + QUOTENAME(sys.objects.name) + COALESCE(N' (' + QUOTENAME(sys.columns.name) + N')', N'') AS permissionstatement FROM sys.database_permissions INNER JOIN sys.objects ON sys.objects.object_id = sys.database_permissions.major_id INNER JOIN sys.schemas ON sys.schemas.schema_id = sys.objects.schema_id LEFT OUTER JOIN sys.columns ON sys.columns.object_id = sys.database_permissions.major_id AND sys.columns.column_id = sys.database_permissions.minor_id WHERE (sys.database_permissions.major_id >= 0) AND (sys.database_permissions.class = 1) UNION ALL SELECT sys.database_permissions.class , sys.database_permissions.class_desc , sys.database_permissions.major_id , sys.database_permissions.minor_id , sys.database_permissions.grantee_principal_id , sys.database_permissions.grantor_principal_id , sys.database_permissions.type , sys.database_permissions.permission_name , sys.database_permissions.state , sys.database_permissions.state_desc , QUOTENAME(sys.schemas.name) AS granted_on_name , CASE WHEN sys.database_permissions.state = N'W' THEN N'GRANT' ELSE sys.database_permissions.state_desc END + N' ' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N' ON SCHEMA::' + QUOTENAME(sys.schemas.name) AS permissionstatement FROM sys.database_permissions INNER JOIN sys.schemas ON sys.schemas.schema_id = sys.database_permissions.major_id WHERE (sys.database_permissions.major_id >= 0) AND (sys.database_permissions.class = 3) UNION ALL SELECT sys.database_permissions.class , sys.database_permissions.class_desc , sys.database_permissions.major_id , sys.database_permissions.minor_id , sys.database_permissions.grantee_principal_id , sys.database_permissions.grantor_principal_id , sys.database_permissions.type , sys.database_permissions.permission_name , sys.database_permissions.state , sys.database_permissions.state_desc , QUOTENAME(targetPrincipal.name) AS granted_on_name , CASE WHEN sys.database_permissions.state = N'W' THEN N'GRANT' ELSE sys.database_permissions.state_desc END + N' ' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N' ON ' + targetPrincipal.type_desc + N'::' + QUOTENAME(targetPrincipal.name) AS permissionstatement FROM sys.database_permissions INNER JOIN sys.database_principals AS targetPrincipal ON targetPrincipal.principal_id = sys.database_permissions.major_id WHERE (sys.database_permissions.major_id >= 0) AND (sys.database_permissions.class = 4) UNION ALL SELECT sys.database_permissions.class , sys.database_permissions.class_desc , sys.database_permissions.major_id , sys.database_permissions.minor_id , sys.database_permissions.grantee_principal_id , sys.database_permissions.grantor_principal_id , sys.database_permissions.type , sys.database_permissions.permission_name , sys.database_permissions.state , sys.database_permissions.state_desc , QUOTENAME(sys.assemblies.name) AS granted_on_name , CASE WHEN sys.database_permissions.state = N'W' THEN N'GRANT' ELSE sys.database_permissions.state_desc END + N' ' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N' ON ASSEMBLY::' + QUOTENAME(sys.assemblies.name) AS permissionstatement FROM sys.database_permissions INNER JOIN sys.assemblies ON sys.assemblies.assembly_id = sys.database_permissions.major_id WHERE (sys.database_permissions.major_id >= 0) AND (sys.database_permissions.class = 5) UNION ALL SELECT sys.database_permissions.class , sys.database_permissions.class_desc , sys.database_permissions.major_id , sys.database_permissions.minor_id , sys.database_permissions.grantee_principal_id , sys.database_permissions.grantor_principal_id , sys.database_permissions.type , sys.database_permissions.permission_name , sys.database_permissions.state , sys.database_permissions.state_desc , QUOTENAME(sys.types.name) AS granted_on_name , CASE WHEN sys.database_permissions.state = N'W' THEN N'GRANT' ELSE sys.database_permissions.state_desc END + N' ' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N' ON TYPE::' + QUOTENAME(sys.types.name) AS permissionstatement FROM sys.database_permissions INNER JOIN sys.types ON sys.types.user_type_id = sys.database_permissions.major_id WHERE (sys.database_permissions.major_id >= 0) AND (sys.database_permissions.class = 6) UNION ALL SELECT sys.database_permissions.class , sys.database_permissions.class_desc , sys.database_permissions.major_id , sys.database_permissions.minor_id , sys.database_permissions.grantee_principal_id , sys.database_permissions.grantor_principal_id , sys.database_permissions.type , sys.database_permissions.permission_name , sys.database_permissions.state , sys.database_permissions.state_desc , QUOTENAME(sys.types.name) AS granted_on_name , CASE WHEN sys.database_permissions.state = N'W' THEN N'GRANT' ELSE sys.database_permissions.state_desc END + N' ' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N' ON TYPE::' + QUOTENAME(sys.types.name) AS permissionstatement FROM sys.database_permissions INNER JOIN sys.types ON sys.types.user_type_id = sys.database_permissions.major_id WHERE (sys.database_permissions.major_id >= 0) AND (sys.database_permissions.class = 6) UNION ALL SELECT sys.database_permissions.class , sys.database_permissions.class_desc , sys.database_permissions.major_id , sys.database_permissions.minor_id , sys.database_permissions.grantee_principal_id , sys.database_permissions.grantor_principal_id , sys.database_permissions.type , sys.database_permissions.permission_name , sys.database_permissions.state , sys.database_permissions.state_desc , QUOTENAME(sys.xml_schema_collections.name COLLATE SQL_Latin1_General_CP1_CI_AS) AS granted_on_name , CASE WHEN sys.database_permissions.state = N'W' THEN N'GRANT' ELSE sys.database_permissions.state_desc END + N' ' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N' ON XML SCHEMA COLLECTION::' + QUOTENAME(sys.xml_schema_collections.name) AS permissionstatement FROM sys.database_permissions INNER JOIN sys.xml_schema_collections ON sys.xml_schema_collections.xml_collection_id = sys.database_permissions.major_id WHERE (sys.database_permissions.major_id >= 0) AND (sys.database_permissions.class = 10) UNION ALL SELECT sys.database_permissions.class , sys.database_permissions.class_desc , sys.database_permissions.major_id , sys.database_permissions.minor_id , sys.database_permissions.grantee_principal_id , sys.database_permissions.grantor_principal_id , sys.database_permissions.type , sys.database_permissions.permission_name , sys.database_permissions.state , sys.database_permissions.state_desc , QUOTENAME(sys.service_message_types.name COLLATE SQL_Latin1_General_CP1_CI_AS) AS granted_on_name , CASE WHEN sys.database_permissions.state = N'W' THEN N'GRANT' ELSE sys.database_permissions.state_desc END + N' ' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N' ON MESSAGE TYPE::' + QUOTENAME(sys.service_message_types.name COLLATE SQL_Latin1_General_CP1_CI_AS) AS permissionstatement FROM sys.database_permissions INNER JOIN sys.service_message_types ON sys.service_message_types.message_type_id = sys.database_permissions.major_id WHERE (sys.database_permissions.major_id >= 0) AND (sys.database_permissions.class = 15) UNION ALL SELECT sys.database_permissions.class , sys.database_permissions.class_desc , sys.database_permissions.major_id , sys.database_permissions.minor_id , sys.database_permissions.grantee_principal_id , sys.database_permissions.grantor_principal_id , sys.database_permissions.type , sys.database_permissions.permission_name , sys.database_permissions.state , sys.database_permissions.state_desc , QUOTENAME(sys.service_contracts.name COLLATE SQL_Latin1_General_CP1_CI_AS) AS granted_on_name , CASE WHEN sys.database_permissions.state = N'W' THEN N'GRANT' ELSE sys.database_permissions.state_desc END + N' ' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N' ON CONTRACT::' + QUOTENAME(sys.service_contracts.name COLLATE SQL_Latin1_General_CP1_CI_AS) AS permissionstatement FROM sys.database_permissions INNER JOIN sys.service_contracts ON sys.service_contracts.service_contract_id = sys.database_permissions.major_id WHERE (sys.database_permissions.major_id >= 0) AND (sys.database_permissions.class = 16) UNION ALL SELECT sys.database_permissions.class , sys.database_permissions.class_desc , sys.database_permissions.major_id , sys.database_permissions.minor_id , sys.database_permissions.grantee_principal_id , sys.database_permissions.grantor_principal_id , sys.database_permissions.type , sys.database_permissions.permission_name , sys.database_permissions.state , sys.database_permissions.state_desc , QUOTENAME(sys.services.name COLLATE SQL_Latin1_General_CP1_CI_AS) AS granted_on_name , CASE WHEN sys.database_permissions.state = N'W' THEN N'GRANT' ELSE sys.database_permissions.state_desc END + N' ' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N' ON SERVICE::' + QUOTENAME(sys.services.name COLLATE SQL_Latin1_General_CP1_CI_AS) AS permissionstatement FROM sys.database_permissions INNER JOIN sys.services ON sys.services.service_id = sys.database_permissions.major_id WHERE (sys.database_permissions.major_id >= 0) AND (sys.database_permissions.class = 17) UNION ALL SELECT sys.database_permissions.class , sys.database_permissions.class_desc , sys.database_permissions.major_id , sys.database_permissions.minor_id , sys.database_permissions.grantee_principal_id , sys.database_permissions.grantor_principal_id , sys.database_permissions.type , sys.database_permissions.permission_name , sys.database_permissions.state , sys.database_permissions.state_desc , QUOTENAME(sys.remote_service_bindings.name COLLATE SQL_Latin1_General_CP1_CI_AS) AS granted_on_name , CASE WHEN sys.database_permissions.state = N'W' THEN N'GRANT' ELSE sys.database_permissions.state_desc END + N' ' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N' ON REMOTE SERVICE BINDING::' + QUOTENAME(sys.remote_service_bindings.name COLLATE SQL_Latin1_General_CP1_CI_AS) AS permissionstatement FROM sys.database_permissions INNER JOIN sys.remote_service_bindings ON sys.remote_service_bindings.remote_service_binding_id = sys.database_permissions.major_id WHERE (sys.database_permissions.major_id >= 0) AND (sys.database_permissions.class = 18) UNION ALL SELECT sys.database_permissions.class , sys.database_permissions.class_desc , sys.database_permissions.major_id , sys.database_permissions.minor_id , sys.database_permissions.grantee_principal_id , sys.database_permissions.grantor_principal_id , sys.database_permissions.type , sys.database_permissions.permission_name , sys.database_permissions.state , sys.database_permissions.state_desc , QUOTENAME(sys.routes.name COLLATE SQL_Latin1_General_CP1_CI_AS) AS granted_on_name , CASE WHEN sys.database_permissions.state = N'W' THEN N'GRANT' ELSE sys.database_permissions.state_desc END + N' ' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N' ON ROUTE::' + QUOTENAME(sys.routes.name COLLATE SQL_Latin1_General_CP1_CI_AS) AS permissionstatement FROM sys.database_permissions INNER JOIN sys.routes ON sys.routes.route_id = sys.database_permissions.major_id WHERE (sys.database_permissions.major_id >= 0) AND (sys.database_permissions.class = 19) UNION ALL SELECT sys.database_permissions.class , sys.database_permissions.class_desc , sys.database_permissions.major_id , sys.database_permissions.minor_id , sys.database_permissions.grantee_principal_id , sys.database_permissions.grantor_principal_id , sys.database_permissions.type , sys.database_permissions.permission_name , sys.database_permissions.state , sys.database_permissions.state_desc , QUOTENAME(sys.symmetric_keys.name) AS granted_on_name , CASE WHEN sys.database_permissions.state = N'W' THEN N'GRANT' ELSE sys.database_permissions.state_desc END + N' ' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N' ON ASYMMETRIC KEY::' + QUOTENAME(sys.symmetric_keys.name) AS permissionstatement FROM sys.database_permissions INNER JOIN sys.symmetric_keys ON sys.symmetric_keys.symmetric_key_id = sys.database_permissions.major_id WHERE (sys.database_permissions.major_id >= 0) AND (sys.database_permissions.class = 24) UNION ALL SELECT sys.database_permissions.class , sys.database_permissions.class_desc , sys.database_permissions.major_id , sys.database_permissions.minor_id , sys.database_permissions.grantee_principal_id , sys.database_permissions.grantor_principal_id , sys.database_permissions.type , sys.database_permissions.permission_name , sys.database_permissions.state , sys.database_permissions.state_desc , QUOTENAME(sys.certificates.name) AS granted_on_name , CASE WHEN sys.database_permissions.state = N'W' THEN N'GRANT' ELSE sys.database_permissions.state_desc END + N' ' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N' ON CERTIFICATE::' + QUOTENAME(sys.certificates.name) AS permissionstatement FROM sys.database_permissions INNER JOIN sys.certificates ON sys.certificates.certificate_id = sys.database_permissions.major_id WHERE (sys.database_permissions.major_id >= 0) AND (sys.database_permissions.class = 25) UNION ALL SELECT sys.database_permissions.class , sys.database_permissions.class_desc , sys.database_permissions.major_id , sys.database_permissions.minor_id , sys.database_permissions.grantee_principal_id , sys.database_permissions.grantor_principal_id , sys.database_permissions.type , sys.database_permissions.permission_name , sys.database_permissions.state , sys.database_permissions.state_desc , QUOTENAME(sys.asymmetric_keys.name) AS granted_on_name , CASE WHEN sys.database_permissions.state = N'W' THEN N'GRANT' ELSE sys.database_permissions.state_desc END + N' ' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N' ON ASYMMETRIC KEY::' + QUOTENAME(sys.asymmetric_keys.name) AS permissionstatement FROM sys.database_permissions INNER JOIN sys.asymmetric_keys ON sys.asymmetric_keys.asymmetric_key_id = sys.database_permissions.major_id WHERE (sys.database_permissions.major_id >= 0) AND (sys.database_permissions.class = 26) ) AS databasepermissions INNER JOIN sys.database_principals AS granteedatabaseprincipal ON granteedatabaseprincipal.principal_id = grantee_principal_id LEFT OUTER JOIN sys.server_principals AS granteeserverprincipal ON granteeserverprincipal.sid = granteedatabaseprincipal.sid INNER JOIN sys.database_principals AS grantor ON grantor.principal_id = grantor_principal_id ORDER BY grantee_name, granted_on_name SELECT roles.name AS role_name , roles.principal_id , roles.type AS role_type , roles.type_desc AS role_type_desc , roles.is_fixed_role AS role_is_fixed_role , memberdatabaseprincipal.name AS member_name , memberdatabaseprincipal.principal_id AS member_principal_id , memberdatabaseprincipal.type AS member_type , memberdatabaseprincipal.type_desc AS member_type_desc , memberdatabaseprincipal.is_fixed_role AS member_is_fixed_role , memberserverprincipal.name AS member_principal_name , memberserverprincipal.type_desc member_principal_type_desc , N'ALTER ROLE ' + QUOTENAME(roles.name) + N' ADD MEMBER ' + QUOTENAME(memberdatabaseprincipal.name) AS AddRoleMembersStatement FROM sys.database_principals AS roles INNER JOIN sys.database_role_members ON sys.database_role_members.role_principal_id = roles.principal_id INNER JOIN sys.database_principals AS memberdatabaseprincipal ON memberdatabaseprincipal.principal_id = sys.database_role_members.member_principal_id LEFT OUTER JOIN sys.server_principals AS memberserverprincipal ON memberserverprincipal.sid = memberdatabaseprincipal.sid ORDER BY role_name , member_name