Welcome to Vishwa's blog - Technology, Spirituality and More...

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        


        INSERT INTO #TempTable2  

  SELECT Entity_Name,Permission_Name 

  FROM fn_my_permissions(@ObjName, 'OBJECT')

        FETCH NEXT FROM TableList INTO @ObjName   



CLOSE TableList



SELECT Entity_Name FROM #TempTable1

WHERE Entity_Name NOT IN (SELECT Entity_Name FROM #TempTable2);


Drop table #TempTable1;

Drop table #TempTable2;