In this series I am going to show you three different ways of enabling cross-database access, starting with the worst, but for whatever reason, still common “solution”. The second one will be better, but still not very secure. Finally, we will see how cryptography can ultimately solve this problem.
Remember, login sa is mapped to dbo account in each and every database
To connect to a SQL Server database, we need a login (at a server level) and a user account (at a database level), that is associated with this login. This means that we have two different types of security tokens: login tokens and user tokens.
Since SQL Server 2000 Service Pack 3, databases are isolated from each other in a way which means that an object in one database cannot access objects that live in a second db. However, sa login is a notable exception. To see this, let me switch to sa login context, create two databases and check what user account is associated with this login in a master database and in a user one:
Let’s say that DB1 is a client’s database that contains some valuable information, and DB2 is a competitor’s database. So, in a DB1 there will be a table:
Now, if we try to execute this procedure, it will work just fine:
No error was returned, which means that this SELECT * FROM DB1..T statement succeeded. The reason is simple: sa login belongs to sysadmin server role, this role is mapped to dbo user in every database and a dbo can do anything within the database.
Take notice that those databases are not trustworthy neither cross-db chaining enabled:
And even if those database had different owners, the situation would not change:
Lesson learned — sa is a server admin and if an application connects as sa, it will be able to do everything not only inside its database, but with others databases as well. And there is absolutely nothing that you, as a DBA, can do about it. The only solution is to persuade the application vendor to not using this login.