Mar 9, 2017 7:30:13 PM by Pinal Dave
One should be very careful while working with system objects in SQL Server. Below is one of the situation where some permissions were given on system objects which was causing SQL startup failure. I have written various blog on script upgrade mode of SQL Server.
Whenever a patch is applied to SQL Server, one of the steps after first start is to run upgrade scripts. Those scripts make changes to system objects. If there is any fatal error in running those scripts, SQL Server would not be able to start.
Below is the ERRORLOG snip from one of the failures.
2017-01-30 17:18:22.44 spid3s Error: 15173, Severity: 16, State: 1.
2017-01-30 17:18:22.44 spid3s Server principal ‘##MS_PolicyEventProcessingLogin##’ has granted one or more permission(s).
Revoke the permission(s) before dropping the server principal.
2017-01-30 17:18:22.44 spid3s Error: 912, Severity: 21, State: 2.
2017-01-30 17:18:22.44 spid3s Script level upgrade for database ‘master’ failed because upgrade step
‘msdb110_upgrade.sql’ encountered error 15173, state 1, severity 16.
This is a serious error condition which might interfere with regular operation and the database will be taken offline.
If the error happened during upgrade of the ‘master’ database, it will prevent the entire SQL Server instance from starting.
Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so
that the script upgrade steps run to completion.
2017-01-30 17:18:22.44 spid3s Error: 3417, Severity: 21, State: 3.
2017-01-30 17:18:22.44 spid3s Cannot recover the master database. SQL Server is unable to run.
Restore master from a full backup, repair it, or rebuild it.
For more information about how to rebuild the master database, see SQL Server Books Online.
The last message is very scary! We need to look back and look at meaningful error which is 15173 in this case. We should note that ##MS_PolicyEventProcessingLogin## is a certificate-based SQL Server login for internal system use only. It’s created from certificates when SQL Server is installed.
In script failure, we always need to start SQL with trace flag 902, which would cause SQL to skip running those scripts and we get a chance to fix the issue. Here are the steps we followed to start SQL with trace flag.
Then we started the SQL Server service. Since we have issue with ##MS_PolicyEventProcessingLogin##
SELECT * FROM sys.server_permissions
WHERE grantor_principal_id = (SELECT principal_id
WHERE NAME = N'##MS_PolicyEventProcessingLogin##')
Once we ran above query, we found that there was a login having permissions on this login and due to that SQL setup was unable to drop it.
Once we cleared dependency (using the REVOKE command), we were able to remove trace flag and start SQL without any error.
Reference: Pinal Dave (http://blog.SQLAuthority.com)
Tags: SQL Server
Written by Pinal Dave
Pinal Dave is a Microsoft Technology Evangelist (Database and BI). He has written over 1700 articles on the subject on his blog at http://blog.sqlauthority.com. He is a dynamic and proficient Principal Database Architect who specializes in SQL Server Performance Tuning and has 7+ years of hands-on experience. He holds a Masters of Science degree and a number of certifications, including MCTS, MCDBA and MCAD (.NET). He is also Regional Mentor for PASS Asia. Prior to joining Microsoft he was awarded Microsoft MVP award for three continuous years for his contribution in community.