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  - gt  Security - gt  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

User · Accepted Answer

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

User · Answer

Whenever you  see  something in the GUI  SSMS  and you re like  that s what I need   you can always run Sql Profiler to fish for the query that was used   Run Sql Profiler   Attach it to your database of course   Then right click in the GUI  in SSMS  and click  Refresh   And then go see what Profiler  catches    I got the below when I was in MyDatabase   Security   Users and clicked  refresh  on the  Users    Again  I didn t come up with the WHERE clause and the LEFT OUTER JOIN  it was a part of the SSMS query   And this query is something that somebody at Microsoft has written  you know  the peeps who know the product inside and out  aka  the experts   so they are familiar with all the weird  flags  in the database   But the SSMS GUI -  Sql Profiler tricks works in many scenarios   SELECT u name AS  Name    Server  Name     quotename CAST          serverproperty N Servername          AS sysname                  Database  Name     quotename db name                   User  Name     quotename u name             AS  Urn   u create date AS  CreateDate   u principal id AS  ID   CAST CASE dp state WHEN N G  THEN 1 WHEN  W  THEN 1 ELSE 0 END AS bit  AS  HasDBAccess  FROM sys database principals AS u LEFT OUTER JOIN sys database permissions AS dp ON dp grantee principal id   u principal id and dp type    CO  WHERE  u type in   U    S    G    C    K    E    X    ORDER BY  Name  ASC

User · Answer

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

User · Answer

EXEC sp helpuser   or  SELECT   FROM sysusers   Both of these select all the users of the current database  not the server

User · Answer

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

User · Answer

Go for this    SELECT name type desc FROM sys sql logins

[sql-server] How to get the list of all database users

Examples related to sql-server