Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2573
  • Last Modified:

How can I get list of users having DB_datareader or DB_datawriter permission among all users having different permission in SQL 2005 ?

Hi,
I want to find out list of users having db_datareader or any other permission in Database. In my database there are about 200 users some of them have DB_datareader permission and some of them DB_data executor and DB_datawriter....How can I find out list of users having different permission.
0
patel99
Asked:
patel99
  • 3
1 Solution
 
Daniel WilsonCommented:
how's this?


Select P.Name, P.type_desc, R.Name as RoleName 
from sys.database_principals P Inner Join 
 sys.database_role_members M on P.Principal_ID = M.member_principal_ID Inner Join
 sys.database_principals R on R.Principal_ID = M.role_principal_ID

Open in new window

0
 
Brendt HessSenior DBACommented:
This code should provide you with the base of what you need:
CREATE TABLE #tmp (
	UserName Sysname NULL,
	groupName Sysname NULL,
	loginName Sysname NULL,
	DefDBName Sysname NULL,
	defSchema Sysname NULL,
	userID Sysname NULL,
	sid Sysname NULL
	)
 
INSERT INTO #tmp
EXECUTE sp_helpuser
 
SELECT *
FROM #tmp t
WHERE groupName = 'db_datareader'
 
DROP TABLE #tmp 

Open in new window

0
 
patel99Author Commented:
Thanks....bhess1 and DanielWilson......
0
 
patel99Author Commented:
I found....
exec sp_helprolemember 'db_datareader'     is working as well.
0
 
patel99Author Commented:
Thanks a lot.....
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now