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 |
|
catdavis67
Starting Member
6 Posts |
 Posted - 2006-12-14 : 12:35:08
|
| Hi everyone and Happy Holidays!I've got a problem with table creation in stored procedures (SQL Server 2000). We've got an application where the user login only has rights to execute stored procedures. The problem is that a stored proc is dynamically creating a table and so the owner of that table is being assigned to whatever login the application is using instead of dbo. It's causing numerous issues. Is there any way that this can be avoided or changed without granting the user sa privileges?Thanks in advance,Cat |
|
|
pareshmotiwala
Constraint Violating Yak Guru
323 Posts |
Posted - 2006-12-14 : 13:36:55
|
| I feel, the "With Grant" should help you. so when the table is created, the user can grant permissions to whomever you chose to give? |
 |
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2006-12-14 : 14:28:50
|
it should be avoided by not allowing app calls to create permanent tables. temp tables ok, but permanent tables should be fixed at design time. the fact that your app needs to create tables at runtime points to problem with the design.
http://www.elsasoft.org |
|
|
|
catdavis67
Starting Member
6 Posts |
Posted - 2006-12-14 : 16:00:54
|
| I agree. But since this is not my design, and I don't have the time to change it right now, I'm stuck with the original question.Thanks for your help.Cat |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-12-14 : 16:06:15
|
| You will need to grant the user db_owner on the database in order to create dbo objects.Tara Kizer |
|
|
|
catdavis67
Starting Member
6 Posts |
Posted - 2006-12-14 : 16:30:51
|
| I was afraid of that, that is not really an option.Thanks,Cat |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-12-14 : 16:36:02
|
| There is no way around it since the app is expecting a dbo object.Tara Kizer |
|
|
|
|
|
|
Problem with owner of table creation in SP