Query to report database user role membership

Hi All,

I using sql server 2005 sp2. I'm trying to construct a query that'll show me all roles and the users that are in the roles. I'm having trouble figuring how sql server stores this information in the system tables. I think it's based around the database_principals system tables but haven't been able to display the info i need.

If anyone has a query like this can they please help me?

thanks,
Dave
I figured it out. I found the sys.database_role_members which had the info i was after:

select distinct c.name, b.name from sys.database_role_members a
inner join sys.database_principals b on b.principal_id = a.member_principal_id
inner join sys.database_principals c on c.principal_id = a.role_principal_id
and b.type <> 'R

Query to report database user role membership

Hi All,

I using sql server 2005 sp2. I'm trying to construct a query that'll show me all roles and the users that are in the roles. I'm having trouble figuring how sql server stores this information in the system tables. I think it's based around the database_principals system tables but haven't been able to display the info i need.

If anyone has a query like this can they please help me?

thanks,
Dave
I figured it out. I found the sys.database_role_members which had the info i was after:

select distinct c.name, b.name from sys.database_role_members a
inner join sys.database_principals b on b.principal_id = a.member_principal_id
inner join sys.database_principals c on c.principal_id = a.role_principal_id
and b.type <> 'R

Query the Security Login or Security User of the current person logged in

How do you find out which group membership "Login or User" the current user has permissions to. I currently use Suser_name() for the current username but how do I see what security login or security user he is assosicated to?

I didn't understand what you meant by Security login/Security User.

Do you meant schema name of the current user.. If yes then the following query will help you.

Code Snippet

Select

Loginname = SUSER_SNAME()

, SchemaName = USER_NAME()

|||

Hi,

I think you want this:

Code Snippet

SELECT

G.name As "group_name",

U.name As "user_name"

FROM

sys.database_role_members M

JOIN

sys.sysusers G WITH(NOLOCK)ON M.role_principal_id = G.uid

JOIN

sys.sysusers U WITH(NOLOCK)ON M.member_principal_id = U.uid

WHERE

U.name = @.user_name

Regards,

Janos

|||I ran this as my sysadmin and it through back dbo, but when running as a user I get blanks. Where is it pulling the information from?|||

Hi,

why do not use the system stored procedure sp_helpuser?

Code Snippet

declare @.username sysname

set @.username =user_name()

execsp_helpuser @.username

Query the Security Login or Security User of the current person logged in

How do you find out which group membership "Login or User" the current user has permissions to. I currently use Suser_name() for the current username but how do I see what security login or security user he is assosicated to?

I didn't understand what you meant by Security login/Security User.

Do you meant schema name of the current user.. If yes then the following query will help you.

Code Snippet

Select

Loginname = SUSER_SNAME()

, SchemaName = USER_NAME()

|||

Hi,

I think you want this:

Code Snippet

SELECT

G.name As "group_name",

U.name As "user_name"

FROM

sys.database_role_members M

JOIN

sys.sysusers G WITH(NOLOCK) ON M.role_principal_id = G.uid

JOIN

sys.sysusers U WITH(NOLOCK) ON M.member_principal_id = U.uid

WHERE

U.name = @.user_name

Regards,

Janos

|||I ran this as my sysadmin and it through back dbo, but when running as a user I get blanks. Where is it pulling the information from?|||

Hi,

why do not use the system stored procedure sp_helpuser?

Code Snippet

declare @.username sysname

set @.username = user_name()

exec sp_helpuser @.username