When working with SQL Server, it is important to exercise caution when dealing with system objects. These objects play a crucial role in the functioning of SQL Server, and any mishandling can lead to serious issues.
One common situation where problems can arise is during the execution of upgrade scripts. These scripts are run after applying patches to SQL Server and are responsible for making changes to system objects. However, if there is a fatal error during the execution of these scripts, SQL Server may fail to start.
Let’s take a look at an example of such a failure:
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.
As we can see, the error log mentions error code 15173, which indicates that the server principal ‘##MS_PolicyEventProcessingLogin##’ has granted permissions that need to be revoked before dropping the server principal. This error can have serious consequences and may prevent the entire SQL Server instance from starting.
So, how can we resolve this issue? One workaround is to start SQL Server with a trace flag, specifically trace flag 902. This flag instructs SQL Server to skip running the upgrade scripts, giving us an opportunity to fix the problem.
Here are the steps to start SQL Server with trace flag 902:
- Open SQL Server Configuration Manager.
- Select the SQL server instance in SQL Server Services.
- Right-click the instance and click Properties.
- Click the Startup Parameter tab.
- Add “-T902”.
- Start the SQL Server service.
Once SQL Server is running with the trace flag, we can investigate the issue further. In this case, we can execute the following query to identify any dependencies:
SELECT * FROM sys.server_permissions
WHERE grantor_principal_id = (SELECT principal_id
FROM sys.server_principals
WHERE NAME = N'##MS_PolicyEventProcessingLogin##')
Running the above query reveals that there is a login with permissions on the ‘##MS_PolicyEventProcessingLogin##’ principal. This dependency prevents SQL Server from dropping the principal and causes the startup failure.
To resolve this, we need to revoke the permissions on the login using the REVOKE command. Once the dependency is cleared, we can remove the trace flag and start SQL Server without any errors.
Working with system objects in SQL Server requires careful attention to detail. By following best practices and being aware of potential issues, we can ensure the smooth operation of our SQL Server instances.