1

I'm using SQL Server Management Studio 17 and I'm trying to list all the users for auditing in an organized way with mentioning their database name.

I tried the query below but it's showing the users in different schedules and some users are duplicated.

exec sp_MSforeachdb 'select * from ?.sys.sysusers'

What could I use/try instead?

  • Welcome to DBA.SE. We appreciate your participation. Please consider reading the following article: How do I ask a good question? (Help Centre). Currently your question is very open ended and might be closed as unclear what you're asking or too broad. Consider adding more information to your question. E.g. what do you want to achieve in the end? – John aka hot2use Sep 18 '18 at 8:40
  • I'm curious: What is DBA.SE? I first thought it was a web-site, but no. – Tibor Karaszi Sep 18 '18 at 8:43
  • DBA.SE = dba.StackExchange.com – Sabin Bio Sep 18 '18 at 8:47
3

Users are shown 'duplicated' simply because each database has its own users.

It may help to extend your query a little, so you can see which database each set of users is coming from:

exec sp_MSforeachdb 'select ''?'' as DBNAME, * from ?.sys.sysusers'

Note the important differences between a Login and a User :

A Login is for Authentication.
A Login verifies that you can access the server.
There are two types: SQL Logins and Windows Logins.
Logins are created at the SQL Server instance level (and stored in master).

A User is for Authorization.
Authorization decides what you can do in a database.
A User needs to be linked to a Login.
A User doesn't have a password - the Login decides where that is stored.
It is quite usual to have Users from different databases linked to a single Login on the server.
Users (and their Roles) are stored in each database, not in master (see Security > Users in each database when using SSMS).

  • I see you have used the quoted text format for part of your answer. Could you add the link that the quoted text references? Or is this just your personal summary? And then you are missing part of the text in the sentence A User needs to be linked either to a SQL Login. (I guess you are missing ...or a Windows Authenticated Account). – John aka hot2use Sep 18 '18 at 9:32
  • It is my own summary, I used quoting just to highlight it. The 'either' was a typo (the distinction between SQL and Windows account is made at the Login record, not at the User record). – Peter B Sep 18 '18 at 9:49
  • Yes, I realise that the (Database) User is Linked to a Login. But a Login can be either a SQL Server Login or a Windows Authenticated Account, and because you were using the word either I thought you might be emphasising that fact. :-) – John aka hot2use Sep 18 '18 at 10:58
0

You might want to try out the built-in function sp_helplogins.

Provides information about logins and the users associated with them in each database.

Reference: sp_helplogins (Transact-SQL) (Microsoft | Docs | SQL Server)

If you execute a simple:

 exec sp_helplogins

...you will be presented with two windows.

First Window

The first report contains information about each login specified, as shown in the following table.

Column name   Data type       Description

LoginName     sysname         Login name.

SID           varbinary(85)   Login security identifier (SID).

DefDBName     sysname         Default database that LoginName uses when connecting to an 
                              instance of SQL Server.

DefLangName   sysname         Default language used by LoginName.

Auser         char(5)         Yes = LoginName has an associated user name in a database.
                              No = LoginName does not have an associated user name.

ARemote       char(7)         Yes = LoginName has an associated remote login.
                              No = LoginName does not have an associated login.

Example Output

 LoginName                              | SID                                                                | DefDBName | DefLangName | AUser | ARemote
----------------------------------------+--------------------------------------------------------------------+-----------+-------------+-------+---------
 ##MS_AgentSigningCertificate##         | 0x010600000000000901000000AB808B5B1F45E02462E89246B35D879D459D7BCB | master    | us_english  | yes   | no     
 ##MS_PolicyEventProcessingLogin##      | 0x99533C1F5E0B584889BB3FA05C33ADC1                                 | master    | us_english  | yes   | no     
 ##MS_PolicySigningCertificate##        | 0x0106000000000009010000006DFF93DB1C345982E5FF1D9148CA1CC1A605C5EC | master    | NULL        | NO    | no     
 ##MS_PolicyTsqlExecutionLogin##        | 0xA9EEB439F4762546BD90D163703F6DA1                                 | master    | us_english  | yes   | no     
 ##MS_SmoExtendedSigningCertificate##   | 0x010600000000000901000000044EFFBABE4DF3793E8DA9D60353CD089AC2C183 | master    | NULL        | NO    | no     
 ##MS_SQLAuthenticatorCertificate##     | 0x0106000000000009010000001D9B4659FEDA4633E7989520152C8AFCCAF6430C | master    | NULL        | NO    | no     
 ##MS_SQLReplicationSigningCertificate##| 0x010600000000000901000000195A781158BF37965401996C9645207BFF20F765 | master    | NULL        | NO    | no     
 ##MS_SQLResourceSigningCertificate##   | 0x010600000000000901000000922D2F20EB6613B78484357D75AFFAB066EAC58A | master    | NULL        | NO    | no     
 NT Service\MSSQLSERVER                 | 0x010600000000000550000000E20F4FE7B15874E48E19026478C2DC9AC307B83E | master    | us_english  | NO    | no     
 NT SERVICE\ReportServer                | 0x010600000000000550000000214401ACF066EA342187301080455260EB684BA2 | master    | us_english  | yes   | no     
 NT SERVICE\SQLSERVERAGENT              | 0x010600000000000550000000DCA88F14B79FD47A992A3D8943F829A726066357 | master    | us_english  | NO    | no     
 NT SERVICE\SQLWriter                   | 0x010600000000000550000000732B9753646EF90356745CB675C3AA6CD6B4D28B | master    | us_english  | NO    | no     
 NT SERVICE\Winmgmt                     | 0x0106000000000005500000005A048DDFF9C7430AB450D4E7477A2172AB4170F4 | master    | us_english  | NO    | no     
 NT-AUTORITÄT\Netzwerkdienst            | 0x010100000000000514000000                                         | master    | us_english  | NO    | no     
 NT-AUTORITÄT\SYSTEM                    | 0x010100000000000512000000                                         | master    | us_english  | yes   | no     
 sa                                     | 0x01                                                               | master    | us_english  | yes   | no     
 test                                   | 0x70F0AA94953CE7479682442D59C74CF5                                 | master    | us_english  | yes   | no  

Second Window

The second report contains information about users mapped to each login, and the role memberships of the login as shown in the following table.

Column name   Data type   Description

LoginName     sysname     Login name.

DBName        sysname     Default database that LoginName uses when connecting to an instance of SQL Server.

UserName      sysname     User account that LoginName is mapped to in DBName, and the roles that LoginName is a member of in DBName.

UserOrAlias   char(8)     MemberOf = UserName is a role.
                          User = UserName is a user account.  

Example Output


LoginName                          | DBName                | UserName                          | UserOrAlias
-----------------------------------+-----------------------+ ----------------------------------+-----------
##MS_AgentSigningCertificate##     | master                | ##MS_AgentSigningCertificate##    | User             
##MS_PolicyEventProcessingLogin##  | master                | ##MS_PolicyEventProcessingLogin## | User             
##MS_PolicyEventProcessingLogin##  | msdb                  | ##MS_PolicyEventProcessingLogin## | User             
##MS_PolicyEventProcessingLogin##  | msdb                  | PolicyAdministratorRole           | MemberOf
##MS_PolicyTsqlExecutionLogin##    | msdb                  | ##MS_PolicyTsqlExecutionLogin##   | User             
##MS_PolicyTsqlExecutionLogin##    | msdb                  | PolicyAdministratorRole           | MemberOf
NT SERVICE\ReportServer            | master                | NT SERVICE\ReportServer           | User             
NT SERVICE\ReportServer            | master                | RSExecRole                        | MemberOf
NT SERVICE\ReportServer            | msdb                  | NT SERVICE\ReportServer           | User             
NT SERVICE\ReportServer            | msdb                  | RSExecRole                        | MemberOf
NT SERVICE\ReportServer            | ReportServer          | NT SERVICE\ReportServer           | User             
NT SERVICE\ReportServer            | ReportServer          | RSExecRole                        | MemberOf
NT SERVICE\ReportServer            | ReportServerTempDB    | NT SERVICE\ReportServer           | User             
NT SERVICE\ReportServer            | ReportServerTempDB    | RSExecRole                        | MemberOf
NT-AUTORITÄT\SYSTEM                | AdventureWorksDW2012  | db_owner                          | MemberOf
NT-AUTORITÄT\SYSTEM                | AdventureWorksDW2012  | dbo                               | User             
sa                                 | master                | db_owner                          | MemberOf
sa                                 | master                | dbo                               | User             
sa                                 | model                 | db_owner                          | MemberOf
sa                                 | model                 | dbo                               | User             
sa                                 | msdb                  | db_owner                          | MemberOf
sa                                 | msdb                  | dbo                               | User             
sa                                 | tempdb                | db_owner                          | MemberOf
sa                                 | tempdb                | dbo                               | User             
test                               | StackExchange         | test                              | User             

This might be what your are searching for.

0

You could also use a cursor to loop over the databases and get their users:

DECLARE @dbname NVARCHAR(255), @sql NVARCHAR(max)

DECLARE c CURSOR FORWARD_ONLY READ_ONLY FOR
SELECT name FROM sys.databases 
WHERE database_id > 4;

OPEN c
FETCH NEXT FROM c INTO @dbname ;

WHILE @@fetch_status = 0
BEGIN
    set @sql =
    'use '+@dbname+' 
    select '''+@dbname+''',* 
    from sys.sysusers 
    where name not in 
    (''public''
    ,''dbo''
    ,''guest''
    ,''INFORMATION_SCHEMA''
    ,''sys''
    ,''db_owner''
    ,''db_accessadmin''
    ,''db_securityadmin''
    ,''db_ddladmin''
    ,''db_backupoperator''
    ,''db_datareader''
    ,''db_datawriter''
    ,''db_denydatareader''
    ,''db_denydatawriter'')';

    EXEC (@sql);
    FETCH NEXT FROM c INTO @dbname;
END
CLOSE C
DEALLOCATE c

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.