Inter-database security handling question

Question

Inter-database security handling question

pblsoft

Valued Member

Points: 70
More actions
September 16, 2006 at 7:15 am

#103234

Hi all
I have a question related to inter-database security. My situation is this: I have two databases, assigned to hold data from two different applications. Some informations need to be synchronized between the two databases. I would like to do this using triggers on the relevant tables. My problem is, that the two databases each have its own set of users, and how will the code in my triggers in one database react security-wise with the tables in the other database? I am not up to speed on how security is implemented in this situation. Can I create a view in database1 to a table in database2 and assign permissions to users/roles in database1 and then expect them to have rights in database2? Can I have some sort of server-wide user that I can activate in my triggers? Any ideas on how to implement this are appreciated.
Peter

Viewing 6 posts - 1 through 5 (of 5 total)

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

I think you may have a look at replication (maybe bi-directionnal). It's surely gonna come up later with admin DBAs (I'm not one of them).

Check it out in books online and see if it can solve your problem.

Eros-158633 SSC Enthusiast Points: 139 More actions · Answer 2

You have to connect them by adding the LINKED SERVER entries in any one or both databases. One of the authenication method is that all local user pretending a common Login to login the linked server.

e.g. user of Server A, Peter, try to retrieve data from Server B. Ask the DBA of Server B to prepare a login called "User_Server_A". And give it a ceratin of rights.

Then ask DBA of Server A to add a linked server in the Server A with this newly created a/c.

Then whenever, Peter or John try to connect to Server B from A, they will pretending "User_Server_A".

For further info, check it in Book Online with keywoeds "linked servers, described"

Joseph Mulhall SSCarpal Tunnel Points: 4672 More actions · Answer 3

I beg to differ. The poster has one server with two databases, not two servers.

Joseph Mulhall SSCarpal Tunnel Points: 4672 More actions · Answer 4

"Can I have some sort of server-wide user that I can activate in my triggers? Any ideas on how to implement this are appreciated."

Absolutely, sp_addlogin creates a server login, not a database login.

The solution is not to move the data at all - merely USE database 1 when you need data from there, and USE data 2 when you need data from there. Or use the appropriate four part naming convention within your queries or procedures to call the data from the correct database.

pblsoft Valued Member Points: 70 More actions · Answer 5

Thank you all for your responses. As it ended up, the solution was much simpler than I thought. Application A (A third-party product) uses internal client-defined access control, and all DB access is done using one user, so I only have to grant this user the appropriate rights to my database.

Peter