July 22, 2021 at 4:48 pm
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?
July 22, 2021 at 5:01 pm
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.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
July 22, 2021 at 5:10 pm
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.
July 22, 2021 at 5:33 pm
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:
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
July 22, 2021 at 7:20 pm
When you create the proc, you would need to specify EXEC AS OWNER, making sure the OWNER is a 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) A socialist is someone who will give you the shirt off *someone else's* back.
July 27, 2021 at 10:27 pm
What's your thoughts on cross database ownership chaining? I have tested and it works. Is there any security risk?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply