/*
Copyright (c) 2007 Gustavo G. Duarte (http://duartes.org/gustavo)

This file is licensed under the terms of the MIT/X11 open source license. 
See copying.txt in the root directory of this project (above /src) or 
http://en.wikipedia.org/wiki/MIT_License for the terms of copyright.
*/

-- View database principals
SELECT * FROM sys.database_principals

-- View membership in Database Roles
SELECT Roles.Name, Roles.Type_Desc, Members.Name MemberName, Members.Type_Desc 
FROM sys.database_role_members RoleMembers
INNER JOIN sys.database_principals Roles ON Roles.Principal_Id = RoleMembers.Role_Principal_Id
INNER JOIN sys.database_principals Members ON Members.Principal_Id = RoleMembers.Member_Principal_Id

-- View Permissions in database (you can also run sp_helprotect)
SELECT 
	State_Desc, Permission_Name, class_desc, 
	COALESCE(O.name,DB_NAME(Perms.major_id)) SecurableName,
	SCHEMA_NAME(O.schema_id) [Schema], Grantees.Name Grantee, Grantees.Type_Desc
FROM sys.database_permissions Perms
INNER JOIN sys.database_principals Grantees ON Perms.Grantee_Principal_Id = Grantees.Principal_Id
LEFT OUTER JOIN sys.all_objects O ON Perms.major_id = O.object_id
ORDER BY SecurableName

-- View Server Principals
SELECT * FROM sys.server_principals

-- View membership in Server roles
SELECT Roles.Name, Roles.Type_Desc, Members.Name MemberName, Members.Type_Desc 
FROM sys.server_role_members RoleMembers
INNER JOIN sys.server_principals Roles ON Roles.Principal_Id = RoleMembers.Role_Principal_Id
INNER JOIN sys.server_principals Members ON Members.Principal_Id = RoleMembers.Member_Principal_Id

-- View server permissions
SELECT 
	State_Desc, Permission_Name, class_desc, 
	COALESCE(OBJECT_NAME(major_id),DB_NAME(major_id)) SecurableName, SCHEMA_NAME(O.schema_id) [Schema],
	Grantees.Name GranteeName, Grantees.Type_Desc GranteeType
FROM sys.server_permissions Perms
INNER JOIN sys.server_principals Grantees ON Perms.Grantee_Principal_Id = Grantees.Principal_Id
LEFT OUTER JOIN sys.all_objects O ON Perms.major_id = O.object_id
ORDER BY SecurableName