How can I query the list of database roles in a SQL Server 2000 database?
45,596
Solution 1
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
Solution 2
With our SQL Server 2016 this works for me
Use Sandbox
Select
name, principal_id
From
sys.database_principals
Where
type = 'R' and principal < 16384
where Sandbox is the name of my database. (I'm using SQL with ESRI ArcGIS Enterprise 10.6.)
Comments
-
Warrior Bob about 4 years
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.
To clarify, I'm not looking for a list of users with their roles, but just the list of roles themselves.