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.
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.
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.