Need a magic script :) - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Need a magic script :)

Author

Topic

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-07-09 : 19:42:58

Anyone got a script to loop through all stored procedures in a DB that start with 'Sproc_' and grant Execute access to a specifict user role?

Thanks!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-07-09 : 19:46:24

You'll have to check this first as I modified my script pretty quickly, but it should be close. It uses a dreaded cursor, but you do have to loop some way if you want to do it in one step.


SET NOCOUNT ON

DECLARE @objName varchar(80), @objType char(2)
DECLARE grant_perms_on_sps CURSOR FOR	
SELECT name, type
FROM sysobjects 
WHERE type = 'P' AND name LIKE 'Sproc[_]%'

OPEN grant_perms_on_sps
FETCH NEXT FROM grant_perms_on_sps 
INTO @objName, @objType

WHILE @@FETCH_STATUS = 0
BEGIN
	EXEC ('GRANT EXECUTE ON dbo.' + @objName + ' TO RoleName')

	FETCH NEXT FROM grant_perms_on_sps 
	INTO @objName, @objType
END

CLOSE grant_perms_on_sps
DEALLOCATE grant_perms_on_sps

Tara Kizer
http://weblogs.sqlteam.com/tarad/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-07-10 : 04:35:12

or run this in QA

SELECT 'GRANT EXECUTE ON dbo.' + name + ' TO RoleName'
FROM sysobjects
WHERE type = 'P' AND name LIKE 'Sproc[_]%'

copy the result back to QA and run them

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources