Getting object permissions

I have always wanted a quick way of Scripting the permissions that have been granted
for an object. I felt it would be handy to get the permission details of an table/sp without going to the enterprise manager. So a small script to do the same.

I have wrapped up the script into a SP called 'sp_get_permissions' and uploaded it here.

sp_get_permissions takes object name as parameter and provides the permission script for the specified object. sp_get_permissions also accepts null parameter and provides the permission script for all objects in the database. This would be extremely handy if you are doing deployments between DEV/Staging and Production.

Deploy it in your master database and you can use it from any of your databases.

Sample call:



The sp_get_permission works for both 2000/2005.


Now for the second part of the post..

If you are frequent user of sp_helptext then you would have felt the need
to get the sp/view's create script along with the permissions attached to it.

To do the same I have tweaked the sp_helptext code to make sp_helptext2
which would provide the object's script along with all the permission details.

Download sp_helptext2 from here ,deploy it to your master db and you can use it from any of your databases.

Sample call




Just a small note. These two sps strictly take the object name as the parameter.
So avoid prefixing the object owner name when passing the parameter..ie
EXEC sp_helptext2 'dbo.testProc' doesnt work. But EXEC sp_helptext2 'testProc' does.