Ownership chaining can be really cool.

Many of us would have granted a restricted user access to a stored procedure or function called dbo.GetInfo, but denied access to the underlying tables. The user can get access to the data through the stored procedure, but can't get at the tables underneath at all. Makes life nice and safe. But why does this work?

The answer is ownership chaining.

What happens is that the system sees the request come in for the stored procedure (or could be a function, I'll say 'module' to make life easier). It checks to see if the user has permission to execute the module, and it does! Great. So then it enters the module and executes what's in there. Now within the module, the system tries to access a table, and it needs to work out if the user should have access to it.

Of course, the answer should be no, because the user doesn't have access to that table. But here's where the ownership chaining kicks in. The system says "I'm in the context of a stored procedure which is owned by dbo (or whoever). This table I'm trying to access is owned by them too, so clearly I must have access to it!" It's a bit sneaky, I know. Heck of an assumption to make, but it's what happens, and it makes life quite handy for us developers.

What many developers think is that a stored procedure has some special kind of super-access to access stuff because it runs under a different context or something, but it's not the case. The security check is just skipped on the underlying tables if the owner is the same.

"But I can use 'execute as' within a module, and that changes all that" I hear you say, and I'm going to ignore you for a minute.

Cross-database ownership chaining is worth being aware of. You might have read about what I've just written and suddenly realise why various things just worked before. You might also have suddenly wondered why that didn't work in some other situations, particularly when you've tried to access a table in a different database.

Cross-database ownership chaining is just like ownership chaining, but it works across databases. Let's suppose we have two databases, we'll call them db1 and db3 (haha - get it?). A stored procedure in db1 tries to access a table owned by the same login on db3. But it's denied. That's because cross-database ownership chaining is turned OFF by default. Let me explain why.

Barry and I each have a database on a server. I don't have access to his, and he doesn't have access to mine. We both have full access to our own databases, and I don't let Barry see my sensitive data. But somehow, Barry has persuaded the DBA to turn on cross-database ownership chaining. I'm not worried, am I?

Next thing I know, all my data is compromised, and I've lost my job. Why?

Turns out evil Barry created a stored procedure in his own database that tried to access my data. It didn't work. But then he created a user in his database for my login. Yeah! He didn't even tell me. But even if I had known, it wasn't as if that user could do anything in his database. Except that he then changed the owner of that dodgy stored procedure to be that login of mine. Yeah great, so I could access a stored procedure on his machine.

But because of cross-database ownership chaining, when Barry accessed that stored procedure, the system let him see my sensitive data. It had confirmed that he had access to the stored procedure in his own database (that module that was owned by me!), and everything was fine. But when that module tried to access my tables, it noticed that the owner was, well, me - the same owner as the module it was running. So it skipped the check and granted access.

So cross-database ownership chaining is bad, unless you have full control over the whole server. Many of us do, of course, but not always. Be aware of that.

Thanks for reading...

Oh sorry - you were asking about 'execute as'. Yes. Execute as is a great way of avoiding this. Stick all your tables into a schema owned by someone else, and tell your modules to run as a particular user, who presumably has access to the right tables in whichever database is appropriate. Now you can avoid all the ownership chaining, and feel like you have proper control over your security.