Need to grant select permission on View but not in table which is in a different database

Question

I have a table in database A and a view using that table in database B. I want To give select privilege to a user on that view but I do not want to give any access to table.

Can anyone help me on that issue?

Answer 1

I will give a full demo how you can achieve this. Create 2 database

USE [MASTER];
GO
CREATE DATABASE [TestA]
GO
CREATE DATABASE [TestB]
GO

Create a login and user in both database.

USE [master];
GO
CREATE LOGIN TestUser WITH PASSWORD = 'Password', CHECK_POLICY = OFF;
GO

Create user in both database using the login above.

USE TestA
GO
CREATE USER TestUser FROM LOGIN TestUser;
GO
USE TestB
GO
CREATE USER TestUser FROM LOGIN TestUser;
GO

Create table is TestA database.

CREATE TABLE TestA.dbo.t1(id INT);
GO

Create view in TestB database.

USE TestB; 
GO
CREATE VIEW dbo.ViewA
AS
  SELECT id FROM TestA.dbo.t1;
GO

Enabling Cross-database Ownership Chaining

ALTER DATABASE testA SET DB_CHAINING ON;  
ALTER DATABASE testB SET DB_CHAINING ON;  

Grant permission to read from the view.

USE [TestB]
GO
GRANT SELECT ON [dbo].[ViewA] TO [TestUser];
GO

Login with TestUser into SSMS and run this:

USE [TestB]
GO
SELECT * FROM [dbo].[ViewA]
USE [TestA]
GO
SELECT * FROM [dbo].[t1]
GO

You should see all the rows from testA.dbo.ViewA table via the view but get following error message for the select on table t1.

Msg 229, Level 14, State 5, Line 6 The SELECT permission was denied on the object 't1', database 'TestA', schema 'dbo'.