1

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
  • Have you checked for an explicit DENY? – Mr.Brownstone Jan 5 '18 at 3:08
  • Perhaps USER01 has elevated permissions or is in a role in DB02? – Aaron Bertrand Jan 5 '18 at 3:11
  • Is DB01 a linked server? – McNets Jan 5 '18 at 8:49
4

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;
  • would you interpret this as checking the authorization before altering it above? stackoverflow.com/questions/4921297/… – MacGyver Oct 12 '18 at 22:17
  • the reason I ask is because I'd be altering the authorization on a product we don't own. So I'm trying to find alternatives since their objects (including schemas) may get upgraded in the future and we'll lose this level of authorization. – MacGyver Oct 12 '18 at 22:18
  • Yes, that query or SELECT name, USER_NAME(principal_id) AS owner FROM sys.schemas; will show the owner (authorization) of each schema. Extending or changing vendor apps is always a slippery slope unless they support and provide guidance for doing so. Upgrades may break your code and customizations could block their upgrades. An alternative in this case might be to add the user to db_datareader, if that's acceptable for your security requirements. – Dan Guzman Oct 13 '18 at 12:21
  • Thanks. We need to build views on one database, but the DDL of the views will reference tables on the vendor's database which they release regular code changes for. Would using db_datareader still apply? – MacGyver Oct 13 '18 at 19:40
  • @MacGyver, yes could add the user to the db_datareader role in the vendor database, or grant them SELECT on the tables directly, so that they can read from the vendor tables directly with the broken ownership chain. The downside is that those users could also query those tables directly outside of your view. – Dan Guzman Oct 13 '18 at 21:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.