Which user has what permissions on table level in all the databases.

DECLARE @Command NVARCHAR(4000)
SELECT @Command='select db_name(db_id(''?''))
,sys.schemas.name ''Schema''
,sys.objects.name Object
,sys.database_principals.name username
,sys.database_permissions.type permissions_type
,sys.database_permissions.permission_name
,sys.database_permissions.state permission_state
,sys.database_permissions.state_desc
,state_desc + '' '' + permission_name + '' on [''+ sys.schemas.name + ''].['' + sys.objects.name + ''] to ['' + sys.database_principals.name + '']'' COLLATE LATIN1_General_CI_AS
from sys.database_permissions join sys.objects
on sys.database_permissions.major_id =sys.objects.object_id join sys.schemas
on sys.objects.schema_id = sys.schemas.schema_id
join sys.database_principals
on sys.database_permissions.grantee_principal_id =sys.database_principals.principal_id
order by 1, 2, 3, 5'
EXEC sp_MSforeachdb @Command

Comments

Popular posts from this blog

System.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out

Pre login Handshake or Connection Timeout Period

Transparent Data Encryption(TDE) with Master Key and Certificate in SQL Server