Database principal permissions script

With the GUI of SSMS is it not easy to grasp all permission and role membership of a database principal. With the code following, you can get all object level and schema level permissions and role membership. If the principal is member of a role, the role membership is extracted until the end.


--A database principal permissions and role memberships, sql server version 2008

if not exists(select * from tempdb.sys.tables where object_id = OBJECT_ID(N'tempdb.dbo.#Roles'))
create table #Roles (DatabaseName sysname, PrincipalName sysname, RoleName sysname)
else
truncate table #Roles

if not exists(select * from tempdb.sys.tables where object_id = OBJECT_ID(N'tempdb.dbo.#tmpRoles'))
create table #tmpRoles (DatabaseName sysname, PrincipalName sysname, RoleName sysname)
else
truncate table #tmpRoles

declare @principalName varchar(100),  @RoleName sysname
set @principalName = 'OnlyReaderRole'
BEGIN
--Object level permissions
select DB_NAME() as [Database],p.state_desc [State],  p.permission_name,
'[' + (s.name) + '].[' + o.name + ']' [Object Name]
from sys.database_permissions p
inner join sys.sysobjects o on p.major_id=o.id
inner join sys.schemas s on o.uid = s.schema_id
inner join sys.database_principals pr on p.grantee_principal_id =pr.principal_id
where p.major_id > 0 and pr.name = @principalName and o.name not like '%diagram%'

--schema level permissions
select DB_NAME() [Database], p.state_desc [State],  p.permission_name, '[' + (s.name) + ']' [schema Name]
from sys.database_permissions p
inner join sys.schemas s on p.major_id = s.schema_id
inner join sys.database_principals pr on p.grantee_principal_id =pr.principal_id
where p.major_id > 0 and pr.name = @principalName

--Database role membership followed until last point
insert into #tmpRoles select DB_NAME() [DatabaseName], dp.name as [PrincipalName], dr.name [RoleName]
from sys.database_principals dp
inner join sys.database_role_members rm on dp.principal_id=rm.member_principal_id
inner join sys.database_principals dr on rm.role_principal_id = dr.principal_id
where dp.name = @principalName
declare curRoles  insensitive cursor for select RoleName from #tmpRoles
while exists(select 1 from #tmpRoles)
BEGIN
open curRoles
insert into #Roles select * from #tmpRoles
truncate table #tmpRoles
fetch next from curRoles into @RoleName
while @@fetch_status = 0
begin
insert into #tmpRoles select  DB_NAME(), dp.name as [PrincipalName], dr.name [RoleName]
from sys.database_principals dp
inner join sys.database_role_members rm on dp.principal_id=rm.member_principal_id
inner join sys.database_principals dr on rm.role_principal_id = dr.principal_id
where dp.name = @RoleName
fetch next from curRoles into @RoleName
end
close curRoles

END
deallocate curRoles

select * from  #Roles
END