For the love of god people, quit screwing around with the base permissions within SQL Server.

Published On: 2011-02-17By:

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


Contact the Author | Contact DCAC

One response to “For the love of god people, quit screwing around with the base permissions within SQL Server.”

  1. TimothyElley says:

    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!

Video

Globally Recognized Expertise

As Microsoft MVP’s and Partners as well as VMware experts, we are summoned by companies all over the world to fine-tune and problem-solve the most difficult architecture, infrastructure and network challenges.

And sometimes we’re asked to share what we did, at events like Microsoft’s PASS Summit 2015.

Awards & Certifications

Microsoft Partner       Insights Sccess Award    Technology Headlines Award    Golden Bridge Gold Award    CIO Review Top 20 Azure Solutions Providers    VMWare Partner
Microsoft Certified Master    Microsoft MVP
Share via