Get User List in a Microsoft SQL Server Database
- by Joe Jr Yamut
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).
- 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.
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.
- > When Remmina Can No Longer Connect To Remote Server October 2, 2013
- > sleeping with the enemy November 12, 2006
- > send text messages via Google Talk October 23, 2006
- > Favorites on your Ubuntu desktop June 25, 2010
- > Access EntityManager From Spring Data JPA In Spring Boot October 20, 2020
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…