List all the users for Db role

Question

Post reply

List all the users for Db role

toparsi

SSCrazy

Points: 2292
More actions
February 25, 2010 at 12:53 pm

#221034

Hey Guys,
I need to find out or list all the users within a database which has db_owner role permissions. Or in that case, can I get some query which will list the permissions based on database roles like
db_accessadmin
db_backupoperator
db_datareader
db_datawriter
db_owner and so on
Thanks a ton in advance,
sorry if this was already posted somewhere..
Nick

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 1

For roles within the Database use the following command:

sp_helprolemember

http://msdn.microsoft.com/en-us/library/aa933454(SQL.80).aspx

For Roles at the Server member use the following command:

sp_helpsrvrolemember

http://msdn.microsoft.com/en-us/library/ms188772.aspx

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 2

PC was hanging double post...

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

Welsh Corgi (2/25/2010)
For roles within the Database use the following command:
sp_helprolemember
http://msdn.microsoft.com/en-us/library/aa933454(SQL.80).aspx
For Roles at the Server member use the following command:
sp_helpsrvrolemember
http://msdn.microsoft.com/en-us/library/ms188772.aspx

This is a good suggestion. There are also a number of scripts available in the SSC repository for finding users and role assignment.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

toparsi SSCrazy Points: 2292 More actions · Answer 4

thanks a tonn for that reply, also is there a system table where I can find the same information??

thanks,

Nick

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 5

I'm sorry but I'm in the middle of an install at the moment.

If you open the system SP's I referenced you will be able to identify the System Tables that they reference.

You might even want to acclimate yourself by looking at the system tables in the Master Database.

You can also try searching for the scripts, there are all over the place...

Regards,

MC

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/