Jose Yamut, Jr

Fond of +rw but seldom has time 4 both. 😎😋🤖

How To Other

Get User List in a Microsoft SQL Server Database

There are 2 ways that I know of. I tend to always do what is easier. That is through the GUI application that is Microsoft SQL Server Management Studio (SSMS).

Option 1

  • Login into Microsoft SQL Server.
  • Select the database.
  • Go to Security. It usually is down at the bottom. Click on the yellow folder icon to expand it if it isn’t already.
  • Users sub-menu is right under it. Open it and the list of users are right there.

First option is quicker. Assuming you are on a Windows OS, of course, and you have SSMS installed (which almost always is if you’re using MS SQL Server).

But what if I’m on a Linux machine or a Mac? What if I’m using a different SQL client application like SQuirrel? Or what if I want it presented in a table format and be able to save it to a spreadsheet or CSV?

Well, I can just get this tabular format through a SQL query.

Option 2

USE <database_name>;
SELECT name AS username,
       type_desc AS type,
       authentication_type_desc AS auth_type
FROM sys.database_principals
ORDER BY type;

Simple query above to get that list of users with additional bonus details. Replace <database_name> with the actual one, less the angle brackets. I normally order them by type. That is because most of the time I want to see the Windows users quickly, without leaving the rest out. Of course, you can filter the list by type by modifying the query above.

USE <database_name>;
SELECT name AS username FROM sys.database_principals;

Or I can make it simpler without the extra columns and sorting order. Just a plain list.

Similar Posts:

There are 2 ways that I know of. I tend to always do what is easier. That is through the GUI application that is Microsoft SQL Server Management Studio (SSMS). Option 1 Login into Microsoft SQL Server. Select the database. Go to Security. It usually is down at the bottom. Click on the yellow folder…

Tags: