'How to get the list of all database users

I am going to get the list of all users, including Windows users and 'sa', who have access to a particular database in MS SQL Server. Basically, I would like the list to look like as what is shown in SQL Server Management Studio (i.e. the list that is shown when you expand [databse] -> Security -> Users) with one important exception: I do not want to see the 'dbo' in the list. Rather, I would like to see the actual user who owns the database. So, for example, if 'sa' is the 'dbo', 'sa' must be included in the list instead of 'dbo'. Another note not to be missed is, the list in the SQL Server Management Studio normally shows Windows users in addition to SQL users, And I would like those users to be included as well.

So far, I have been able to come up with the following query:

SELECT * FROM sys.database_principals where (type='S' or type = 'U')

This query is almost right but the problem is it doesn't satisfy the 'dbo' condition.

How can I change this query or should I use another one?

sql-server


Solution 1:[1]

For the SQL Server Owner, you should be able to use:

select suser_sname(owner_sid) as 'Owner', state_desc, *
from sys.databases

For a list of SQL Users:

select * from master.sys.server_principals

Ref. SQL Server Tip: How to find the owner of a database through T-SQL

How do you test for the existence of a user in SQL Server?

Solution 2:[2]

EXEC sp_helpuser

or

SELECT * FROM sysusers

Both of these select all the users of the current database (not the server).

Solution 3:[3]

SELECT name FROM sys.database_principals WHERE
type_desc = 'SQL_USER' AND default_schema_name = 'dbo'

This selects all the users in the SQL server that the administrator created!

Solution 4:[4]

Go for this:

 SELECT name,type_desc FROM sys.sql_logins

Solution 5:[5]

I try to avoid using the "SELECT * " option and just pull what data I want or need. The code below is what I use, you may cull out or add columns and aliases per your needs.

I also us "IIF" (instant if) to replace binary 0 or 1 with a yes or no. It just makes it easier to read for the non-techie that may want this info.

Here is what I use:

SELECT 
    name AS 'User'
  , PRINCIPAL_ID
  , type AS 'User Type'
  , type_desc AS 'Login Type'
  , CAST(create_date AS DATE) AS 'Date Created' 
  , default_database_name AS 'Database Name'
  , IIF(is_fixed_role LIKE 0, 'No', 'Yes') AS 'Is Active'
FROM master.sys.server_principals
WHERE type LIKE 's' OR type LIKE 'u'
ORDER BY [User], [Database Name]; 
GO

Hope this helps.

Solution 6:[6]

To run a query returning users of individual databases, try this:

EXEC sp_MSforeachdb 'USE ? <QUERY HERE>'

This will run a query (and return a result) for each database. So to get all users (probably with a lot of internal users and roles you are not interested in, try:

EXEC sp_MSforeachdb 'USE ?; SELECT DB_NAME(), * FROM sys.database_principals;'

Just apply filters mentioned in other replys to get exactly the subset you are looking for.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Lance Fisher
Solution 2 Vedran
Solution 3 nick zoum
Solution 4
Solution 5 Aubrey Love
Solution 6 user13249425

Related Questions