One account for many databases?

Question

Post reply

One account for many databases?

Barkingdog

SSCoach

Points: 18670
More actions
November 23, 2009 at 5:35 pm

#206705

Most of our databases are for OLTP and we assign a single login\user to access that database. The name of that user contains the name of the database. We are now exploring OLAP databases and need to run queries that span multiple databases. The queries would fail if we continued the OLTP practice of assigning each database a unique user name (because the query can only run under one security context.)
We could just say "that all databases belonging to this line of business, and application, can be accessed using this single account". Hope no one every cracks that single accounts for they would then have access to all the databases in the LOB!
How do you deal with this issue.
TIA,
barkingdog

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

support.sql Default port Points: 1444 More actions · Answer 1

You can create a generic user and add that user into every database needed by OLAP system.

SNM

Try Free Microsoft SQL Server Data Collector & Performance Monitor.

http://www.analyticsperformance.com[/url]

@nalyticsperformance - Microsoft SQL Server & Windows Server Free Data Collector

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 2

Ummmm.... I haven't used them in SQL Server but wouldn't synonymns do the trick here?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 3

If you are looking at OLAP are we to assume SSAS or equivalent? Are you going to be building that directly from the operational systems?

CEWII