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.
| Author |
Topic |
|
WJHamel
Aged Yak Warrior
651 Posts |
 Posted - 2013-04-18 : 10:59:28
|
| I need a select statement which show me a list of the system db's, the users associated with each db, the roles associated with each db and the roles associated with each db. i know this is readily accessible from the object explorer, but i need a query resultset that shows me the same. |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-04-18 : 11:31:30
|
| There are only 4 system dbs (if you don't count resource db), so list them explicitly using an IN clause.To list users and roles, use sys.database_principals system view. The one-character type column indicates which type of principal it is. What each of those codes mean is listed here: http://msdn.microsoft.com/en-us/library/ms187328.aspx |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2013-04-18 : 11:43:25
|
| SELECT name FROM sys.databases WHERE database_id<=5EXEC sp_msforeachdb 'USE [?];SELECT ''?'' db_name, name from sys.database_principals where type<>''R'' 'EXEC sp_msforeachdb 'USE [?];SELECT ''?'' db_name, name from sys.database_principals where type=''R'' ' |
|
|
|
|
|
|
Select system dbs