Re: How to view the contents of a Database Role
- From: Chris <Chris@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Wed, 11 Mar 2009 04:36:00 -0700
Thanks, that will work perfectly.
"Russell Fields" wrote:
What to you mean by 'contents'?
For members of the role:
EXEC sp_helprolemember 'rolename'
For rights granted to the role:
EXEC sp_helprotect @username='rolename'
Or, in SQL Server 2005 and up you can use dynamic management views, such as:
-- Role members
select u.name DatabaseRole, u2.name Member
from sys.database_role_members m
join sys.database_principals u on m.role_principal_id = u.principal_id
join sys.database_principals u2 on m.member_principal_id = u2.principal_id
order by DatabaseRole, Member
-- Rights granted
select u.name principal_name, p.permission_name, p.class_desc,
object_name(p.major_id) ObjectName, state_desc from sys.database_permissions
p join sys.database_principals u
on p.grantee_principal_id = u.principal_id
WHERE p.permission_name <> 'CONNECT'
order by principal_name, ObjectName, p.permission_name
"Chris" <Chris@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
How do I view the contents of a database role I created?
- Prev by Date: RE: No manageable attributes on files of backed up keys and certificat
- Next by Date: RE: No manageable attributes on files of backed up keys and certif
- Previous by thread: Re: How to view the contents of a Database Role
- Next by thread: No manageable attributes on files of backed up keys and certificat