September 29, 2008 at 12:02 pm
Hi all: First off I am NOT A DBA, and I did not sleep in a Holiday Inn last night. I am a sys admin with limited sql skill, so be patient.
We use an application that utilizes MSSQL as the backend. The ISV setup the database and application to use the "sa" account, which I find is bad form. However, if I change to use Windows Authentication, some custom stored procedures fail when trying to create local temp tables.
In MSSQL 2005, is there a way I can grant users the ability to create local temp tables (create #tempTable) without granting any additional privileges??
Thanks for the help, Chris.
September 30, 2008 at 6:10 am
Chris,
As a side issue whats sleeping in a Holiday Inn got to do with things?
September 30, 2008 at 12:00 pm
Mark (9/30/2008)
Chris,As a side issue whats sleeping in a Holiday Inn got to do with things?
It was a joke taken from a lame TV commercial.
September 30, 2008 at 12:08 pm
cmosentine (9/30/2008)
Mark (9/30/2008)
Chris,As a side issue whats sleeping in a Holiday Inn got to do with things?
It was a joke taken from a lame TV commercial.
Actually, to be precise, it is Holiday Inn Express! (Sorry, I am Production/Development DBA :hehe: )
😎
September 30, 2008 at 12:15 pm
Lynn Pettis (9/30/2008)
cmosentine (9/30/2008)
Mark (9/30/2008)
Chris,As a side issue whats sleeping in a Holiday Inn got to do with things?
It was a joke taken from a lame TV commercial.
Actually, to be precise, it is Holiday Inn Express! (Sorry, I am Production/Development DBA :hehe: )
😎
You are correct! But how 'boat my original question? Whay are my users unable to create local temp tables?
September 30, 2008 at 12:25 pm
What exactly is the error you get?
September 30, 2008 at 12:36 pm
Lynn Pettis (9/30/2008)
What exactly is the error you get?
The error is that the user "cannot access the database". When she clicks through that error we get "Database Connection Error HY007 ... Associated statement is not prepared."
This whole issue started when we changed the security model on this database from using 'sa' to using Windows Authentication. We had a few custom stored procedures that broke. If I make this user an administrator she can run the procedure fine.
I spoke with the developer and they say it is due to the creation of local tables which required the 'sa' account. I looked at the procedure and indeed there are a few "CREATE #t1" and similar statements.
From my very limited knowledge I thought all users could create these types of tables as they are stored in tempdb.
September 30, 2008 at 12:48 pm
I will have to do some research, but the developer is full of it when it comes to needing to have to use sa to create temp tables.
I'll let you know what I find if someone else doesn't beat me to it.
😎
September 30, 2008 at 12:54 pm
Lynn Pettis (9/30/2008)
I will have to do some research, but the developer is full of it when it comes to needing to have to use sa to create temp tables.I'll let you know what I find if someone else doesn't beat me to it.
😎
I am not sure if that is what the developer meant, but I am sure that their use of the 'sa' account is not the way things should be done. I have always used the least-rights-needed model and having everyone access the database using 'sa' is not proper.
September 30, 2008 at 1:09 pm
A quick fix may (or may not) be to add the user to the db_ddladmin role in the database in question.
I haven't found anything else as of yet.
😎
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply