Find Permission on all objects

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;