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"?

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

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

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