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