Logins

Overview

The Logins report answers two questions that are awkward to answer in Management Studio without writing a query: how many people can get into this instance, and how many of them are sysadmin.

It is a five figure summary over a grid. The figures split the instance’s logins four ways; clicking one filters the grid to it.

The Logins report
Five figures over the login list. Clicking a figure filters the grid below it.

The report deliberately excludes the internal certificate-backed principals whose names begin ##, because they are not logins anybody granted.

Where to find it

Route How
Server tree Right-click the server → Instance Level Reports → Logins
Server Overview page The Logins panel – click a bar, or the Open Logins for Instance link
Command palette Ctrl+K, then type Logins

Clicking a bar on the Server Overview panel opens the report already filtered to that bar.

The page title reads Logins for <server name>.

Registered copies only. The Logins panel and this report are not available in an unregistered copy; the panel says so in place of the chart.

Requirements

  • A connection to the instance. The report reads master.
  • Permission to read sys.server_principals and sys.syslogins. A login with no rights over server principals sees only itself, which is a permission answer rather than an empty instance.
  • Nothing is installed on the monitored instance and nothing is stored.

The five figures

Figure What it counts
Total Logins Every SQL login, Windows login and Windows group on the instance, enabled or not.
SysAdmin Logins Enabled logins in the sysadmin server role.
Windows Logins Enabled Windows logins and Windows groups.
SQL Logins Enabled SQL Server logins.
Disabled Logins Logins that are disabled, of any type.

The four detail figures deliberately do not sum to the total. A sysadmin is also either a Windows or a SQL login, so it is counted twice; a disabled login is counted in the total and in Disabled but in neither of the enabled buckets. Each figure answers its own question.

Click a figure to filter the grid to it. Click Total Logins to go back to everything.

SysAdmin Logins is the number to read first. On most instances it should be a very short list, and the names on it should all be explicable.

Reading the grid

The login list
One row per login, sorted by name.
Column What it is
Row Row number in the current filter.
Login Name The server principal name.
Type SQL_LOGIN, WINDOWS_LOGIN or WINDOWS_GROUP.
Disabled Whether the login is disabled.
Date Created When the login was created.
Date Updated When it was last modified – a password change, a rename, an enable or disable.

The grid is sorted by login name.

A Windows group is one row here and any number of people in practice. The count of logins is not a count of humans, and a group row is where an unexpected sysadmin usually comes from.

Date Updated is worth scanning. A cluster of logins modified on the same recent day is usually somebody working through the server, and is worth knowing about if it was not you.

Right-click actions

Item What it does
Script Create User for All Users Puts a full CREATE LOGIN script for every login on the instance on the clipboard, including the password hashes, SIDs, default databases and the password policy settings, plus the sysadmin role memberships.

The script is the one you want before a migration: recreating logins by hand loses the SIDs, which orphans every database user that pointed at them, and loses the hashes, which means every password has to be reset.

The script is for all logins, not the selected one, despite needing a selected row to offer the menu item.

How to read the report

  1. Read SysAdmin Logins. Every name behind that figure can do anything on this instance.
  2. Read Windows Logins against SQL Logins. A high SQL login count on an instance that is supposed to be Windows authenticated only is worth asking about.
  3. Click Disabled Logins. Disabled logins that nobody has removed are usually leavers. They are harmless while disabled and a problem the day somebody re-enables one.
  4. Scan Date Created for logins nobody remembers creating.
  5. Scan Date Updated for recent changes.

Common patterns

More sysadmins than people. Applications given sysadmin because a permission error was easier to silence than to diagnose. Each one is a full compromise of the instance if its password leaks.

A long tail of SQL logins created years apart. Usually one login per application, added as applications were, and never removed as they were retired. Check them against Orphan Users and Database Use.

Windows groups you cannot resolve. A group from a domain that no longer exists, or that was renamed. It still occupies a row and still grants whatever it was granted.

Where the data comes from

sys.server_principals joined to sys.syslogins, in master. Principals whose names begin with ## are excluded. Nothing is stored.

Report Why you would go there
Security Posture The wider question – how hard is this instance to get into.
Orphan Users Database users whose login no longer exists.
Connections Which of these logins are actually connected right now.
Sessions What those connections are doing.
Configuration Values Whether SQL authentication is even enabled.

Frequently asked questions

Why do the four figures not add up to the total? Because they overlap on purpose. A sysadmin is also a Windows or SQL login; a disabled login is in the total and in Disabled and in neither enabled bucket.

Where are the ##MS_... principals? Excluded. They are internal certificate-backed principals, not logins anybody granted.

Does this show database users? No. These are server level logins. For database users with no login behind them, use Orphan Users.

Is the script safe to run? It is a script for you to review, not something the product runs. It creates logins with their original SIDs and password hashes, which is what makes it useful for a migration, and it is worth reading before executing it on any instance.

Why is the report not available? The Logins panel and report are for registered copies.