CODEWITHSUNDEEP
sql-servert-sql
Robin
Robin

Reputation: 505

Listing ALL users on a specific database

I would like to list all users on a specific database.

When I use the sys.server_principals or sys.sysmembers system views it does not list all the users that appear under the Security -> Users list in the Object Browser in SQL Server Management Studio.

Here is what I attempted:

USE [Database_Name];

SELECT user_name([memberuid]) as [Username]
FROM [sys].[sysmembers];

USE [Database_Name];

SELECT [name]
FROM [sys].[server_principals];

Thanks!

Upvotes: 2

Views: 1271

Answers (1)

Aaron Bertrand
Aaron Bertrand

Reputation: 280252

You should not be using sysmembers or sysusers for this - all the sys___ views are are backward compatibility views and (a) will be dropped from the product at some point and (b) won't necessarily contain current information.

If you are looking for database principals, don't look in sys.server_principals, look in sys.database_principals.

USE your_database;
GO

SELECT name, create_date, type_desc
  FROM sys.database_principals
  WHERE is_fixed_role = 0;

This will include things like guest, INFORMATION_SCHEMA, dbo, public and sys. However it won't include accounts like sa which have inherent rights over the database without needing an explicit principal defined.

Upvotes: 3

Related Questions