This has been on my radar now for a little over a week.  I ran across a  request in the forums to assist with permissions and decided to test some things.  The gist of the request was to disallow a user from inserting into a table and still have execute permissions on a stored procedure that may need to insert data into the same table.

The Setup

To work through this, I decided to create a user, grant that user specific permissions, and create a stored procedure to which I would grant that user I created execute permissions.  All of this is pretty straight forward stuff just to demonstrate the principle.  I will also be using a database previously setup just for testing purposes.

So let’s just start by creating this user that we will be using to test our permissions.

Code block

USE [master]
GO
CREATE LOGIN [TestRW] WITH PASSWORD=N'testrw' , DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [TestA]
GO
CREATE USER [TestRW] FOR LOGIN [TestRW]
GO
USE [TestA]
GO
EXEC SP_ADDROLEMEMBER N'db_denydatawriter', N'TestRW'
GO

We can see that the user creation is pretty straight forward and we are immediately adding the user to the denydatawriter role for the database.  Now for the next couple of items that we will be using in this little experiment, a table and a stored procedure.

Code block

CREATE TABLE testinsert(testid INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, teststring VARCHAR(50))

Code block

CREATE PROCEDURE sp_inserttestinsert
 
AS
 
INSERT INTO testinsert (teststring)
SELECT 'Successfully inserted a string'

And the last little bit of the setup is to grant our TestRW user execute permissions on this stored procedure.

Code block

GRANT EXECUTE ON sp_inserttestinsert TO TestRW

That is it for the initial setup.  I think this is a pretty straight forward setup to demonstrate these permissions.  The next item of business is to actually create these objects and to test.

Testing – 1,2,3

The testing I have setup for this is also pretty straight forward.  I will execute the proc and then select all records from that table that was created.  If successful, then there will be no records.  If (per the requirements), we see records then the setup has failed and we need to explore other options or find a reasonable conclusion that is preventing us from achieving success.

Code block

EXEC sp_inserttestinsert
 
SELECT * FROM testinsert

Upon executing that, you should see the following results at this point.

Well, would you look at that.  Having denydatawriter on the table, and granting execute permissions on the proc has allowed us to insert data into the table.  Let’s take it one step further now – what if we explicitly deny update, delete, and insert permissions on the table to this user?  We can do that with the next script.

Code block

DENY INSERT,UPDATE, DELETE ON testinsert TO
TestRW

With the new permissions added (or subtracted depending on how you look at it), we can test once again – using the same script as in the first round.

Well, now we can see that explicitly denying update, insert and delete on the table is no more effective than adding the user to the denydatawriter role.  Are the deny permissions even working at this point?  Well, this verify that.

Code block

INSERT INTO testinsert (teststring)
SELECT 'Successfully inserted a string'

Now, I had forgotten to mention this but it is very critical to the proper testing of this routine.  The objects created were created by a different user than the TestRW account we created.  Once the objects are created, then we MUST login to the Database Server with the new login and we must run the queries from that user account.  To recap – grant permissions and object creation are done from an account other than TestRW.  Running the test queries is done through the TestRW account.  (For ease of testing, you may also want to grant access to the db_datareader role on the TestRW user.

The results of running that simple Insert statement should yield the following result.

So we know that the permissions are working but that the stored procedure is circumventing those explicit permissions.

Conclusion

Well, this is not actually a failure.  This is by design.  This is called implicit permissions.  If I am going through the trouble of granting execute permissions on a stored procedure to a specific user, then the system interprets that as the stored proc permissions override any explicitly set permissions.  Is there a way around this?  You could setup triggers on a table to prevent this from happening or you could remove the execute permissions for that user on each proc.  I also think this helps to underscore the importance of not just granting execute to all stored procedures in a database to a user.  This simply over-permissions that user and they may be able to do more than you wish for them to do.  Another option is to add code to each stored procedure that would check for group memberships and error out if the user is a member of the denydatawriter role (for instance).  This would be very similar to the way that MS prevents users from executing certain system stored procs from being executed by users who do not have adequate permissions.