List of users and rols in DB - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

List of users and rols in DB

Author

Topic

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2010-08-11 : 01:04:23

Dear All,

Can we have single query to SQL 2005 that will give us a list of all users and what roles they have for SQL 2005 server roles and DB roles?

Thanks,
Gangadhar

odanwada
Starting Member

2 Posts

Posted - 2010-08-11 : 02:26:57

WITH perms_cte as

(

select USER_NAME(p.grantee_principal_id) AS principal_name,

dp.principal_id,

dp.type_desc AS principal_type_desc,

p.class_desc,

OBJECT_NAME(p.major_id) AS object_name,

p.permission_name,

p.state_desc AS permission_state_desc

from sys.database_permissions p

inner JOIN sys.database_principals dp

on p.grantee_principal_id = dp.principal_id

)

--users

SELECT p.principal_name, p.principal_type_desc, p.class_desc, p.[object_name], p.permission_name, p.permission_state_desc, cast(NULL as sysname) as role_name

FROM perms_cte p

WHERE principal_type_desc <> 'DATABASE_ROLE'

UNION

--role members

SELECT rm.member_principal_name, rm.principal_type_desc, p.class_desc, p.object_name, p.permission_name, p.permission_state_desc,rm.role_name

FROM perms_cte p

right outer JOIN (

select role_principal_id, dp.type_desc as principal_type_desc, member_principal_id,user_name(member_principal_id) as member_principal_name,user_name(role_principal_id) as role_name--,*

from sys.database_role_members rm

INNER JOIN sys.database_principals dp

ON rm.member_principal_id = dp.principal_id

) rm

ON rm.role_principal_id = p.principal_id

order by 1

Regards,
Omeswar Reddy danwada

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2010-08-11 : 03:28:37

Hi,
I need in a Single query i mean to say that..

For each database..I need to get the User and in a column wise i need DB level role and SQL level role.

If you pls help it would be great.

Example:

Database db_owner db_acceesadmin db_backupoperator
A 0 1 0

like this.

And one more for
Userlogin sysADMIN setupadminprocess
aa 0 1

like this i need ..pls help me..

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources