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 |
|
OOT
Starting Member
6 Posts |
 Posted - 2007-11-06 : 12:57:25
|
| I'm new to 2005 (new to sql server in general). Up until now it's only been writing easy stored procedures. Read that it's a good idea to have a user who can only run stroed procedures (so aren't connceting with 'sa' privileges and such). How would I go about doing this?The server I am working on has about 15 different databases on it. My database (named "myDBForTest") is the only one I want my end user to be able to connect on. I'm writing a .NET application that will have several users using it at same time. They will all be connecting with same information (limited to only running stored procedures and connecting/disconnecting). I basically want them to be able to run any stroed procedures that are part of my database. If I go back in 4 months and add 100 stored procedures, the next time the log on they should be able to use those new ones also.Is there some kind of command I run to add a user (maybe with username of sprocUser?) who can do this? I'm interfacing with database using SQL Server Management Studio Express (from MS).Thanks for any help you can give. |
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-11-06 : 13:31:08
|
You can create a permissions script as:SELECT 'GRANT EXEC ON dbo.['+ Name + '] to theuser'FROM SysobjectsWHERE Type = 'p' get the results and execute them in QA.Also, usually its a good idea to have the developer include the GRANT script as part of the proc creation script.Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-11-06 : 13:37:20
|
| I'd addORDER BY [name]to that because you may get some system crud ("dt_...") stuff and it will make it easier to delete it if they are all adjacent.Kristen |
|
|
|
|
|
|
User who can connect/run stored procedures only?