SELECT permission denied on object
-
This is difficult for me to understand...
I have a Sql Server 7 Db. I have a DB where I've issued Rights to a user (application used) that can execute procedures in that database and select data from views. All works as expected in that this user does not have explicit rights to the tables and therefore can't access them. The problem I'm running into is when a stored procedure in DB1 accesses objects from a 2nd DB on the same server. The object is explicitly called as DB.dbo.Table but I keep getting this error telling me that "SELECT permission denied on object 'ATableName', database '2ndDatabase', owner 'dbo'. (the names of the objects have been re-written).
any ideas why this is happening? I thought that if a user had rights to execute a sproc that they would retreive the requested data, but according to this error it makes me think that I have to grant them select rights on the table objects of the 2nd database which doesn't seem right to me
-Francisco
-Francisco -
Not cross database. Permissions are rechecked the dbo in db1 might not be dbo in db 2. You need to grant rights in the 2nd db.
Steve Jones
-
Steven,
I have the same problem, but it occurs within a view. When i create a view like this:
CREATE VIEW view1
AS
SELECT t1.field1, t2.field1
FROM table1 t1
INNER JOIN table2 t2 ON t1.key = t2.key
I don´t have to grant access to table1 and table2, granting access to view1 is enough.
But if the view has to access an external table, like this:
CREATE VIEW view1
AS
SELECT t1.field1, t2.field1
FROM table1 t1
INNER JOIN db2.dbo.table2 t2 ON t1.key=t2.key
I have to grant read access on table2 to the database users who will access this view, otherwise the view doesn´t work and returns select permission denied on object table2...
I think this is not correct because one of the goals we want by creating a view is improving the security by not allowing the access to tables.
Alexandre
-
Within the same database, ownership chains work so long as you don't switch owners. For instance, if View1 refers to Table1 and they have the same owner, the ownership chain means you only have to grant permissions against the view.
When you cross databases, the rules change.
Pre-SQL Server 2000 SP3 (to include SQL Server 7), cross-database ownership chains are on by default. This means that as long as the objects map back to the same login, an ownership chain can form. If you're on SQL Server 2000 SP3, you can turn cross-database ownership chains off (with the exception of the system databases where they are hard-coded on).
However, for cross-database ownership chains to work properly, the person logged in has to have a valid user account in the second database. For instance, if the person (think login) has a user account in database A which has view1, the same person (login) must have a valid user account in database B which has table1. Otherwise, the user doesn't have access to the database and therefore doesn't have access to table1.
The reason the master database works is because the guest account is enabled (and you aren't supposed to disable it because you will break things). Keep in mind that if a particular login doesn't have an explicit user account in a given database and the guest account is present, the login will map to the guest user.
Now back to SQL Server 2000 SP3. The recommended practice is to turn cross database ownership chains off (and the default setting when you apply the service pack unless you change it during the install). If you do, that means the ownership chain won't happen and that means explicit permissions are required.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
Edited by - bkelley on 04/28/2003 11:12:28 AM
K. Brian Kelley
@kbriankelley
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply