locked
Select permission on View RRS feed

  • Question

  • Hi

    I am using SQL Server 2008 R2, I have created a view in Database A, but the base table is in Database B. I need to give permission(select only) to the user for the View only not to the base table. which is not working.

    I was reading about DB cross DB chain ownership, but our company does not support that due to compliance. There is any other thing I can try that I am missing.

    Thank you so much for any suggestion.


    VR

    Friday, September 19, 2014 12:05 AM

Answers

  • What you propose requires either cross-database ownership chaining or the user to have select permissions in Database B.

    Could you put a view in Database B?  Such a view would have an intact ownership chain to the table without turning on cross-database ownership chaining.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Friday, September 19, 2014 2:33 AM
  • With a view, your options are limited. But if you can use a stored procedure or a multi-statement function, you can use module signing with certificates.

    I describe this in create detail in this article on my web site:
    http://www.sommarskog.se/grantperm.html


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, September 19, 2014 9:38 PM

All replies

  • What you propose requires either cross-database ownership chaining or the user to have select permissions in Database B.

    Could you put a view in Database B?  Such a view would have an intact ownership chain to the table without turning on cross-database ownership chaining.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Friday, September 19, 2014 2:33 AM
  • With a view, your options are limited. But if you can use a stored procedure or a multi-statement function, you can use module signing with certificates.

    I describe this in create detail in this article on my web site:
    http://www.sommarskog.se/grantperm.html


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, September 19, 2014 9:38 PM