On this page.... RSS 2.0 | Atom 1.0 | CDF
# Tuesday, December 13, 2005

The script below will grant execute on all stored procedures in the selected database to the specified role.  Just change it to use your database and, if desired, change the role name.  After that, just add users to that role.  You should be able to rerun this script as needed.  It was tested in 2005 but should work in 2000.

use [YourDatabase]

declare @sprocName sysname,
    @roleName sysname,
    @grantStatement nvarchar(4000)
select @roleName = N'db_exec_all_sprocs';

if not exists(select * from sys.database_principals
    WHERE name = @roleName AND type = 'R')
        exec sp_addrole @roleName;

declare sprocs cursor local for
    SELECT [name] FROM sys.objects
        WHERE type in (N'P', N'PC')

open sprocs
while (1=1)
    fetch next from sprocs into @sprocName
    if @@fetch_status != 0 break;
    select @grantStatement = N'grant execute on '
        + @sprocName + N' to ' + @roleName;
    print N'Granting: ' + @grantStatement;
    exec sp_sqlexec @grantStatement;
close sprocs
deallocate sprocs

Tuesday, December 13, 2005 5:10:03 PM (Eastern Standard Time, UTC-05:00)  #    Disclaimer  |  Comments [1]  | 
Wednesday, December 14, 2005 7:50:27 AM (Eastern Standard Time, UTC-05:00)
Nice one!
Comments are closed.

The opinions expressed herein are solely my own personal opinions, founded or unfounded, rational or not, and you can quote me on that.

Thanks to the good folks at dasBlog!

Copyright © 2020 J. Ambrose Little