Global Database

[HaveTrouble]

Each customer has one database in my system. Customer can login from a front-end ASP site to view data in their database. Now I want to setup a "super login" who can view the data in all databases. Some of the reports will need to pull the data from multiple databases. What's the best way to achieve this ? All I can think of is construct the query like:

select * from mydb1.dbo.mytable
union
select * from mydb2.dbo.mytable
union
select * from mydb3.dbo.mytable

Is there any better way to do it ? In addition to pulling report, the "super login" user needs to update data too. Please help.

 

[MDXer]

create a Role in the various databases named something like Global user. give the rold the required permissions to accomplish you the tasks you require. After creating the roll assign you "super login" to these databases as members of this roll.

This is better than a single login that multiple people use because if you have to revoke usage from one person it requires resetting the password and telling all users. Also better than giving every one SA rights or DBO on the various dbs for obvious reasons.

Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[SQLDenis]

In addition to the response above you might want to create a view of all the tables in question in your Global DB so that everything is in 1 place




“I sense many useless updates in you... Useless updates lead to defragmentation... Defragmentation leads to downtime...Downtime leads to suffering..Defragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/

 

[HaveTrouble]

I've created a view that unions all the invoice table in child's database. However, it doesn't allow me to execute update statement from the view because it contains union operator. What's the most efficient way to update child's database ?