I need to lock down access to a specific database for a user - they should only have access to execute a couple of stored procedures.

However, by default the user gets everything in the "public" role. I took a look at the various permissions and it appears that public still gives them WAY too much access. However, I'm afraid if I revoke these permissions something unexpected will break.

Are there any default or open source scripts that lock public down safely? And if not, is there at least a list of "bare minimum" permissions I'd need to leave as-is? Additionally, is there any easier way to create the scripts than me manually walking through every securable, looking at the access, then writing a line of script to remove it?

  • What exactly does public have access to that you're afraid of? Also note that DENY overrides these. So you can DENY this user those "sensitive" permissions (still curious what these are) and if this causes problems you can just back it out. Oct 8 '14 at 17:07
  • 1
    It's not so much that I can think of bad ways they can use the information as that I don't want them to have ANYTHING they don't absolutely need (so I don't need to worry about it). Oct 8 '14 at 17:22
  • But if you are going to deny or revoke anything, you kind of have to know what you need to deny or revoke... Oct 8 '14 at 17:25
  • Public permissions are quite limited by default. Although public may have permissions to select from system views, meta data visibility is limited to those objects the user has been granted permissions on. The risk with revoking or denying permissions is breaking client APIs that may need meta data.
    – Dan Guzman
    Oct 8 '14 at 17:27
  • Yeah, that's the point of this post. In my case, I know exactly which stored procedures they should have access to. In my mind, that's ALL they should have access to. But I've got this public role that gives them access to a bunch of other system things that (in my mind) I wouldn't give them. However, if it's not safe to deny or remove these permissions, I won't. I really don't know, which is why I'm asking the question. Oct 8 '14 at 17:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.