Skip to main content

Database Permissions Script

DatabasePermissions.zip

This time I want to share a simple script to extract database permissions.
The code is based on sys.database_persmissions system view.
It basically contains the object ids, principal ids and related permissions (GRANT, DENY, ...).


SELECT
 CASE
  WHEN P.class IN (0, 3) THEN P.class_desc
  ELSE O.type_desc
 END type_desc,
 CASE
  WHEN P.class IN (0, 3) THEN ''
  ELSE SCHEMA_NAME(O.schema_id)
 END object_schema,
 CASE
  WHEN P.class = 3 THEN SCHEMA_NAME(P.major_id)
  WHEN P.class = 1 THEN OBJECT_NAME(P.major_id)
  ELSE ''
 END object_name,
 ISNULL(C.name, '') column_name,
 P.class_desc,
 USER_NAME(P.grantee_principal_id) grantee,
 P.permission_name,
 P.state_desc,
 USER_NAME(P.grantor_principal_id) grantor,
 CASE P.state
  WHEN 'W' THEN 'GRANT'
  ELSE P.state_desc
 END + ' ' +
 CASE
  WHEN P.class = 0 THEN P.permission_name + ' TO ' + QUOTENAME(USER_NAME(P.grantee_principal_id)) COLLATE database_default
  WHEN P.class = 3 THEN P.permission_name + ' ON SCHEMA::' + QUOTENAME(SCHEMA_NAME(P.major_id)) + ' TO ' + QUOTENAME(USER_NAME(P.grantee_principal_id))
  WHEN P.minor_id <> 0 THEN P.permission_name + ' ON ' + QUOTENAME(SCHEMA_NAME(O.schema_id)) + '.' + QUOTENAME(O.name) + ' (' + QUOTENAME(C.name) + ') TO ' + QUOTENAME(USER_NAME(P.grantee_principal_id))
  ELSE P.permission_name + ' ON ' + QUOTENAME(SCHEMA_NAME(O.schema_id)) + '.' + QUOTENAME(O.name) + ' TO ' + QUOTENAME(USER_NAME(P.grantee_principal_id))
 END +
 CASE P.state
  WHEN 'W' THEN ' WITH GRANT OPTION'
  ELSE ''
 END script
FROM
 sys.database_permissions P
  LEFT JOIN
 sys.all_objects O ON
  O.object_id = P.major_id
  LEFT JOIN
 sys.all_columns C ON
  C.object_id = P.major_id AND
  C.column_id = P.minor_id
WHERE
 NOT
 (
  P.class = 1 AND
  (
   EXISTS (SELECT TOP 1 1 FROM sys.extended_properties EP WHERE EP.major_id = O.object_id AND EP.name = 'microsoft_database_tools_support') OR
   O.is_ms_shipped = 1
  )
 )
ORDER BY
 1,2,3,4,5,6,7,8

Comments

Popular posts from this blog

SqlDbAid DBA tool

Latest version (v2.6.0.0 - 2017-09-03) SqlDbAid.zip
SqlDbAid is an intuitive standalone application that will enable you to easily script database items and data.


SqlDbAid is free for personal and commercial usage.

Main Features SQL Server 2005+ supportText search inside code definition with highlitghtingDBA reports (missing indexes, missing foreign key indexes, indexes status, top queries, table MBytes)One file per object or single file scripts (tables, views, triggers, procedures, functions, indexes...)Select, Insert and Update scriptsTable data insert script creationTable/View data exportOffline database compare tool
Requirements .Net Framework 2.0+ For questions and suggestions write to Miken.

Columns Inconsintency Check

Another hopefully useful script  from my toolbox.

Recently I had to check a quite denormalized database at the end of its development phase.

Frequently data types, length and other field attributes change during development, so I wrote a simple script that highlights possible mistakes (same column name but different definition).

UPDATE: system objects skipped

;WITH cte AS (SELECT c.NAME cl_name ,Schema_name(t.schema_id) +'.'+ t.NAME tb_name ,p.NAME [type],c.max_length ,c.scale ,c.PRECISION FROM sys.tables t INNER JOIN sys.columns c ON c.object_id = t.object_id INNER JOIN sys.types p ON p.user_type_id = c.user_type_id AND p.system_type_id = c.system_type_id WHERENOT( EXISTS (SELECT1FROM sys.extended_properties EP WHERE EP.major_id = t.object_id AND EP.name ='microsoft_database_tools_support')OR t.is_ms_shipped =1))SELECT DISTINCT x.*FROM cte x INNER JOIN cte y ON y.tb_name != x.tb_name AND y.cl_name = x.cl_name AND( y.max_length != x.max_length O…