I want to grant access on the below view for an end user so that he connect to our SQL server and retrieve data. The view looks like the below
CREATE VIEW DB1.[dbo].[View1]
AS
-- For brevity, I made it as simple statement.
SELECT *
From DB2.dbo.table2
GO
For the above view, it looks like I have to grant select and connect permission for the DB1. [dbo].[View1] as well as DB2.dbo.table2.
1. Is my understanding correct?
2. I want the user to access only DB1. [dbo].[View1] and not the underlying tables. Is there a way to grant access only on the view and execute the statement on a different security context so that the user can’t access DB2.dbo.table2 directly?
3. When the user uses SQL Server Management Studio to connect to SQL server, he is able to connect and select DB2.dbo.table2 directly. Is there any way to restrict user from viewing and executing select statement on DB2 database from SQL Server Management Studio
Thanks in advance for your help
With regards
Ganesh
Because you are crossing databases, regular ownership chaining doesn’t apply. While it is possible to use cross-database ownership chaining (CDOC) for this scenario, I strongly discourage using it as enabling CDOC also enables some escalation of privileges opportunities.
I wrote an article in my blog that describes how to use digital signatures as an alternative, and it probably will be useful in your scenario:http://blogs.msdn.com/raulga/archive/2006/10/30/using-a-digital-signature-as-a-secondary-identity-to-replace-cross-database-ownership-chaining.aspx
NOTE: It is not possible to sign views directly, but it is possible to sign SP and multi-statement user-defined functions.
Also notice that using digital signatures it is possible to grant access to the target DB (DB2) via the signature without granting explicit CONNEC permission to the database on any other principal.
For the last part of your question; it is not possible to restrict actions based on the application, we have a separate discussion on this topic in the following link: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=790497&SiteID=1
I hope this information helps, let us know if you have any additional questions .
Thanks a lot,
-Raul Garcia
SDE/T
SQL Server Engine
|||Thanks a lot for your help. Does the digital signature concept applicable to view also?|||
No, unfortunately views cannot be signed. An alternative is to create a multistatement table-valued function (See BOL for details: http://msdn2.microsoft.com/en-us/library/ms186755.aspx) instead of a view, and you can insert into the return value of the view the same content the view would return.
I hope this helps,
-Raul Garcia
SDE/T
SQL Server Engine
