How to get the list of all database users

Question

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?

Answer 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?

Answer 2

EXEC sp_helpuser 

or

SELECT * FROM sysusers 

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