When are execute permissions dropped

Question

Ran into something interesting today.  We ran some routine scripts to re-write some stored procedures.  The script writer instead of using Alter Procedure did an If Exist Drop then create on each stored procedure.  However no grant execute statments for the re-created stored procedures were run.  But when I opened the securables properties  window for the user that would need the permissions, they were already granted.  So apparently the execute entries in the permissions table exists for a time after the stored procedure is dropped.  

So. If we had not recreated the stored procedures, how long would these entries have persisted in the permissions table before being dropped, (assuming they are dropped, which I can't see them not being)? 

---

Edward R. Joell MCSD MCDBA

Answer 1

Objects in the database are internally described by an object ID, such as 1234567. The name (MyProc) is an attribute of object 1234567. If you change a proc table or view using the ALTER statement, the object ID stays the same and all the permissions stay. If you drop object MyPro (1234567) and recreate the object MyProc it will have the same name but have a new ID number (8901234). All permissions will be wiped out, and you must grant them again. There isn't a mechanism to let the permissions stay awhile, as you describe.

So what happened? Possibilities:

• Your scripts might have done an ALTER instead of DROP/CREATE.
• You scripts might have copied the permissions and reapplied them.
• The user in question might be getting permissions on the object some other way, such as membership in a database role.

I would start by looking at SELECT * FROM SYS.DATABASE_PERMISSIONS and see what permissions were explicitly granted on that proc.

---

Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

Answer 2

I just performed a test and when the procedure is dropped and recreated that had previously been granted EXECUTE explicitly to that proc and when recreated it cannot execute it such as "GRANT EXECUTE ON usp_Test to TestUser".  This would not allow TestUser to execute the proc if it was dropped and recreated.  What may be going on is your user may have been granted "GRANT EXECUTE TO TestUser" which does allow execution of ANY stored procedure and does work if the proc is dropped and recreated.  Possibly a higher level of security may be allowing this as well but if permissions are granted explicitly to the object and the object is dropped and recreated without granting the permissions back, the user cannot execute the procedure

---

Convert DTS to SSIS | Document SSIS | 30+ SSIS Tasks | Real-time SSIS Monitoring | Quick Starts | BI Blitz

Answer 3

Here is a script you can use to check permissions for that user

SELECT 	DB_NAME(), 
	dp.NAME AS principal_name ,
	dp.type_desc AS principal_type_desc ,
	o.NAME AS object_name ,
	p.permission_name ,
	p.state_desc AS permission_state_desc
FROM sys.database_permissions p
	LEFT OUTER JOIN sys.all_objects o
		ON p.major_id = o.OBJECT_ID
	INNER JOIN sys.database_principals dp
		ON p.grantee_principal_id = dp.principal_id
WHERE dp.NAME = 'TestUser'

---

Convert DTS to SSIS | Document SSIS | 30+ SSIS Tasks | Real-time SSIS Monitoring | Quick Starts | BI Blitz

Answer 4

So what everyone is saying is that I was correct in my original reaction of "Where are the Grant Execute statements?"  and that these procedures do need to have execute rights assigned.   Based on this I made a re-examination of the script and found waaaaaaaaay at the bottom

-- Grant execute on all stored procedures in the current database
DECLARE @name nvarchar(128)
DECLARE procCursor CURSOR FOR SELECT Name FROM sysobjects WHERE OBJECTPROPERTY(id, N'IsProcedure') = 1
OPEN procCursor
FETCH NEXT FROM procCursor INTO @name
WHILE  @@FETCH_STATUS = 0
BEGIN  
    EXEC ('grant execute on [' + @name + ']  to cmapl')
FETCH NEXT FROM procCursor INTO @name
END
CLOSE procCursor
DEALLOCATE procCursor
GO

So mystery solved.

Edward R. Joell MCSD MCDBA

Answer 5

If you would look closer you can see where the user has been granted execute as the permission_name is 'EXECUTE' and the object_name is NULL.  I just created this user today when reading your question so I know eaxctly what I granted and I can see object level as well as 'GRANT EXECUTE' form this view.  Sounds like the user either has 'GRANT EXECUTE to User' or has a higher level of database level role or server level role

---

Convert DTS to SSIS | Document SSIS | 30+ SSIS Tasks | Real-time SSIS Monitoring | Quick Starts | BI Blitz

Answer 6

Which explains why it did not show up when I queried the sys.database_permissions view.  It was not visible in the database level permissions window either.

---

Edward R. Joell MCSD MCDBA