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 |
|
Geneoh
Starting Member
4 Posts |
 Posted - 2004-08-31 : 10:44:08
|
| I have two issues that are some what related. I am not sure if this belongs here or in the developer section i will start here.I have a VB app that needs to create temporary Stored Procedures in the tmpdb. The only way I get them to execute correctly is to give my SQL Login user system administration rights. I really do not want to do this. Any ideas on what I am missing. I have granted public rights to model and tmpdb but that does not seem to be enough.The second part in the app I am doing record inserts that I need to turn on identity seed. But again this fails unless I have system administration rights. Any other thoughts on how to allow this with out granting these rights?The last part of this is then how do I create scripts to move these settigns out to my live databasesthanks so much for the help!!!GeneEugene O Howard |
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2004-08-31 : 11:11:25
|
| OkayFirst off, I'm confused why you would want to do this?Second, in order to create a procedure, your login would need DDLADMIN database role ticked. Tick that along with datareader and writer for tempdb and your logon would have the rights you want..-------Moo. :) |
 |
|
|
Geneoh
Starting Member
4 Posts |
Posted - 2004-08-31 : 11:17:29
|
| The applicaiton is a report Righter that uses stored PROC's to get data from multipel databases. When the user is testing the Proc I create it in the TEMPDB until they are ready to save it.OK So I need those three sets of rights How do I grant them in a script?thanksEugene O Howard |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-08-31 : 13:02:16
|
Did you know that those stored procedures will be lost when you reboot the server or restart the MSSQLSERVER service? Each time that happens, tempdb is recreated.And to write to an identity column, you can use SET IDENTITY_INSERT ON. For permissions:quote:
Execute permissions default to the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles, and the object owner.
Tara |
|
|
|
Geneoh
Starting Member
4 Posts |
Posted - 2004-08-31 : 13:09:50
|
| Thansk Tara I am aware that they will be lost. The reason I am using the tempDB is that they are only stored here while testing once thet are comleted they are saved to my mane applicaiton databaseEugene O Howard |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-08-31 : 13:14:02
|
| You will use the GRANT command with CREATE PROC. You will need to recreate these permissions after each reboot of the server. or restart of the MSSQLSERVER service.Tara |
|
|
|
|
|
|
Rights Issues Help Needed