patternsqlMinor
Where can I find a list of reserved logins?
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?
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:
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:
Here are the results in SQL Server 2016:
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:
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.
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.