June 17, 2005 at 12:33 am
Is there an easy way of doing this in sql 2k short of enumerating all stored procs in a database and using something like
GRANT EXECUTE ON <spname> to GroupName
for each stored proc? This is a nuisnace especially as new stored procs are added all the time to the database.
TIA,
Bill
P.S. I don't think there is a Sql 2000 Role that offers that capability.
P.P.S. I used
select name from sysObjects where type = 'P' and name not like 'dt_%'
order by name
to get the stored proc names in QA. Then I pasted them into an editor and prepended the GRANT part. Not difficult but not eloquent either.
June 17, 2005 at 1:38 am
No, there is no easy way of doing that.
But if you want to do it more times you could consider doing it in a cursor loop then doing cut & paste...
//Hanslindgren
June 17, 2005 at 4:28 am
This just runs a cursor for you, but you don't have to write it
EXEC master.dbo.xp_execresultset '
SELECT
''GRANT EXECUTE ON dbo.'' + SPECIFIC_NAME + '' TO <role>
FROM
INFORMATION_SCHEMA.ROUTINES
WHERE SPECIFIC_NAME LIKE ''usp_%''
', '<dbname>'
June 17, 2005 at 10:05 am
Shawn,
I modified your code as follows:
>>>>
EXEC master.dbo.xp_execresultset '
SELECT
''GRANT EXECUTE ON dbo.'' + SPECIFIC_NAME + '' TO Maintenance
FROM
INFORMATION_SCHEMA.ROUTINES
WHERE SPECIFIC_NAME LIKE ''usp_%''
', 'Northwind'
>>>>
When I run it I get this:
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near ''.
Server: Msg 105, Level 15, State 1, Line 6
Unclosed quotation mark before the character string '
'.
I wil try to fix it myself but I want to make sure I don't screw up what you intended.
TIA,
Bill
June 17, 2005 at 10:54 am
It's the inner string, but I can't test it.
Try adding more quotes e.g
EXEC master.dbo.xp_execresultset '
SELECT
''''GRANT EXECUTE ON dbo.'''' + SPECIFIC_NAME + ''.. TO Maintenance
FROM
INFORMATION_SCHEMA.ROUTINES
WHERE SPECIFIC_NAME LIKE ''''usp_%''''
', 'Northwind'
Maybe when I posted it removed the doubles
test this:
SELECT 'SELECT
''''GRANT EXECUTE ON dbo.'''' + SPECIFIC_NAME + ''.. TO Maintenance
FROM
INFORMATION_SCHEMA.ROUTINES
WHERE SPECIFIC_NAME LIKE ''''usp_%''''
'
to assist how it turns out
June 17, 2005 at 1:32 pm
That works better. Thanks. (I hate all those ' , '', ", ''', etc. )
Bill
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply