Cross Data Base Permissions on Views

Cross Data Base Permissions

•  Let’s assume we have 2 databases Database_A & Database_B

o   We have a table dbo.Employee on Database_A

• Now create view on Database_B for table dbo.Employee which is in Database_A like below

Use Database_B

go

CREATE View Employee_View

as

Select * from Database_A.dbo.Employee

•  Assume we have user called ‘webuser’ now we have to grant select permission for user on view dbo.Employee_View, At the same time this user shouldn’t have any access on base table i.e., dbo.Employee on Database_A
•  Now create user called ‘webuser’ on Database_B then grant SELECT permission to user on view.

use Database_B

go

create user webuser for login webuser

•  Now grant SELECT PERMISSION TO user ‘webuser’ on view ‘dbo.Employee_View’

USE Database_B

GO

GRANT SELECT ON [dbo].[Employee_View] TO [webuser]

• Now let’s test if user ‘webuser’ can able to select the data on view
• Login to the server with user ‘webuser’ and select the view, see below screen shot.

• User is not able to access the view and getting below error

Msg 916, Level 14, State 1, Line 2

The server principal "webuser" is not able to access the database "Database_A" under the current security context.

• Now in order to fix this error we need to create user on ‘Database_A’ but don’t grant any permissions to user on this db to restrict him not to access the base table i.e., dbo.Employee.

• Now let’s try to select the view on Database_B

• This time also user not able to get the data, getting the below error.

Msg 229, Level 14, State 5, Line 2

The SELECT permission was denied on the object 'Employee', database 'Database_A', schema 'dbo'.

• In order to fix the issue we need to enable the “cross db ownership chaining” on server

        ALTER DATABASE Database_A SET DB_CHAINING ON;

        ALTER DATABASE Database_B SET DB_CHAINING ON

• Now let’s try to select the view on Database_B database

• SUCCESSES! , now user ‘webuser’ can able to access the view ‘dbo.Employee_View’ on     Database_B
• Now let’s try to test if the user ‘webuser’ can able to access the base table dbo.Employee on Database ‘Database_A’

• In above screen shot we can clearly see that ‘webuser’ cannot able to see the table     dbo.Employee on Database_A.
• It means webuser is having access on view on database Database_B but he doesn’t have any access on base table dbo.Employee on database Database_A.

Note: 1. The user should be created in both the databases.

      2. Make sure shouldn’t grant any permission to the user on Base Table Database.

      3. Make sure to enable Cross Database Chaining on both the databases