[Webinar] Streamline your web hosting managementRegister Today

x
?
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
Medium Priority
?
2,777 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:
Brendt Hess earned 375 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

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

Question has a verified solution.

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

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

612 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