0

I've got a SQL Server (2012) and I need to create a read-only user. At first, I was just going to create a user, and then give them the db_datareader role. But while looking around, I saw the public role, and how it had Select, Update, Insert and Delete privileges on all the objects in the db.

BadRoles

Is there a way for me to say "Yes, I know you are a member of the public role, but I'm revoking privileges X,Y,Z"?

New contributor
Black Dynamite is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
2

Is there a way for me to say "Yes, I know you are a member of the public role, but I'm revoking privileges X,Y,Z"?

You can use DENY :

"Denies a permission to a principal. Prevents that principal from inheriting the permission through its group or role memberships. DENY takes precedence over all permissions, except that DENY does not apply to object owners or members of the sysadmin fixed server role. Security Note Members of the sysadmin fixed server role and object owners cannot be denied permissions."

So to DENY the DELETE permission to a user Fred database-wide:

DENY DELETE to Fred;

Or for just one schema

DENY DELETE ON SCHEMA::dbo to Fred;

But I would advise you to investigate why Public has all those permissions. That's not a common or recommended security model.

  • Thanks David Browne. I've added the user to the db_denydatawriter role so I assume that will work. Some of the folks beforehand wanted to set things up quickly without the application fussing, so blowing away security is not out of character based on what else I've seen here. – Black Dynamite Feb 11 at 21:47
  • You should move away form the old db_* roles, and just GRANT or DENY the permissions directly on the database. – David Browne - Microsoft Feb 11 at 21:48

Your Answer

Black Dynamite is a new contributor. Be nice, and check out our Code of Conduct.

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.