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:
- 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.
- Looking at each of the processes in the deadlock both of them have the following attributes
- waitresource=METADATA: database_id = 10 PERMISSIONS
- 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.
Very good article about a topic that I have seen myself happen but I didn't realise it caused deadlocks, so that is one to look out for.
If you are not sure whether or not you have any stored procs or UDF's in your DB where you have accidentally left in the GRANT EXEC statement then you can run the following SQL to list out the names that do contain the string GRANT EXEC.
SELECT DISTINCT NAME AS [NAME],
CASE WHEN TYPE ='P' THEN 'PROCEDURE'
WHEN TYPE IN('FN', 'IF','TF') THEN 'FUNCTION'
END AS OBJECTTYPE
FROM syscomments as comm
JOIN sysobjects as obj
ON comm.id = obj.id and obj.type IN ('P','FN', 'IF', 'TF')
WHERE lower(TEXT) LIKE '%' + 'grant exec on%'
LikeLike
I've done it to myself, but I've always caught it before putting it into production.
LikeLike