Sysadmin permission removal causes login error

Recently, I set up a SQL 2014 development system for a new vendor database. Initially, I gave the developers sysadmin permission to perform the install. When the install was complete, I reviewed the setup, and saw that a new SQL login had been added for the application that had been granted sysadmin permission.

I removed the sysadmin permission from both the Windows Group that the developers were using to log in to the server and to the SQL login that had been added for the application.

And, I got calls that neither the developers nor the application could log in to the server. For the Windows Group, the following error was logged in the SQL Event Log:

Error: 18456, Severity: 14, State: 11

Login failed for user ‘Domain\DevGroup’. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors.

For the SQL login, this error was logged:

Error: 18456, Severity: 14, State: 12

Login failed for user ‘ApplicationUser’. Reason: Login-based server access validation failed with an infrastructure error. Check for previous errors.

I googled the errors, but the information that I turned up indicated that deleting the login and re-creating it would solve the problem. It did not.

Granting the following permissions explicitly did fix the issue:

GRANT CONNECT SQL TO [ApplicationUser];
GRANT CONNECT ON ENDPOINT::"TSQL Default TCP" TO "ApplicationUser";

As of SQL 2014, Microsoft no longer grants CONNECT ON ENDPOINT permission to the public role as it did in previous versions. This improves the default security of an instance, but as soon as I removed sysadmin permission, the users couldn’t connect to the instance at all.

 

Sysadmin permission removal causes login error

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s