Set user on multiple sp - 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 2000 Forums

Transact-SQL (2000)

Set user on multiple sp

Author

Topic

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2007-08-04 : 01:36:32

My old workplace....

The sql server failed and only the .MDB exists.

Ok, need to set the permissons for the stored procedures once fixed to a new db.

Can be done one at a time.

How can this be set for all stored procedures at once?

The line for one is:

GRANT EXECUTE ON mySPname TO someUser

How can this be set for multiple stored procedures?

thanks,

Zath

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-08-04 : 12:45:30

Run this, get the script an compile it.



SELECT 'GRANT EXECUTE ON ' + name + ' TO someUser'
From sysobjects
where Type = 'p'

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources