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.
| Author |
Topic |
|
admin001
Posting Yak Master
166 Posts |
 Posted - 2003-10-06 : 05:03:10
|
| Hi , I have restored one database and just added a user ID .Now I need to give EXE permissions to all the user stored procedures in that database . Is there any way to do it through stored procedures so that I can grant access to all the SP's at one time or I have to individually give access to one SP at a time . Is there any convinient way to do it ?Thank you very much . |
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2003-10-06 : 05:16:39
|
| Use the GRANT command. You'll need to do each procedure.-------Moo. :) |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-10-06 : 12:44:39
|
| Search the forums for isp_Grant_Permissions. I wrote a stored procedure that can do this for you.Tara |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-10-06 : 12:49:25
|
| Or, look in your sql toolbox...Tara...you might want to mention something about your standards...Brett8-)SELECT @@POST FROM Brain ORDER BY NewId()That's correct! It's an AlphaNumeric! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-10-06 : 12:57:34
|
| Hope this helps:[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=28422&SearchTerms=isp_grant_permissions[/url]The stored procedure can easily be modified for your environment. Let me know if you need help.Tara |
|
|
|
admin001
Posting Yak Master
166 Posts |
Posted - 2003-10-06 : 23:42:07
|
| Well , thanks to all for your responses . Thanks a lot Tara for the script . In fact your script was amazing as it give me an another idea to do it . Maybe you all can validate whether it would be proper to do this way:Execute the the query below in QA -SELECT 'GRANT EXECUTE ON ' + NAME + ' TO login' (where Login with the name of the new Login ) FROM SYSOBJECTS WHERE TYPE = 'P' AND LEFT(NAME,2) <> 'sp' -- system procs AND LEFT(NAME,2) <> 'dt' AND LEFT(NAME,2) <> 'xyz' -- User Stored procs.Then paste the results of this query in the Query analyzer again and execute it . What do you suggest ??Well things are really getting better and better man !!! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-10-07 : 12:28:16
|
| That works too. But if you use dynamic sql with a cursor, you don't have to copy and paste. Cursor and dynamic sql are useful for cases like this especially since they are not part of an application.Tara |
|
|
|
|
|
|
Access to Stored Procedures.