Does Your Stored Procedure Grant Itself Permissions?

It’s a very good question. One that might not seem to insidious. Nothing that should be able to bring down the system and cause failures. Or will it?

I’ve been to a number of clients and done it myself before where I start to check out a stored procedure with some performance issues and sitting all pretty at the bottom is a GRANT EXEC statement. When I script out the stored procedure I get something similar to the following:

CREATE PROCEDURE dbo.FooGetTableA
    (
    @Parameter varchar(4)
    )
AS
 
SELECT Column1 
FROM dbo.TableA
WHERE Column2 = @Parameter
 
GRANT EXEC ON dbo.FooGetTableA TO ApplicationRole
GO

But if you look carefully, there is something missing, or one could say included that shouldn’t be.  Look again if you don’t see it. It’s hidden in plain sight.  The permissions for the procedure are included in the body of the stored procedure.  When the procedure was written, someone thought ahead to add permissions to the script but forgot the GO statement between the stored procedure

In a better world this script would have looked like this:

CREATE PROCEDURE dbo.FooGetTableA
    (
    @Parameter varchar(4)
    )
AS
 
SELECT Column1 
FROM dbo.TableA
WHERE Column2 = @Parameter
GO
 
GRANT EXEC ON dbo.FooGetTableA TO ApplicationRole
GO

It’s Just a Permission Statement

Who cares, right?  So you are assigning some permissions every time that procedure executes.  What harm could possibly come of it.  I’ve seen this so many times and usually it’s one of things I’ll point out and say, “oops, you should take care of that”.  When I should be saying, “yeah, fellas.  You’ve got a time bomb there waiting for your business to take off.”

And the time bomb is deadlocks.  Completely preventable deadlocks.

If you have procedures that grant themselves permissions, then as the volume of activity in your database increases you may start to see deadlock graphs similar to the following:

deadlock-list deadlock victim=process30108bac8  process-list   process id=processec55dd68 taskpriority=0 logused=0 waitresource=METADATA: database_id = 10 PERMISSIONS(class = 1, major_id = 219199881) waittime=15000 ownerId=746424569 transactionname=Load Permission Object Cache lasttranstarted=2009-10-22T23:06:59.287 XDES=0x3712a8e98 lockMode=Sch-S schedulerid=1 kpid=5832 status=suspended spid=157 sbid=2 ecid=0 priority=0 transcount=1 lastbatchstarted=2009-10-22T23:06:59.287 lastbatchcompleted=2009-10-22T23:06:59.280 clientapp=.Net SqlClient Data Provider hostname=PRDWB0111 hostpid=5640 loginname=BaconUser isolationlevel=serializable (4) xactid=746424394 currentdb=10 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056    executionStack     frame procname=AdventureWorks2008.dbo.FooGetTableA line=1 sqlhandle=0x03000a0089b9100d0e527800669c00000100000000000000CREATE PROCEDURE dbo.FooGetTableA    (    @Parameter varchar(4)    )AS

SELECT Column1 FROM dbo.TableAWHERE Column2 = @Parameter

GRANT EXEC ON dbo.FooGetTableA TO ApplicationRole    inputbufProc [Database Id = 10 Object Id = 219199881]       process id=process30108bac8 taskpriority=0 logused=0 waitresource=METADATA: database_id = 10 PERMISSIONS(class = 1, major_id = 1746157316) waittime=2125 ownerId=746479249 transactionname=Load Permission Object Cache lasttranstarted=2009-10-22T23:07:12.180 XDES=0x3786c61c8 lockMode=Sch-S schedulerid=3 kpid=4048 status=suspended spid=69 sbid=2 ecid=0 priority=0 transcount=1 lastbatchstarted=2009-10-22T23:07:12.180 lastbatchcompleted=2009-10-22T23:07:12.167 clientapp=.Net SqlClient Data Provider hostname=AMBER hostpid=568 loginname=BaconUser isolationlevel=serializable (4) xactid=746372404 currentdb=10 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056    executionStack     frame procname=AdventureWorks2008.dbo.FooGetTableB line=1 sqlhandle=0x03000a00043f146882564201a09b00000100000000000000CREATE PROCEDURE dbo.FooGetTableB    (    @Parameter varchar(4)    )AS

SELECT Column1 FROM dbo.TableBWHERE Column2 = @Parameter

GRANT EXEC ON dbo.FooGetTableB TO ApplicationRole    inputbufProc [Database Id = 10 Object Id = 1746157316]      resource-list   metadatalock subresource=PERMISSIONS classid=class = 1, major_id = 219199881 dbid=10 id=lock4153ec880 mode=Sch-M    owner-list     owner id=process30108bac8 mode=Sch-M    waiter-list     waiter id=processec55dd68 mode=Sch-S requestType=wait   metadatalock subresource=PERMISSIONS classid=class = 1, major_id = 1746157316 dbid=10 id=lock415451780 mode=Sch-M    owner-list     owner id=processec55dd68 mode=Sch-M    waiter-list     waiter id=process30108bac8 mode=Sch-S requestType=wait

 

Breaking It Down

When I first started looking at these there are a few things I noted right away:

1. The procedures were access completely different tables with no common objects between them.  In the sample above there is TableA and TableB and no relationship.
2. Looking at each of the processes in the deadlock both of them have the following attributes
   1. waitresource=METADATA: database_id = 10 PERMISSIONS
   2. transactionname=Load Permission Object Cache

So nothing in common and a deadlock on a metadata resource for permissions.  This made me start to re-think how the two procedures were related.  With a metadata resource wait, there seems to be an issue above the data in the table.  Since both procedures point to the Load Permission Object Cache, maybe there is an issue there.

If you take a look, each of the procedures has a GRANT EXEC permission statement in it.  This is the area of commonality and where the two executions deadlocked.  Removing the GRANT EXEC permissions statements stop this deadlock from occurring.

After going through and removing these permission statements from a number of procedures that had this issue, all of the deadlocks with these types of issues disappeared.  And it is smooth sailing once again.

Cautionary Tale

Hopefully this is a scenario that only I’ve run into.  But if it’s not then this should serve as a reminder that little details that seem like a little non-issue, could be the crack that breaks the damn when there’s enough water behind it.  The thing that gets you on this issue is that it isn’t until execution start to really grow before it pops out and it will only hit when you’re the busiest.  This is something I’ll be keeping an eye out for in the future and I’d recommend the same for others as well.