Jasinski Technical Wiki

Navigation

Home Page
Index
All Pages

Quick Search
»
Advanced Search »

Contributor Links

Create a new Page
Administration
File Management
Login/Logout
Your Profile

Other Wiki Sections

Software

PoweredBy

List Database Permissions - SQL Server

RSS
Modified on Mon, Oct 27, 2008, 5:09 PM by Administrator Categorized as SQL Server
The following T-SQL statement will list all the objects in the database and the permissions for each.

select 
     ObjectName = s.name + '.' + o.name
    ,Permission = p.state_desc + ' ' + p.permission_name
    ,Grantee = dbp.name 

from 
    sys.database_permissions p

    inner join sys.objects o
        on o.object_id = p.major_id

    inner join sys.schemas s
        on o.schema_id = s.schema_id

    inner join sys.database_principals dbp
        on p.grantee_principal_id = dbp.principal_id

where 1=1
--    and o.name in ('MyProcedure', 'InsertUpdateEvent')
    and o.is_ms_shipped = 0
    and o.name not like 'z[_]DeadCode[_]%'
    and o.name not like 'z[_]OldVersion[_]%'
order by 
    ObjectName

ScrewTurn Wiki version 3.0.1.400. Some of the icons created by FamFamFam. Except where noted, all contents Copyright © 1999-2018, Patrick Jasinski.