I have a table, which I'd like to be read-only, except for a few specific stored procedures which need permissions to UPDATE rows in the table.

After that, I want only a select group of additional stored procedures to have permissions to EXECUTE the aforementioned UPDATE procedures.

I've been reading about GRANT and DENY on MSDN/TechNet, and I've also been reading about schemas, but the documentation is so vague and non-relatable that I'm having trouble figuring out how to accomplish real-world security goals based on the descriptions provided.

I can see that you can GRANT or DENY permissions for individual principles, one at a time - but I need to deny INSERT UPDATE and DELETE permissions for ALL principles, EXCEPT for the few stored procedures to which I am granting UPDATE permissions (as well as sa and/or db_owner I suppose).
I don't want any new users to have UPDATE permissions on this table by default, and I would really like to avoid having to manually deny permissions every time we create/modify users or other objects.

  • 3
    Best is to use ROLE based permissions. This way, you just grant permissions to the ROLE only. – Kin Shah Jul 12 '15 at 18:54

By default, no direct access is permitted to either tables or stored procedures. One must explicitly grant access in order for non-privileged users to have access. The best practice to simplify security administration is to grant permissions to roles, and control access via role membership.

If you want to restrict access only via stored procedures, take a look at ownership chaining (https://technet.microsoft.com/en-us/library/ms188676). This allows you to grant execute permissions to individual roles/users without allowing direct access to underlying tables as long as the object involves have the same owner (default is dbo).

Using ownership chaining in your scenario, you could grant only SELECT permission on the tables, with updates allowed only by users with execute permission on the update procs. No direct table modifications would be allowed.


Below is an ownership chaining example. Note that neither user can update the table directly and the write user can update the table only using the stored procedure. The implication of ownership chaining is that when you grant execute permissions on a stored procedure, users can access all of the encapsulated objects without direct object permissions as long as the objects have the same owner.

--create objects
      MyTableID int IDENTITY NOT NULL 
    , SomeData int

CREATE PROC dbo.UpdateMyTable
      @MyTableID int
    , @SomeData int
UPDATE dbo.MyTable
SET SomeData = @SomeData
WHERE MyTableID = @MyTableID;

--create roles
CREATE ROLE TableReaderRole;
CREATE ROLE TableWriterRole;

--grant permissions to roles
GRANT SELECT ON dbo.MyTable TO TableReaderRole;
GRANT EXECUTE ON dbo.UpdateMyTable TO TableWriterRole;

--create user with only read permissions
CREATE USER SomeReadOnlyUser FOR LOGIN SomeReadOnlyUser;
ALTER ROLE TableReaderRole
    ADD MEMBER SomeReadOnlyUser;

--create user with both read and writer permissions
CREATE USER SomeAllowedWriteUser FOR LOGIN SomeAllowedWriteUser;
ALTER ROLE TableReaderRole
    ADD MEMBER SomeAllowedWriteUser;
ALTER ROLE TableWriterRole
    ADD MEMBER SomeAllowedWriteUser;

Be aware that it is the owner rather than the schema name that is relevant for ownership chaining. If the table and proc were in different schemas, the ownership chain would still be unbroken as long as the schemas had the same owner (AUTHORIZATION). The object owner is typically inherited from the schema owner.

  • You said, "By default, no direct access is permitted to either tables or stored procedures." Do you mean, no direct access is permitted by default, when the objects are in different schemas? Is that my first step? Getting everything out of dbo? – Giffyguy Jul 13 '15 at 21:55
  • @Giffyguy, a non-privileged user has no permissions on objects regardless of schema unless permissions are granted explicitly, either directly to the user or to roles the user belongs. Different schemas may be used to logically categorize database objects, simply security administration, and provide a security boundary if the schemas have different owners. User-defined schemas are not required to meet your objective of allowing updates only via stored procedures, though. I'll add examples to my answer. – Dan Guzman Jul 14 '15 at 12:15

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.