HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Where can I find a list of reserved logins?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
canwhereloginsfindlistreserved

Problem

I have a web page where the user can configure a SQL Server login and password. I am trying to implement validation for this page.

I looked up the requirements for a SQL Server login, and found this Microsoft article. According to that article, SQL Server logins cannot be a reserved login name. "sa" and "public" are provided as examples. A full list of reserved login names is not provided, and I was not able to find one by searching.

Is there a list of reserved SQL Server login names? If so, where can I find it?

Solution

If I try the following:

create login [public] WITH PASSWORD = '';


I get this error message:

Msg 15025, Level 16, State 1, Line 12

The server principal 'public' already exists.

That makes me think that reserved logins are the server principals that exist on a fresh install of SQL Server. I can get a list of those with valid login names with this query:

SELECT [name]
FROM sys.server_principals
WHERE [name] NOT LIKE '%\%';


Here are the results in SQL Server 2016:

╔═════════════════════════════════════════╗
║                  name                   ║
╠═════════════════════════════════════════╣
║ sa                                      ║
║ public                                  ║
║ sysadmin                                ║
║ securityadmin                           ║
║ serveradmin                             ║
║ setupadmin                              ║
║ processadmin                            ║
║ diskadmin                               ║
║ dbcreator                               ║
║ bulkadmin                               ║
║ ##MS_SQLResourceSigningCertificate##    ║
║ ##MS_SQLReplicationSigningCertificate## ║
║ ##MS_SQLAuthenticatorCertificate##      ║
║ ##MS_PolicySigningCertificate##         ║
║ ##MS_SmoExtendedSigningCertificate##    ║
║ ##MS_PolicyTsqlExecutionLogin##         ║
║ ##MS_PolicyEventProcessingLogin##       ║
║ ##MS_AgentSigningCertificate##          ║
╚═════════════════════════════════════════╝


Both of the examples mentioned in the article are in the result set. Trying to create logins with any of those names seems to fail. For example, both of the following fail:

create login [diskadmin] WITH PASSWORD = '';
create login [##MS_SmoExtendedSigningCertificate##] WITH PASSWORD = '';


I can't say for sure that the above is the complete list but hopefully it helps.

Just for fun I tried creating all possible alphanumeric logins to see which ones would throw an error. With a single CPU I was only able to create and drop 55 logins per second. At that rate it would take about a year to test all possible six character combinations, so this doesn't seem to be a viable approach.

Code Snippets

create login [public] WITH PASSWORD = '';
SELECT [name]
FROM sys.server_principals
WHERE [name] NOT LIKE '%\%';
╔═════════════════════════════════════════╗
║                  name                   ║
╠═════════════════════════════════════════╣
║ sa                                      ║
║ public                                  ║
║ sysadmin                                ║
║ securityadmin                           ║
║ serveradmin                             ║
║ setupadmin                              ║
║ processadmin                            ║
║ diskadmin                               ║
║ dbcreator                               ║
║ bulkadmin                               ║
║ ##MS_SQLResourceSigningCertificate##    ║
║ ##MS_SQLReplicationSigningCertificate## ║
║ ##MS_SQLAuthenticatorCertificate##      ║
║ ##MS_PolicySigningCertificate##         ║
║ ##MS_SmoExtendedSigningCertificate##    ║
║ ##MS_PolicyTsqlExecutionLogin##         ║
║ ##MS_PolicyEventProcessingLogin##       ║
║ ##MS_AgentSigningCertificate##          ║
╚═════════════════════════════════════════╝
create login [diskadmin] WITH PASSWORD = '';
create login [##MS_SmoExtendedSigningCertificate##] WITH PASSWORD = '';

Context

StackExchange Database Administrators Q#173141, answer score: 5

Revisions (0)

No revisions yet.