SQL Server: Remove public role privileges from login account 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.



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