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

[kjv1611]

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

 

[gmmastros]

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.aspx?f=255&MSPPError=-2147217396

-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

 

[kjv1611]

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

 

[Andrzejek]

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.

 

[kjv1611]

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

 

[gmmastros]

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.aspx

-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

 

[kjv1611]

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

 

[kjv1611]

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. 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