Access

  • I have store proc where I grant execute to a role. However, it fails with insert permission was denied on the object so the role [db_datawriter] guarantees that insert, update, and delete permissions are provided on all objects within the database. This also includes any future objects to be created which is granting update and delete. Instead is there an alternate for insert only permissions for all objects within the database and also includes any future objects to be created?

  • Personally, I don't like that security model as I like working with a "least privilege" model.  I generally apply permissions as part of my object creation scripts.

    Now, if you DO want to go with the route you are asking about, I think you can "GRANT INSERT ON SCHEMA::<schema name>" to a ROLE which would do what you are asking as long as you aren't making new schemas on a regular basis.

    Experts - do correct me if I am mistaken on the above.

  • I am a bit confused on the permissions required to execute stored procedure. For example, if you have 2 tables and a stored procedure which reads data from Table1 and inserts data into Table2, will execute permission on the stored procedure and select permission on the tables be enough? In theory I am finding that this is given as a solution but practically I am finding that a user would need execute permission on the stored procedure, select permission on the tables and insert permission on Table2.

  • This is one of those "it depends" situations.  According to the Microsoft documentation (found here https://docs.microsoft.com/en-us/previous-versions/dotnet/framework/data/adonet/sql/managing-permissions-with-stored-procedures-in-sql-server), it states:

    Stored procedures take advantage of ownership chaining to provide access to data so that users do not need to have explicit permission to access database objects. An ownership chain exists when objects that access each other sequentially are owned by the same user. For example, a stored procedure can call other stored procedures, or a stored procedure can access multiple tables. If all objects in the chain of execution have the same owner, then SQL Server only checks the EXECUTE permission for the caller, not the caller's permissions on other objects. Therefore you need to grant only EXECUTE permissions on stored procedures; you can revoke or deny all permissions on the underlying tables.

     

    Things that are key in that are that the OWNER of the objects needs to be the same.  If the owner of the stored procedure is not the same owner as the table, then you will need to give explicit permissions on the table.

    Also, if I remember right, if explicit permissions are granted on the object (ie table), they will override the ownership chaining.  What I mean is if the table has DENY SELECT to the user and the stored procedure has GRANT EXECUTE to the user, the user will hit an error when they need to SELECT from the table.  There are other exceptions such as with dynamic SQL in a stored procedure, or I hit one recently where a stored procedure called another stored procedure and the second stored procedure tried to truncate a table.  I think you may also hit snags with cross-database queries.

    I think there may also be exceptions if you have an EXECUTE AS in your stored procedure.

     

    A good writeup on ownership chaining (with examples you can run on your own non-production SQL Instance) can be found here:

    https://www.mssqltips.com/sqlservertip/6545/implementing-sql-server-security-with-stored-procedures-and-views/

  • When you create the proc, you would need to specify EXEC AS OWNER of some other high-level user.

    When you just give permission to EXEC the proc, the default is to EXEC as whoever is running the proc (EXEC AS CALLER).  That's why it doesn't work: those users don't have authority to do the INSERTs, etc..

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply