INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

One Server: Two Databases with Separate Logins: One SQL Script?

One Server: Two Databases with Separate Logins: One SQL Script?

(OP)
My new employer is a small company, and we have one SQL Server that houses databases for a few systems. Each system has its own login.

What I would like to be able to do (once I get permissions for the second system) is to at least explore writing one query against 2 databases that have separate logins.

My first question is: Is this even possible? It seems to me that I would have to have a separate connection to the server with the proper credentials for each database.

My next question is: If possible, is it advisable? Would I run into more issues in this scenario than in a scenario where my Windows credentials are accepted at all connected databases?

What I expect to happen currently is:
Get permissions to the 2nd database (I currently have permissions to the "main" system database, and second database is in progress)
Write queries from each database, dumping results into temp tables so I can query without any concern of affecting production.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

RE: One Server: Two Databases with Separate Logins: One SQL Script?

If both databases are on the same instance, then you just need a single login that has permissions to both.

If you want to limit accessibility to the other database, you could use the Execute As clause on any stored procedures that use both databases.

https://msdn.microsoft.com/en-us/library/ms181362....

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

RE: One Server: Two Databases with Separate Logins: One SQL Script?

(OP)
Hmm, yeah, I have never needed to use EXECUTE AS, but I wonder if I could do this:
1. In one script, run a standard SELECT from Database1, dumping my results to the temp database, so #TempDb1Table
2. In same script window, use the EXECUTE AS to run against Database2, and dump those results to another temp table, #TempDb2Table
3. Then I can run my comparison SQL against the 2 temp tables.

I don't think I'll be able to, at least for now, build any stored procedures or user defined functions. There is no Sandbox table, and I don't think adding anything (though safe) to the production databases will be considered. That might be something I can work on introducing at a later date, though.

Thanks for the thoughts

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

RE: One Server: Two Databases with Separate Logins: One SQL Script?

How about creating a View in Database1 with appropriate data and give SELECT privileges to 'whoever'. And do the same in Database2
Would that work?

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: One Server: Two Databases with Separate Logins: One SQL Script?

(OP)
Hmm, maybe. But I don't think they'll like the idea of me creating any objects inside the databases just yet. Eventually, I may be able to do something like that, but I think the best route to take longterm, once I get there, will be to create a Sandbox database, and then build anything I want to use in that database to ensure I'm not adding anything at all to a production database.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

RE: One Server: Two Databases with Separate Logins: One SQL Script?

You could also try OPENROWSET. This allows you to specify a connection to another server and/or database.

https://msdn.microsoft.com/en-us/library/ms190312....

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

RE: One Server: Two Databases with Separate Logins: One SQL Script?

(OP)
Oh yeah, that's true. Thanks for that thought. I tried to use that at my last company, but it was not allowed by server settings. I might consider giving that a try as well once I can try anything.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

RE: One Server: Two Databases with Separate Logins: One SQL Script?

(OP)
Well, I'm back to having to go text to data import instead of 2 databases. IT Director wants to keep anyone from querying the 2nd database and found a way to get the text export to work correctly, or so it seems so far. Thanks for the thoughts along the way, though, b/c I'm sure I'll be able to do this eventually. smile I've been at the new company for about a month now, and hopefully much more time to go.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close