- Make sure
DB_CHAINING
is ON
.
- Map TestUser Login to DB1 and DB2.
- Grant
SELECT
on V2.
- Don't grant
SELECT
on V1.
See Ownership Chains in the documentation.
Basically, what it does is that, because V1 and V2 has the same owner (that's mandatory) and because DB_CHAINING
is ON
, when TestUser will SELECT
V2, permissions will not be checked on V1, so you don't need to grant it any permission.
TestUser will be able to retrieve data from V2 but SELECT
on V1 will be denied.
Double-check that DB_CHAINING
is ON
by executing:
SELECT name, is_db_chaining_on
FROM sys.databases;
If it's not the case, set it with:
ALTER DATABASE DBX SET DB_CHAINING ON
...and make sure both views have the same owner.
You can try following code to validate that it's working:
Connect as sysadmin and create Databases DB1 and DB2
USE master
GO
CREATE DATABASE DB1;
GO
CREATE DATABASE DB2;
GO
in DB1:
Create a sample table TB1
Insert a value in TB1 table (this is only because I dont like empty result set)
Create a sample view selecting all rows in TB1 table
USE DB1
GO
CREATE TABLE TB1 (id int);
GO
INSERT INTO TB1 VALUES(42);
GO
CREATE VIEW V1 AS SELECT * FROM TB1;
GO
in DB2:
Create a sample view V2 selecting all rows in DB1.dbo.V1 view
USE DB2
GO
CREATE VIEW V2 AS SELECT * FROM DB1.dbo.V1;
GO
Create TestUser Login
USE [master]
GO
CREATE LOGIN [TestUser] WITH PASSWORD=N'password', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
in DB1:
Create TestUser User mapped to TestUser Login
USE [DB1]
GO
CREATE USER [TestUser] FOR LOGIN [TestUser]
GO
in DB2:
Create TestUser User mapped to TestUser Login
Grant SELECT on V2 view to TestUser user
USE [DB2]
GO
CREATE USER [TestUser] FOR LOGIN [TestUser]
GO
GRANT SELECT ON V2 TO TestUser;
Connect with TestUser Login
In DB2:
Try to select from V2
USE DB2
GO
SELECT * FROM V2;
This should fail with error message: The SELECT permission was denied on the object 'V1', database 'DB1', schema 'dbo'.
Connect as sysadmin
Enable DB_CHAINING on DB1
USE master
GO
ALTER DATABASE DB1 SET DB_CHAINING ON;
GO
Enable DB_CHAINING on DB2
ALTER DATABASE DB2 SET DB_CHAINING ON;
GO
Connect with TestUser Login
In DB2:
Try to Select From V2 again, that time it should succeed
USE DB2
GO
SELECT * FROM V2;
In DB1:
Try to Select From V1
USE DB1
GO
SELECT * FROM V1;
GO
As we didnt grant TestUser any permission this will fail with error message: The SELECT permission was denied on the object 'V1', database 'DB1', schema 'dbo'.
Best Answer
You need to create the login on the DB2 server. Use Robert's script so that it grabs the sid and password, well if it's a SQL account that is.
Then grant the permission on DB1. That permission will auto-magically get copied over to DB2 due to the AG.
There is no way to just grant permission on DB2. It has to be done on DB1. You can control access by disabling the login on DB1 though.