SQL Server FAQ - sys.database_principals

Home >> FAQs/Tutorials >> SQL Server FAQ

SQL Server FAQ - sys.database_principals - Listing All User Names

By: FYIcenter.com

How To List All User Names in a Database?

If you want to see a list of all user names defined in a database, you can use the system view, sys.database_principals as shown in this tutorial exercise:

-- Login with sa

-- Select a database
USE FyiCenterData;
GO

-- List all user names
SELECT name, sid, type, type_desc 
   FROM sys.database_principals WHERE type = 'S';
GO
name                 sid                      type type_desc
-------------------- ------------------------ ---- ---------
dbo                  0x01                     S    SQL_USER
guest                0x00                     S    SQL_USER
INFORMATION_SCHEMA   NULL                     S    SQL_USER
sys                  NULL                     S    SQL_USER
Fyi_User             0x5EB8701EAEBAA74F86F... S    SQL_USER
(5 row(s) affected)

As you can see, there are 5 user names defined in "FyiCenterData". 4 are defined by SQL Server during the database creation process. Only the last one "Fyi_User" was defined by you in the previous tutorial.

(Continued on next topic...)