Sql-server – What would cause “SELECT permission denied” for an object

permissionsSecuritysql server

This is a work backwards kind of issue, since I can't tell you all of the requirements to cause this to happen.

I'm getting the following error for a table (TABLE01) in one database (DB01), but not a table (TABLE02) in the other (DB02), for a brand new user (USER01), when building a view (VIEW##) and granting SELECT permission on the view for the user. I can correct it by granting SELECT permission on the table (TABLE01) in the database that's causing the issue (DB01). But why wouldn't I need to do it in DB02?

CREATE VIEW [DB01].[x].[VIEW01] AS SELECT * FROM [DB01].[dbo].[TABLE01]

GRANT SELECT ON [DB01].[x].[VIEW01] TO [domainx\USER01];

SELECT * FROM [DB01].[x].[VIEW01]; -- throws error below
-- The SELECT permission was denied on the object 'TABLE01', database 'DB01', schema 'x'."

GRANT SELECT ON [DB01].[dbo].[TABLE01] TO [domainx\USER01]; -- fixes error above

BUT IN DB02, I ONLY HAD TO RUN TWO COMMANDS TO GRANT SELECT PERMISSION. WHY WOULD THIS BE?

CREATE VIEW [DB02].[x].[VIEW02] AS SELECT * FROM [DB02].[dbo].[TABLE02]

GRANT SELECT ON [DB02].[x].[VIEW02] TO [domainx\USER01];

SELECT * FROM [DB02].[x].[VIEW02]; -- works fine; returns results

Best Answer

These symptoms suggest the x schema in DB01 is not owned by dbo, resulting in a broken ownership chain. Permissions are then required on indirectly referenced objects.

To maintain an unbroken chain in DB01 for the query that joins tables in the x and dbo schemas, the x schema must also be owned by dbo. This can be changed with ALTER AUTHORIZATION:

USE DB01;
ALTER AUTHORIZATION ON SCHEMA::x TO dbo;
Related Question