Monitor Logins & Users

The procedure Monitor Logins & Users creates an overview of all logins that are found on the Linked Servers and the associated users in the databases. This list is compared to the list that was retrieved the last time this procedure ran. Any changes found are reported. 

Reported fields are:

  • ID: The ID of the login in SQL Server.
  • LinkedServer: The server the Login was found.
  • UserName: The Loginname on the server level, not the username on database level.
  • sysadmin: 1 or 0, whether the user has sysadmin rights.
  • DatabaseName: The database the user has access to.
  • RoleName: The role(s) the user has in the database.
  • DateStart: The first time this login was found for this database.
  • DateStop: The first time the login was no longer found for this database.

If a login has no associated database, the fields DatabaseName and RoleName will be empty.
If a user is found on a database without an associated login on the server level, the user has no real access and is not reported.
If a user has access to multiple databases the user will be in the list for every database found.

How to use it

The procedure usp_Report_Logins will crawl all Linked Servers and retrieve information on all logins and users it finds. Information for these logins is stored in the table tbl_Logins.

Create a scheduled job using the Settings window. This will create a job with all necessary information. If you prefer to do this manually, execute the stored procedure with the following parameters:

DECLARE @RC int
DECLARE @Recipient varchar(250)
DECLARE @ExceptionList varchar(250)
DECLARE @MailStats bit
DECLARE @SqlVersion int
DECLARE @Separator nchar(1)

SET @Recipient = ‘yourname@yourdomain.com’ 
SET @ExceptionList = ‘ServerNotToProcess’
SET @MailStats = 1
SET @SqlVersion = 0
SET @Separator = ‘,’

EXECUTE @RC = [dbo].[usp_Report_Logins] 
   @Recipient
  ,@ExceptionList
  ,@MailStats
  ,@SqlVersion
  ,@Separator
;

  • The recipient is the emailaddress where to send the results to.
  • The ExceptionList is a comma separated list of servers that should not be monitored. 
  • Mailstats = 1 means that you will recieve an email if there is no useful information te report, telling you exactly this. If you set this to 0 then no email is sent unless there is something to report.
  • SqlVersion = 0 means report all (possible) Sql Server Verions. If you set this to 11, then only Sql Server 2012 will be monitored and reported.
  • Seperator is the separator character for the comma separated list. You can choose any single character for this.

We recommend that you schedule this procedure on a daily basis after the Monitor Servers procedure has run. Any servers not found by the Monitor Servers will be skipped, saving time.

Collected Information

  • LinkedServer
  • uid: The UserID on the server
  • sid: The Security ID of the user, this can be SQL based or Windows AD Based.
  • UserName
  • DefaultDB: The default database for this user.
  • denylogin: If Denylogin = 1 then the user has no access.
  • hasaccess: If the user has Server access.
  • isntname: 1 means Windows login, 0 means SQL login.
  • sysadmin: If the user has sysadmin rights.
  • securityadmin: Whether the login is a member of this serverrole.
  • serveradmin: Whether the login is a member of this serverrole.
  • setupadmin: Whether the login is a member of this serverrole.
  • processadmin: Whether the login is a member of this serverrole.
  • diskadmin: Whether the login is a member of this serverrole.
  • dbcreator: Whether the login is a member of this serverrole.
  • bulkadmin: Whether the login is a member of this serverrole.
  • principal_id
  • type_desc: Windows Group, Windows User of SQL User
  • is_disabled: If the account is disabled
  • hasdbaccess: Whether the acount has any specific database access.
  • DataBaseName: The database the account is specifically granted rights for.
  • default_schema_name. The default schema for this user.
  • RoleName: The roles this user is a member of for the specified database.
  • DateStart: The first time this user was found for this database.
  • DateStop: The first time this user was not found anymore for this database.

This information contains current as well as historical information and can therefor be used for auditing purposes.