Where can I find a list of reserved logins?

Question

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?

Answer 1

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.