SQL Server: explicit rights to schema?

Question

When giving permissions to certain Schema in SQL Server, I want to deny some operations from certain users. Do I have to explicitly deny these, or is it so that when user gets a permission to this schema, rights for this schema is automagically denied for other users?

Answer 1

By default, rights to the objects in the schema are not granted to a user. However, there are a few ways in which a user may get the rights to the object. For example, rights can be granted explicitly to the user and/or to a group in which the user is a member.

The DENY command denies rights to the user or group even if the user has been granted rights to the object. For example, assume user A is a member of the MyGroup group and the group has been granted the right to access the MyTable table. All users in MyGroup can query MyTable. Now, you can deny access to MyTable for user A. User A will not be able to query MyTable, but the rest of the group members can still query the table.

So, it's probably best to explicitly deny the rights to the users that should not have access to the schema.

Answer 2

Think of Schema in SQL as a tree. The further back to the root of the schema that a user has an allowed right, the further up the trunk and branches the user will maintain that allow right until you deny it. If user A has permissions to execute stored procs on a database, then they have rights to access all stored procs on that DB, and if you don't want them to have rights to run a single on you'd have to mark it as deny. Same goes with read, write, update, etc etc rights. If the user has those rights at a higher level then yes you'd need to implicitly deny. I can't think of anything (including login) that a user has rights to do unless you grant them that access in the first place. Keeping in mind of course, some of these may be granted upon user creation.

The last thing I'll say, is that it never hurts to be sure. I will often specify allow or deny on items that are more security sensitive even though I know that it should already be that way.