Why can't a stored procedure read a table from another database (I must be using GRANT and DENY wrongly)

Question

I have two Microsoft SQL Server 2000 databases, and a stored procedure on one tries to read data from the other. This used to work fine, but since I became security-conscious and changed the login (SQL user) from "db owner" to "denydatareader" the call fails.

I can get things working if I use the group "datareader", but since I do not want this login to have read access to user tables (the ASPs use only procs), I thought this unwise. It also works if I take the user out of all groups!!! Is this OK?

---

One database is called 'Internal' and has a table called 'Stuff'. The other is called 'WebFacing' and has a stored procedure called 'Get_Some_Data' which SELECTs from 'Internal..Stuff'.

I ran this command on the Internal database:
GRANT SELECT ON Stuff TO magnus

I ran this one on the WebFacing database:
GRANT EXECUTE ON Get_Some_Data TO magnus

My ASP uses the SQL login 'magnus' and connects to the 'WebFacing' database. When it tries to EXEC the procedure, it errors with:
SELECT permission denied on object 'Stuff', database 'Internal', owner 'dbo'.

---

(Apologies if this is a dumb question, but I've been shoved in the deep end and only learnt about GRANT and DENY yesterday. I have tried Googling...)

Answer 1

Associating SQL logins with groups/roles (or not) is more a function of convenience for the person who has to keep track of permissions for the database(s). Since you're new to all this, I would first focus on getting the permissions of the particular login correct prior to worrying about managing permissions via groups/roles.

One of the things that gave me fits when I first started working with SQL Server permissions was understanding whether the permissions used for the execution of a stored procedure were those of the SQL login used to call the proc, or the SQL login associated with the creation of the proc itself. The term for that (which set of credentials and associated permissions is used for the execution of proc code) is called the "security context" under which the stored proc runs. I've been working with MySQL recently, but if I remember correctly, the default security context used to execute a stored procedure on SQL Server is that of the CALLER rather than the proc owner. This always struck me as counterintuitive, because it seemed to me that one of the key advantages to using stored procedures should be the ability to grant only EXEC permissions on specific procs to CALLER logins. But when I tried to do it that way, I'd inevitably get permissions errors because the credentials I was using to call the proc wouldn't have the permissions needed to complete one or more of the operations contained within the stored procedure.

If you're using SQL Server 2005 and want to be able to grant only EXEC permissions to CALLER credentials, then this article may help shed some light on how to do so. In my opinion, that's the "correct" way to do things, though I'm sure there are probably others who might disagree (though I'd probably stick to my argument on this point).

Anyway, I'm not sure how much I've clarified the issue for you with this post. SQL Server permissions management is indeed a bit of a deep end when you first delve into the whole issue. It's not making it any easier that you're having to deal with setting them up accross multiple databases.

Answer 2

When you go across to a different database on the same instance, each "commmand" has permissions on its tables and views evaluated by the destination database in the same context before it is allowed to run.

This is unlike being completely in the same database as the SP, where the SELECT permissions are not required for tables referred to in an SP for which you have EXEC rights.

You can use EXECUTE AS to execute as a person who does have the rights, or another alternative is to make a VIEW in the second database which returns only the columns they need, or to set column-level permissions on the tables, or to create an SP and call it (you will have to insert the results into a temporary table, so not terribly efficient).

Answer 3

You can enable Croos-Database ownership, without having to give select permissions on the table that your sproc is calling. But use at your own risk. I use it for the scenraio you are describing:

alter database dbname set db_chaining on