Menu
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.
995
To find all the role assignments to users in SQL Server database, you can use the following query. SELECT r.name role_principal_name, m.name AS member_principal_name FROM sys. database_role_members rm JOIN sys. database_principals r ON rm.
Querying database roles in SQL Server for a userIn the Server type list box, select Database Engine. In the Server name text box, type the name of the SQL cluster server. In the Authentication list box, choose your SQL Server Authentication method and specify the credentials to use.
Use SQL Server Management StudioIn Object Explorer, connect to an instance of the SQL Server Database Engine, and then expand that instance. To see a list of all databases on the instance, expand Databases.
SQL Server includes a very useful system function sys. fn_my_permissions to list all the permissions of a particular principal (user or login) and this system function will help you list all permissions of a principal on a specific database object (securable).
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
96
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
