Revoke Public / Guest Permissions and tempdb is created from model

2 11 2013

Probably the worst and maybe most important step to STIGing a SQL Server is to remove public and guest permissions, I found a great script over at Fort SQL, http://blogs.technet.com/b/fort_sql/archive/2010/02/04/remove-public-and-guest-permissions.aspx, to perform this action. I would suggest if using this script first ensure that you run it as script only, there is a variable that can be set. This will allow you to roll back any all of the changes that get made when the script is ran in execute mode.

If you use this script it will break your applications that use the SQL Server! It is not a question of if but more of how badly! I recommend that prior to running this script you enable the built-in auditing features of MSSQL Server 2008, as the information collected is invaluable to resolving the permissions issues that will occur.

That being said I ran this script against a database server that hosted the database for SCCM 2007. I knew that SCCM was going to break and was very prepared to deal with that. It wasn’t until several days later that the SQL Server was rebooted and SCCM stopped deploying patches. Looking at the failures in the audit log I quickly realized that guest required the select permissions to sys.sysobjects in the tempdb.

use tempdb
grant select on sys.sysobjects to guest;

executing this resolved the issue and SCCM happily began chugging away with deploying software!

This resolution only worked as long as the SQL Server did not shutdown, as the tempdb is re-created at every startup.  Looking for solutions to this issue many places I looked advised to just create a job to run using the SQL Agent service everytime it started. This seemed like ghetto fix for the problem, what happens when that service fails to start or the script fails to execute? I know the answer is I’m going to have to manually update permissions again! After some more research I found that tempdb is created in the eyes of the model and if I set the permissions in the model database they would translate to the tempdb upon reboot.

use model
grant select on sys.sysobjects to guest;

The problem is now resolved and no longer or will ever require intervention from me.

 

Advertisements