I have an application which has a bunch of specific permissions granted to some objects within the database. I encountered a problem while trying to migrate this application from one server to another because I needed to recreate these permissions which were added over the period of a year as and when new features were added to the application.
I ended up using sp_helprotect to list out the permissions for the objects that I was interested in. But I still needed to convert the permissions into the necessary GRANT/REVOKE statements. This can be tedious when you are trying to manipulate permissions across databases and multiple objects. What I ended up doing was writing a crude T-SQL script to help me create the necessary GRANT/REVOKE scripts based on the sp_helprotect output.
Note: sp_helprotect does not return information about securables that were introduced in SQL Server 2005. Use sys.database_permissions and fn_builtin_permissions instead.
So, if you have a securable that you are interested in which was introduced in SQL Server 2005, then this is not going to help you.
Note: This script has been tested only for the following objects:
2. Stored Procedures
3. Functions (UDFs/TVFs)
Modification: Aug 8, 2012: Thanks to Graham [blog] for suggesting the corrections.
set nocount on -- Declare and initialize local variables declare @newuser varchar(255) set @newuser = 'newuser' -- This is the new user that you need to add permissions for -- Create temp table to store sp_helprotect output create table #tbl_permissions (Owner varchar(50), Object varchar(255), Grantee varchar(255), Grantor varchar(255), ProtectType varchar(50), Action varchar(50), Col varchar(50) ) -- Store sp_helprotect output in a temp table insert into #tbl_permissions exec sp_helprotect null,'olduser' -- Get the necessary GRANT/REVOKE/DENY T-SQL commands select RTRIM(LTRIM(ProtectType)) + ' ' + CASE Action WHEN 'CONNECT' THEN Action + ' TO ['+@newuser+']' ELSE Action + ' ON OBJECT::'+OWNER+'.'+OBJECT+' TO [' +@newuser+']' END from #tbl_permissions -- Drop the temporary table drop table #tbl_permissions
Now that a bit of my work was easier, I decided to put this script out on the web so that someone else can benefit from the same. I am sure that there are multiple T-SQL scripts which do the same but I found the one below very helpful. I shall try and make it more comprehensive by using sys.database_permissions in a future blog post.