Which user has SHOWPLAN permissions at database level.

The below query will tells us the which user has SHOWPLAN permission on the database level.
DECLARE @Table TABLE
  (ServerName varchar(50),DatabaseName varchar(50),
  [Name] varchar(50),PermissionName varchar(50),StateDesc varchar(50))
  INSERT INTO @Table
  (ServerName,DatabaseName,[Name],PermissionName,StateDesc)
  EXEC sp_MSforeachdb 'USE ?
  SELECT ServerName=@@ServerName,
  DatabaseName=db_name(), p.name, perm.permission_name,perm.STATE_DESC
  FROM sys.database_permissions perm
  INNER JOIN sys.database_principals p ON perm.grantee_principal_id = p.principal_id
  WHERE perm.permission_name = ''SHOWPLAN'' AND [Name] in(''User1'',''User2'',
  ''User3'',''User4j'');'
  SELECT * FROM @Table
  order by [Name] DESC

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