Need Help with Views

Question

Need Help with Views

sql2k8

Hall of Fame

Points: 3455
More actions
February 1, 2019 at 10:48 pm

#376936

SQL Server 2014 enterprise edition 64 bit
Windows server 2012 R2 Standard Edition 64 Bit
Database A
TableA
Create Table TableA
(
[ColA] Varchar(10) NOT NULL,
[ColB] [datetime] NOT NULL,
[ColC] [datetime] NOT NULL,
[ColD] [datetime] NOT NULL,
[ColE] [datetime] NOT NULL,
[ColF] [datetime] NOT NULL
) ON [PRIMARY]
Database B
ViewA
Is there a way to create ViewA in Database B based on Table A in Database A.
I don't want to use Cross-Database Ownership Chaining for this.
The UserA in Database B should be running the views in database B only. The userA in Database B should not have access to tables in Database A
Query;
Use DatabaseB
GO
Select * from dbo.vwTableA
Error Message:
Msg 916, Level 14, State 1, Line 4
The server principal UserA is not able to access the database "DatabaseA" under the currrent security context

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 1

sql2k8 - Friday, February 1, 2019 10:48 PM
SQL Server 2014 enterprise edition 64 bit
Windows server 2012 R2 Standard Edition 64 Bit
Database A
TableA
Create Table TableA
(
[ColA] Varchar(10) NOT NULL,
[ColB] [datetime] NOT NULL,
[ColC] [datetime] NOT NULL,
[ColD] [datetime] NOT NULL,
[ColE] [datetime] NOT NULL,
[ColF] [datetime] NOT NULL
) ON [PRIMARY]
Database B
ViewA
Is there a way to create ViewA in Database B based on Table A in Database A.
I don't want to use Cross-Database Ownership Chaining for this.
The UserA in Database B should be running the views in database B only. The userA in Database B should not have access to tables in Database A
Query;
Use DatabaseB
GO
Select * from dbo.vwTableA
Error Message:
Msg 916, Level 14, State 1, Line 4
The server principal UserA is not able to access the database "DatabaseA" under the currrent security context

Suggest you look into using a synonym for this.
😎

Thom A SSC Guru Points: 99133 More actions · Answer 2

Eirikur Eiriksson - Saturday, February 2, 2019 6:57 AM
Suggest you look into using a synonym for this.
😎

How is that going to help with the permissions here Eirikur?

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

sql2k8 Hall of Fame Points: 3455 More actions · Answer 3

Eirikur Eiriksson - Saturday, February 2, 2019 6:57 AM
sql2k8 - Friday, February 1, 2019 10:48 PM
SQL Server 2014 enterprise edition 64 bit
Windows server 2012 R2 Standard Edition 64 Bit
Database A
TableA
Create Table TableA
(
[ColA] Varchar(10) NOT NULL,
[ColB] [datetime] NOT NULL,
[ColC] [datetime] NOT NULL,
[ColD] [datetime] NOT NULL,
[ColE] [datetime] NOT NULL,
[ColF] [datetime] NOT NULL
) ON [PRIMARY]
Database B
ViewA
Is there a way to create ViewA in Database B based on Table A in Database A.
I don't want to use Cross-Database Ownership Chaining for this.
The UserA in Database B should be running the views in database B only. The userA in Database B should not have access to tables in Database A
Query;
Use DatabaseB
GO
Select * from dbo.vwTableA
Error Message:
Msg 916, Level 14, State 1, Line 4
The server principal UserA is not able to access the database "DatabaseA" under the currrent security context
Suggest you look into using a synonym for this.
😎

Let me try that option and get back to you on that.

HappyGeek SSCoach Points: 18729 More actions · Answer 4

Thom A - Saturday, February 2, 2019 7:54 AM
Eirikur Eiriksson - Saturday, February 2, 2019 6:57 AM
Suggest you look into using a synonym for this.
😎
How is that going to help with the permissions here Eirikur?

+1

...

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 5

Thom A - Saturday, February 2, 2019 7:54 AM
Eirikur Eiriksson - Saturday, February 2, 2019 6:57 AM
Suggest you look into using a synonym for this.
😎
How is that going to help with the permissions here Eirikur?

Create a schema and synonym in the destination database for the target table and grant select to the user/group on both the synonym and the target table, makes it transparent and the user/group can only do select.
😎

Thom A SSC Guru Points: 99133 More actions · Answer 6

Eirikur Eiriksson - Sunday, February 3, 2019 6:04 AM
Create a schema and synonym in the destination database for the target table and grant select to the user/group on both the synonym and the target table, makes it transparent and the user/group can only do select.
😎

The problem, however, is that the OP specifically doesn't want to use Cross-database ownership chaining or grant the login any privileges in the other database. Unless my understanding of Synonyms is out of date, you still need the relevant permissions on the destination object when using a synonym.

I've not used them before, but this sounds like the OP might need to use signed objects; not sure if you can have signed views, but a signed Stored Procedure might do the job.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 7

Thom A - Sunday, February 3, 2019 10:25 AM
Eirikur Eiriksson - Sunday, February 3, 2019 6:04 AM
Create a schema and synonym in the destination database for the target table and grant select to the user/group on both the synonym and the target table, makes it transparent and the user/group can only do select.
😎
The problem, however, is that the OP specifically doesn't want to use Cross-database ownership chaining or grant the login any privileges in the other database. Unless my understanding of Synonyms is out of date, you still need the relevant permissions on the destination object when using a synonym.
I've not used them before, but this sounds like the OP might need to use signed objects; not sure if you can have signed views, but a signed Stored Procedure might do the job.

You got a point there Tom, the problem is much simpler though!
😎
No cross-database ownership is needed, only a select on a specific table/view, even if the suspect (read user) can connect to the source database, no further permissions will be required. In extreme cases, one takes further measures but in cases like this one, it is trivial, grant read to the source and synonym it to a schema in the destination. The benefit of using a synonym is simply to avoid four part qualifier apart from the synonym's definition.
Done this hundreds of times where one has to report SaaS clients to the business without exposing the Clients' details, works a charm every time.

Thom A SSC Guru Points: 99133 More actions · Answer 8

Eirikur Eiriksson - Sunday, February 3, 2019 10:38 AM
You got a point there Tom, the problem is much simpler though!
😎
No cross-database ownership is needed, only a select on a specific table/view, even if the suspect (read user) can connect to the source database, no further permissions will be required. In extreme cases, one takes further measures but in cases like this one, it is trivial, grant read to the source and synonym it to a schema in the destination. The benefit of using a synonym is simply to avoid four part qualifier apart from the synonym's definition.
Done this hundreds of times where one has to report SaaS clients to the business without exposing the Clients' details, works a charm every time.

But you seem to be saying again that you need to grant SELECT permissions on the other table to the login/user. The OP stated "The userA in Database B should not have access to tables in Database A". |If we try to following:

USE master;
CREATE DATABASE DatabaseA;
CREATE DATABASE DatabaseB;
GO
CREATE LOGIN TestLogin WITH PASSWORD = 'abc123', CHECK_POLICY=OFF, CHECK_EXPIRATION=OFF, DEFAULT_LANGUAGE=BRITISH;
GO
USE DatabaseA;
GO
CREATE TABLE dbo.Table1 (ID int IDENTITY,
          SomeValue varchar(10));
INSERT INTO dbo.Table1 (SomeValue)
VALUES('asdkjasgbk'),
   ('asdkasjhd');
GO
USE DatabaseB;
GO
CREATE USER TestLogin FOR LOGIN TestLogin;
GO
CREATE VIEW dbo.T1_View AS
   SELECT ID,
     SomeValue
   FROM DatabaseA.dbo.Table1;
GO
CREATE SYNONYM T1 FOR DatabaseA.dbo.Table1;
GO
GRANT SELECT ON T1 TO TestLogin;
GRANT SELECT ON dbo.T1_View TO TestLogin;
GO
EXECUTE AS LOGIN = 'TestLogin';
GO
PRINT 'Try View';
SELECT *
FROM dbo.T1_View
GO
PRINT 'Try Synonym';
SELECT *
FROM T1;
GO
REVERT
GO
--Just create the USER, with no permissions:
USE DatabaseA;
GO
CREATE USER TestLogin FOR LOGIN TestLogin;
GO
USE DatabaseB;
GO
EXECUTE AS LOGIN = 'TestLogin';
GO
PRINT 'Try View Again';
SELECT *
FROM dbo.T1_View
GO
PRINT 'Try Synonym Again';
SELECT *
FROM T1;
GO
REVERT
GO
USE master;
GO
DROP DATABASE DatabaseA;
DROP DATABASE DatabaseB;

DROP LOGIN TestLogin;

All 4 attempts fail with an error, The server principal "TestLogin" is not able to access the database "DatabaseA" under the current security context. and The SELECT permission was denied on the object 'Table1', database 'DatabaseA', schema 'dbo'. respectively.

This is, therefore, where I'm confused, how can one make a synonym "work", without providing permissions on the object it's referencing to (in this case creating TestLogin in DatabaseA and Granting it SELECT on Table1) or using Cross-database ownership chaining? Perhaps I am missing something, but even the documentation states:

You do not need permission on the base object to successfully compile the CREATE SYNONYM statement, because all permission checking on the base object is deferred until run time.

This says to me that to use a synonym you do need permissions on the base object at run time.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 9

Thom A - Monday, February 4, 2019 2:07 AM
Eirikur Eiriksson - Sunday, February 3, 2019 10:38 AM
You got a point there Tom, the problem is much simpler though!
😎
No cross-database ownership is needed, only a select on a specific table/view, even if the suspect (read user) can connect to the source database, no further permissions will be required. In extreme cases, one takes further measures but in cases like this one, it is trivial, grant read to the source and synonym it to a schema in the destination. The benefit of using a synonym is simply to avoid four part qualifier apart from the synonym's definition.
Done this hundreds of times where one has to report SaaS clients to the business without exposing the Clients' details, works a charm every time.
But you seem to be saying again that you need to grant SELECT permissions on the other table to the login/user. The OP stated "The userA in Database B should not have access to tables in Database A". |If we try to following:
USE master;
CREATE DATABASE DatabaseA;
CREATE DATABASE DatabaseB;
GO
CREATE LOGIN TestLogin WITH PASSWORD = 'abc123', CHECK_POLICY=OFF, CHECK_EXPIRATION=OFF, DEFAULT_LANGUAGE=BRITISH;
GO
USE DatabaseA;
GO
CREATE TABLE dbo.Table1 (ID int IDENTITY,
SomeValue varchar(10));
INSERT INTO dbo.Table1 (SomeValue)
VALUES('asdkjasgbk'),
('asdkasjhd');
GO
USE DatabaseB;
GO
CREATE USER TestLogin FOR LOGIN TestLogin;
GO
CREATE VIEW dbo.T1_View AS
SELECT ID,
SomeValue
FROM DatabaseA.dbo.Table1;
GO
CREATE SYNONYM T1 FOR DatabaseA.dbo.Table1;
GO
GRANT SELECT ON T1 TO TestLogin;
GRANT SELECT ON dbo.T1_View TO TestLogin;
GO
EXECUTE AS LOGIN = 'TestLogin';
GO
PRINT 'Try View';
SELECT *
FROM dbo.T1_View
GO
PRINT 'Try Synonym';
SELECT *
FROM T1;
GO
REVERT
GO
--Just create the USER, with no permissions:
USE DatabaseA;
GO
CREATE USER TestLogin FOR LOGIN TestLogin;
GO
USE DatabaseB;
GO
EXECUTE AS LOGIN = 'TestLogin';
GO
PRINT 'Try View Again';
SELECT *
FROM dbo.T1_View
GO
PRINT 'Try Synonym Again';
SELECT *
FROM T1;
GO
REVERT
GO
USE master;
GO
DROP DATABASE DatabaseA;
DROP DATABASE DatabaseB;
DROP LOGIN TestLogin;
All 4 attempts fail with an error, The server principal "TestLogin" is not able to access the database "DatabaseA" under the current security context. and The SELECT permission was denied on the object 'Table1', database 'DatabaseA', schema 'dbo'. respectively.
This is, therefore, where I'm confused, how can one make a synonym "work", without providing permissions on the object it's referencing to (in this case creating TestLogin in DatabaseA and Granting it SELECT on Table1) or using Cross-database ownership chaining? Perhaps I am missing something, but even the documentation states:
You do not need permission on the base object to successfully compile the CREATE SYNONYM statement, because all permission checking on the base object is deferred until run time.
This says to me that to use a synonym you do need permissions on the base object at run time.

The user/group will have a select permission on the source table, does not matter if they connect to database A or database B, they can only select from the table and in the source database, they can only see that table. Unless one starts to implement a stored procedure to encapsulate (execute as) the object, this is the most straight forward method.
😎

As there is no need for using a four part identifier, this solution "contains" things 😉

Thom A SSC Guru Points: 99133 More actions · Answer 10

Eirikur Eiriksson - Monday, February 4, 2019 7:41 AM
The user/group will have a select permission on the source table, does not matter if they connect to database A or database B, they can only select from the table and in the source database, they can only see that table. Unless one starts to implement a stored procedure to encapsulate (execute as) the object, this is the most straight forward method.
😎
As there is no need for using a four part identifier, this solution "contains" things 😉

Not gunna lie, you lost me there Eirikur. :hehe: Perhaps some T-SQL will speak "louder" than words; could you show an example please?

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

sql2k8 Hall of Fame Points: 3455 More actions · Answer 11

Eirikur Eiriksson - Sunday, February 3, 2019 6:04 AM
Thom A - Saturday, February 2, 2019 7:54 AM
Eirikur Eiriksson - Saturday, February 2, 2019 6:57 AM
Suggest you look into using a synonym for this.
😎
How is that going to help with the permissions here Eirikur?
Create a schema and synonym in the destination database for the target table and grant select to the user/group on both the synonym and the target table, makes it transparent and the user/group can only do select.
😎

I created schema and synonym in the destination database B

Eirikur Eiriksson - Sunday, February 3, 2019 6:04 AM
Thom A - Saturday, February 2, 2019 7:54 AM
Eirikur Eiriksson - Saturday, February 2, 2019 6:57 AM
Suggest you look into using a synonym for this.
😎
How is that going to help with the permissions here Eirikur?
Create a schema and synonym in the destination database for the target table and grant select to the user/group on both the synonym and the target table, makes it transparent and the user/group can only do select.
😎

I created a schema C and TestSynonym in the destination database B. I had created views in database B referring to the schema

Eirikur Eiriksson - Sunday, February 3, 2019 6:04 AM
Thom A - Saturday, February 2, 2019 7:54 AM
Eirikur Eiriksson - Saturday, February 2, 2019 6:57 AM
Suggest you look into using a synonym for this.
😎
How is that going to help with the permissions here Eirikur?
Create a schema and synonym in the destination database for the target table and grant select to the user/group on both the synonym and the target table, makes it transparent and the user/group can only do select.
😎

Eirikur Eiriksson - Sunday, February 3, 2019 6:04 AM

Thom A - Saturday, February 2, 2019 7:54 AM
Eirikur Eiriksson - Saturday, February 2, 2019 6:57 AM
Suggest you look into using a synonym for this.
😎
How is that going to help with the permissions here Eirikur?

Create a schema and synonym in the destination database for the target table and grant select to the user/group on both the synonym and the target table, makes it transparent and the user/group can only do select.
😎

As per your direction I got it working

what i tried

I created a schema TestSchema and TestSynonym in the destination database B. I had created view ViewA in database B referring to the schema TestSchema pointing to the table A in database A.
I had granted select permission on TestSynonym to the User A in database. After that i tried to grant select permissions on TableA in database A.

After that i tried to run the run the view ViewA in database B. It works.

But if I am giving read access directly on TableA in database A. I don't need Synonyms in Database B to create and let the user A use it in Database B. The read permissions on TableA in database A will itself be sufficient to run the viewA or directly read from the tableA.

Database A is very regulated database. Users should not have direct access to it. Indirect access is okay as long thet can't see the tables directly in DatabaseA

When i gave select permissions to the user A on TableA in database A. The user is directly able to see the TableA in database A. If he can see that he can query it directly.That is against the requirement.