Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We have a situation where we have restored a large number (50+) of MSSQL2008 databases from backup and most if not all of them require that a user is granted EXECUTE permissions on scalar functions and stored procedures in the dbo schema.

I've seen bits of documentation with T-SQL queries to grant execute permissions on single stored procedures or scalar functions, but I need to do this for multiple DBs and multiple stored procedures.

Unfortunately, my experience with MSSQL is limited so I have no idea where to begin with this.

Any help would be awesome.

share|improve this question
add comment

2 Answers

up vote 1 down vote accepted

This bit of nested dynamic SQL grants exec or select on all the various procedure and function types, that were not shipped by MS, and that exist in any database outside of master, msdb, tempdb and model. It is certainly not easy on the eyes, tedious to reverse engineer, and I haven't thoroughly tested it, but hopefully if you work through it you can figure out what I've done. ALl you really need to specify is the name of the login you want to grant the rights to - of course this assumes that the login has been mapped to a database user with the same name in each database. That was a layer of complexity I wasn't prepared to add, sorry. :-)

DECLARE 
  @principal SYSNAME = N'some_server_principal';


DECLARE 
  @sql1 NVARCHAR(MAX) = N'', 
  @sql2 NVARCHAR(MAX) = N'',
  @sql3 NVARCHAR(MAX) = N'';

SELECT @sql1 += N'
SELECT @sql += N''SELECT @sql += N''''
USE ' + QUOTENAME(name) + ';
GRANT '''' 
  + CASE WHEN type IN (''''P'''',''''PC'''') THEN ''''EXEC'''' ELSE ''''SELECT'''' END 
  + '''' ON '''' + QUOTENAME(SCHEMA_NAME(schema_id)) + ''''.'''' + QUOTENAME(name) 
  + '''' TO ' + QUOTENAME(@principal) + ';'''' FROM ' + QUOTENAME(name) + '.sys.objects
  WHERE is_ms_shipped = 0 AND type IN (''''P'''',''''PC'''',
    ''''FN'''',''''AF'''',''''FN'''',''''TF'''',''''IF'''');'';'
  FROM sys.databases
  WHERE database_id > 4;

EXEC sp_executesql @sql1, N'@sql NVARCHAR(MAX) OUTPUT', @sql = @sql2 OUTPUT;

EXEC sp_executesql @sql2, N'@sql NVARCHAR(MAX) OUTPUT', @sql = @sql3 OUTPUT;

-- you won't be able to validate all of this, since Management Studio will
-- truncate the output quietly. But you should get a good sense of what the
-- end result will be:

SELECT @sql3;

-- When you are happy with the above, uncomment this:

-- EXEC sp_executesql @sql3;

This is kind of like Inception. In order to generate the right output, you have to figure out which layer every element is on. This is why the initial string is so ugly, and littered with escaped apostrophes in sets of 2 and 4. It can be a little unnerving at first, but you can become adept very quickly at writing dynamic SQL for metadata purposes.

share|improve this answer
    
+1: Heh, very nice. –  RBarryYoung Jul 26 '12 at 3:37
add comment

Consider using scripts and variables, see Use sqlcmd with Scripting Variables:

 c:\>for %d in (db1 db2 db3 db4) do for %f in (fn1 fn2 fn3 fn4) do sqlcmd -E -d %d -v fn="%f" -Q "grant execute on [$(fn)] to [user];"

There are other techniques, like using PowerShell or doing T-SQL scripts with dynamic SQL, but I find this simple combination of CMD batch and SQLCMD variables quite powerfull and easy to use. You can get more fancy, as CMD's FOR accepts input from files (eg. store the list of DBs and/or objects in a .txt file) and of course SQLCMD can execute a .SQL script file with variables substitution, not a single ad-hoc line as in my example.

share|improve this answer
    
The challenge with this is still going to be building that list of databases and procedure / function names. As written, at least on first glance, I don't think it will work (without a ton of exceptions) unless fn1, fn2 etc. exist in every single database. –  Aaron Bertrand Jul 25 '12 at 17:57
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.