Solved

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

Posted on 2008-10-20
5
1,935 Views
Last Modified: 2012-05-05
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
Comment
Question by:patel99
  • 3
5 Comments
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 22769248
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
 
LVL 32

Accepted Solution

by:
bhess1 earned 125 total points
ID: 22769418
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
 

Author Comment

by:patel99
ID: 22772704
Thanks....bhess1 and DanielWilson......
0
 

Author Comment

by:patel99
ID: 22772715
I found....
exec sp_helprolemember 'db_datareader'     is working as well.
0
 

Author Closing Comment

by:patel99
ID: 31508142
Thanks a lot.....
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question