How to know whether or not there is object-level permission in a database?

• SQL ORACLE

  One Orange Chip

  Points: 27807

  More actions

  June 24, 2010 at 10:38 am

  #223186

  Generating an object-level script is really time-consuming. Is there any way in telling whether or not there is an object-level permission in a database?

  Any input will be greatly appreciated.

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  June 24, 2010 at 10:55 am

  #1184616

  Query sys.database_permissions

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• Ashwani Malik

  Ten Centuries

  Points: 1140

  More actions

  July 8, 2010 at 8:09 am

  #1190258

  Try this

  WITH ObjectLevelPermissions(ObjectName,ObjectType,GranteeName,GrantorName,[SELECT],[DELETE],[INSERT],[UPDATE],[EXECUTE],[VIEW DEFINITION]) AS

  (SELECT ObjectName,

  ObjectType,

  grantee_name,

  grantor_name,

  [SL],

  [DL],

  [IN],

  [UP],

  [EX],

  [VW]

  FROM

  (SELECT object_name(major_id) ObjectName,

  CASE A.Type

  WHEN 'U' THEN 'Table'

  WHEN 'P' THEN 'SQL Stored Procedure'

  WHEN 'FN' THEN 'SQL scalar function'

  WHEN 'V' THEN 'View'

  --WHEN 'IF' THEN 'SQL inline table-valued function'

  --WHEN 'SN' THEN 'Synonym'

  --WHEN 'S' THEN 'System base table'

  --WHEN 'TR' THEN 'SQL DML trigger'

  --WHEN 'PC' THEN 'Assembly (CLR) stored-procedure'

  ELSE 'Miscellaneous'

  END ObjectType,

  grantee.name grantee_name,

  grantor.name grantor_name,

  P.TYPE

  from sys.database_permissions P

  Inner Join sys.all_objects A

  ON A.OBJECT_ID = P.MAJOR_ID

  Inner Join sys.database_principals grantee

  ON P.grantee_principal_id = grantee.principal_id

  Inner Join sys.database_principals grantor

  ON P.grantor_principal_id = grantor.principal_id

  where major_id > 0

  AND A.is_ms_shipped <> 1

  AND A.TYPE IN ('U','P','FN','V')) RawData

  PIVOT

  ( COUNT([Type])

  FOR [Type] IN ([SL],[DL],[IN],[UP],[EX],[VW]) ) AS PivotedData)

  Select @@Servername 'Server Name',

  db_name() 'Database Name', --> Might need to be replaced by a parameter once it is converted to a SP.

  ObjectName,

  ObjectType,

  GranteeName,

  GrantorName,

  CASE [SELECT] WHEN 1 THEN 'Y' ELSE 'N' END 'SELECT',

  CASE [DELETE] WHEN 1 THEN 'Y' ELSE 'N' END 'DELETE',

  CASE [INSERT] WHEN 1 THEN 'Y' ELSE 'N' END 'INSERT',

  CASE [UPDATE] WHEN 1 THEN 'Y' ELSE 'N' END 'UPDATE',

  CASE [EXECUTE] WHEN 1 THEN 'Y' ELSE 'N' END 'EXECUTE',

  CASE [VIEW DEFINITION] WHEN 1 THEN 'Y' ELSE 'N' END 'VIEW DEFINITION'

  fromObjectLevelPermissions

  Orderby ObjectType,ObjectName