For years I have used a script that I had to manually add the ADO user to, and then run it on a database, take the results, and then execute them. I finally got fed up with this, so I wrote a script I could use to automatically just run after I added a Stored Procedure, Function or User to a database, and have it just fix all my execute permissions.
SLICK!
** Disclaimer: REVIEW THIS BEFORE RUNNING, AND MODIFY IT APPROPRIATELY FOR YOUR SCENARIO. I TAKE NO RESPONSIBLITY FOR YOU RUNNING BAD CODE OR INCORRECTLY SETTING SECURITY ON YOUR DATABASE!
use [dbname]
-- This grants Execute to ALL stored procedures for ADO Logins (specifically db_datareader users) on a database
DECLARE @Login varchar(50)
create table #ADOUSERS(
group_name varchar(255),
group_id int,
users_in_group varchar(255),
userid int
)
print 'The rows affected on the next line is the number of ADO users'
insert into #ADOUSERS
exec sp_helpgroup db_datareader
print 'Granting these users permissions to execute all user stored procedures'
declare LOGINTMP CURSOR for
select users_in_group from #ADOUSERS
DECLARE @ProcName varchar(128) -- Procedure Name
DECLARE @sql varchar(500)
DECLARE ProcCursor CURSOR FOR
SELECT Name FROM sysobjects
WHERE TYPE in ('FN','P')
AND name NOT LIKE 'dt[_]%' -- remove system procs
--AND not objectproperty( id, N'IsMSShipped' ) = 1 -- Needed??
--Loop through records and grant execute permissions
OPEN LOGINTMP
FETCH NEXT FROM LOGINTMP into @Login -- Lookup the users from the upper nested cursor
WHILE (@@FETCH_STATUS = 0)
BEGIN
Print '********************************* ' + @Login + ' *********************************'
OPEN ProcCursor
FETCH NEXT FROM ProcCursor INTO @ProcName -- Get the List of SP's/Functions
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @sql = 'GRANT Execute ON [' + @ProcName + '] TO [' + @Login +']'
EXECUTE (@sql)
Print 'Updated permissions on ' + @ProcName + ' - Granted to ' + @Login
FETCH NEXT FROM ProcCursor INTO @ProcName
END
CLOSE ProcCursor
FETCH NEXT FROM LOGINTMP into @Login
END
-- Done, now cleanup!
DEALLOCATE ProcCursor
DEALLOCATE LOGINTMP
DROP TABLE #ADOUSERS