March 7, 2007 at 1:36 pm
I'm having a problem querying data from two different databases on a SQL 2000 server. We have two databases, a main data db, and a separate security db that has login info.
Database logins for the data db DO NOT have access to the security db. I have a function that runs in the data db, but needs to query the security db. This works if the function runs from a user that has permissions in both databases (sa for example). But as a general user, it fails, because that general user login isn't a valid user in the security db. How can I do this?
Is there any way in SQL 2000 to "run as" another user? I just want to execute the subquery that goes to the security database as a user that has permissions to access that info. 2005 apparently has an "execute as" method, but that doesn't help me now. There's *got* to be some way to do this in 2000, right?? Thanks for any help!
March 7, 2007 at 3:22 pm
How are you running the script?
You could create the script as a stored procedure, then have it run by a job. In the job you can list who is the owner and who it runs as.
-SQLBill
March 7, 2007 at 4:44 pm
Thanks for the reply! It's actually a user defined function - it's being called from classic ASP code and returning a value. There is logic within the function that works on the data db, but we want to check a value in the security db from the function, too.
I'm not sure if running it as a job would apply for what we want to do, which is a real time query from a web page.
March 8, 2007 at 6:43 am
Why don’t you just create a database role in the security database, just give select permissions to the table or columns that you need to access and the just add the logins to that Role. If you want to be extra careful you could do a deny access on the role to all of the rest of the tables in the security database.
March 8, 2007 at 7:00 am
That looks like what we're going to do. The thing is, old (poor) database design has a corresponding SQL login user for every user of the system (which is also in the corresponding security db), so we'd basically have to create a db login for every user. It just seems sloppy - why can't you execute a chunk of code or script with another users permissions in 2000? Sigh....
March 8, 2007 at 7:18 am
Since you are running this through an application another possibility is for you to use an application role in the security database. Then it will run under the applications credentials not the user.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply