SQL Server Permission Query Details

The below queries is useful for getting the server,database and schema level permissions.

User must have securityadmin permission in respective sqlserver in order to view all permissions/

-- Server level admin acces (login level permissions)

-- Author : Malleswara Reddy Mamidi

select distinct sp.name,

sp.type_desc as LoginType,

sp.default_database_name,

ISNULL(sp1.name,'public') as ServerRole,

class_desc as PrincipalClassification,

Permission_name as ServerPermission,

state_desc AS PermissionState

from sys.server_principals sp

left join sys.server_role_members rm on sp.principal_id = rm.member_principal_id

left join sys.server_principals sp1 on sp1.principal_id = rm.role_principal_id

left join sys.server_permissions pm on pm.grantee_principal_id = sp.principal_id

order by sp.name

-- database level admin access

select distinct sp.name as DatabaseUser,

sp.type_desc as UserType,

isnull(sp.default_schema_name,'dbo') as DefaultSchema,

ISNULL(sp1.name,'public') as DatabaseRole,

class_desc as PrincipalClassification,

Permission_name as DatabasePermission,

state_desc AS PermissionState

from sys.database_principals sp

left join sys.database_role_members rm on sp.principal_id = rm.member_principal_id

left join sys.database_principals sp1 on sp1.principal_id = rm.role_principal_id

left join sys.database_permissions pm on pm.grantee_principal_id = sp.principal_id

 order by sp.name

-- Schema level permissions

SELECT pr.principal_id, pr.name, pr.type_desc,  pe.state_desc,

    pe.permission_name, s.name + '.' + o.name AS ObjectName

FROM sys.database_principals AS pr

JOIN sys.database_permissions AS pe

    ON pe.grantee_principal_id = pr.principal_id

JOIN sys.objects AS o

    ON pe.major_id = o.object_id

JOIN sys.schemas AS s

    ON o.schema_id = s.schema_id

            --where --pr.name = 'Public'

            order by pr.name