Menu
I am using SQL 2000 and SQL 2005.
I want to know which logins have db_owner or db_accessadmin rights to which databases.
I can click on users or database roles in every database to see that. Could this be done in an easier way using TSQL?
Thanks in advance
265
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.
We can use the following query to see the list of all user in the database server: mysql> Select user from mysql.
To view all server role membership requires the VIEW ANY DEFINITION permission or membership in the securityadmin fixed server role. Logins can also view role memberships of roles they own. In Azure SQL Database, members of the server-role ##MS_DefinitionReader## can query all catalog views.
For SQL 2000 and still works for SQL 2005 too
SELECT
USER_NAME(memberuid), USER_NAME(groupuid)
FROM
sys.sysmembers
WHERE
USER_NAME(groupuid) IN ('db_owner', 'db_accessadmin')
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
