![]() ![]()
LEFT JOIN sys.database_principals AS imp ON imp. LEFT JOIN sys.schemas AS objschem ON objschem. LEFT JOIN sys.schemas AS permschem ON permschem. LEFT JOIN sys.database_permissions AS perm ON perm. LEFT JOIN sys.server_principals AS ulogin ON ulogin. Higher-level objectsĮLSE OBJECT_NAME(perm.) - General objects SQL STUDIO SEE ALL DATBASES I HAVE ACCESS TO WINDOWS1) List all access provisioned to a SQL user or Windows user/group directly Schema : Name of the schema the object is in. This could also be an Active Directory group. LoginName : SQL or Windows/Active Directory user account. This reflects the type of user/group defined for the SQL Server account. UserType : Value will be either 'SQL User', 'Windows User', or 'Windows Group'. :) /*ġ) List all access provisioned to a SQL user or Windows user/group directlyĢ) List all access provisioned to a SQL user or Windows user/group through a database or application role Hopefully this saves someone else an hour or two of their lives. iw.kuchin: Exclude users sys and INFORMATION_SCHEMA.iw.kuchin: Replace sys.login_token with sys.server_principals as it will show also SQL Logins, not only Windows ones.iw.kuchin: Handle IMPERSONATE permissions.iw.kuchin: For it's better to use obj.type_desc only for OBJECT_OR_COLUMN permission class.Here is a complete version of Jeremy's Aug 2011 query with the changes suggested by Brad (Oct 2011) and iw.kuchin (May 2012) incorporated: Only objects of ours, not the MS objects List all access provisioned to the public role, which everyone gets by default Sys.database_principals memberprinc ON memberprinc. Sys.database_principals roleprinc ON roleprinc. List all access provisioned to a sql user or windows user/group through a database or application role List all access provisioned to a sql user or windows user/group directly Is only populated if the object is a table, view or a table value function. ObjectName : Name of the object that the user/role is assigned permissions on.ĬolumnName : Name of the column of the object that the user/role is assigned permissions on. SQL_SCALAR_FUNCTION, SQL_INLINE_TABLE_VALUED_FUNCTION, SQL_STORED_PROCEDURE, VIEW, etc. ObjectType : Type of object the user/role is assigned permissions on. PermissionState : Reflects the state of the permission type, examples could include GRANT, DENY, etc. Some built in roles have implicit permission This value may not be populated for all roles. Examples could include CONNECT, EXECUTE, SELECTĭELETE, INSERT, ALTER, CONTROL, TAKE OWNERSHIP, VIEW DEFINITION, etc. PermissionType : Type of permissions the user/role has on an object. On the user account, otherwise this will be the name of the role that the user is a member of. This will be null if the associated permissions to the object are defined at directly This reflects the type of user defined for theĭatabaseUserName: Name of the associated user as defined in the database user account. UserType : Value will be either 'SQL User' or 'Windows User'. UserName : SQL or Windows/Active Directory user account. This query is intended to provide a list of permissions that a user has either applied directly to the user account, or throughġ) List all access provisioned to a sql user or windows user/group directlyĢ) List all access provisioned to a sql user or windows user/group through a database or application roleģ) List all access provisioned to the public role SQL STUDIO SEE ALL DATBASES I HAVE ACCESS TO CRACKThis is my first crack at a query, based on Andomar's suggestions. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |