I came across a situation where I had to find all the stored procedures or user defined functions that do not have “execute” permission under a particular user. Here is a simple SQL Statement one which can be used under SQL Server 2005 to find those objects.
Note: Make sure that you have logged in as a most privileged user through a Query Analyzer window and then replace the ‘user_name’ for which you want to check permission. For example, following code will show all the procedures which do not have Execute permission for 'user_name'.
Declare @ObjName Varchar(100)
Create table #TempTable1 (Entity_Name varchar(100), Permission_Name varchar(25))
Create table #TempTable2 (Entity_Name varchar(100), Permission_Name varchar(25))
INSERT #TempTable1(Entity_Name)
SELECT Name FROM sys.objects
WHERE type='P'
ORDER by Name
EXECUTE AS USER = 'user_name';
Declare TableList cursor LOCAL FAST_FORWARD FOR
SELECT Entity_Name FROM #TempTable1
ORDER by Entity_Name
OPEN TableList
FETCH NEXT FROM TableList INTO @ObjName
WHILE @@fetch_status = 0
BEGIN
INSERT INTO #TempTable2
SELECT Entity_Name,Permission_Name
FROM fn_my_permissions(@ObjName, 'OBJECT')
FETCH NEXT FROM TableList INTO @ObjName
End
CLOSE TableList
DEALLOCATE TableList
SELECT Entity_Name FROM #TempTable1
WHERE Entity_Name NOT IN (SELECT Entity_Name FROM #TempTable2);
Drop table #TempTable1;
Drop table #TempTable2;