I know that security people like to remove permissions from everything before certifying that a server is ready to go into production. And like 10+ years ago that was something that you might have wanted to do (I’m just talking about SQL Server here). However in today’s world of SQL Server 2005 and newer that isn’t needed. These newer versions are designed to take security much more seriously than before. The rights that are granted to public in the master and msdb databases should be left the hell alone.
If you are going to go around revoking database permissions that you don’t understand what do, don’t come to be complaining that your SQL Server isn’t working correctly. Guess what, those permissions where there for a reason, and should be left alone. If you have some out dated security mandate that says that all database permissions must be revoked from public before the server can be used, then you had damn well better understand what that means. And you should probably update your stupid security policy so that it reflects the changes that have been made in the product over the last 10 or so years. Even under SQL Server 2000 I didn’t ever recommend that people remove all the rights from public, ever. Not if you wanted the SQL Server to work as expected.
If you have decided to go and remove all the permissions, then you will probably want to install a new SQL Server, and find all the permissions there and grant them back. That or restore your master database back to a state from before you screwed it up, which is the same thing that I recommended to the person in the forum thread above do. If you intentionally break your SQL Server don’t expect much sympathy from me.
Denny
One Response
I think there’s a standard policy that ship’s with SQL recommending that action. I know I followed it once and it broke stuff wonderfully.
Of course I have been known to forget stuff occasionally, so it is possible I have my wires crossed!