How can I query the list of database roles in a SQL Server 2000 database?

Question

In Sql Server 2000, is it possible to return, via SQL query, a complete list of database roles that exist in a given database?

I know it is possible to see these roles by expanding the Security, Roles, and Database Roles nodes in SQL Server Management Studio, but I'd like to get them through a query that I can parse programmatically.

Screenshot of the nodes in question

To clarify, I'm not looking for a list of users with their roles, but just the list of roles themselves.

score 33 · Accepted Answer · answered Jan 03 '13 at 16:51

33

Every database in SQL Server 2000 has a sysusers system table

Probably something like

Use <MyDatabase>

Select 
  [name]
From
  sysusers
Where
  issqlrole = 1

will do the trick

answered Jan 03 '13 at 16:51

Laurence

10,896
1
25
34

1

I had not realized that sysusers included roles as well as users. I checked your code and it indeed returns the correct data. Many thanks! – Warrior Bob Jan 03 '13 at 16:55
what a funny place to put it. – granadaCoder Apr 07 '16 at 18:45
8

***NB*** `sys.sysusers` is deprecated. From 2008 should be using [`sys.database_principles`](https://msdn.microsoft.com/en-gb/library/ms187328.aspx). – Richard Dec 01 '16 at 10:33
1

^ Should be `sys.database_principals`? – f01 Nov 02 '22 at 22:35

score 10 · Answer 2 · edited Jul 11 '22 at 15:32

10

With our SQL Server 2016 this works for me

Use Sandbox
Select
  name, principal_id
From
  sys.database_principals 
Where
  type = 'R' and principal_id < 16384

where Sandbox is the name of my database. (I'm using SQL with ESRI ArcGIS Enterprise 10.6.)

edited Jul 11 '22 at 15:32

CodeNaked

40,753
6
122
148

answered Jun 15 '18 at 21:44

Brian H Wilson

133
1
8

Should `type = 'R' and principal < 16384` not be `type = 'R' and principal_id < 16384`? – René Nyffenegger Mar 15 '22 at 13:10

How can I query the list of database roles in a SQL Server 2000 database?

2 Answers2