We are setting up local sql server logins on a server that has multiple databases and have the situation where a piece of software will need to connect to more than one database, so I'm looking for some advice on which approach is better:
Simplifying our dilema, let's assume we have 1 application, 1 server, 2 databases on it:
Create a single APP_RW login which can be mapped to 2 databases.
Create 2 logins APP_DB1_RW and APP_DB2_RW, each of which is mapped to 1 database.
SQL Server allows both methods and there are obvious pro's and con's...application login's are simpler by having 1 account, small possibility of updating the wrong database table if tables are named identically in both databases.
I just wondered what the general best practice is?