Post reply

Problem securing a view

  • December 12, 2011 at 2:44 pm

    #248099

    I have a view that selects data from tables in two separate databases that are on the same SQL instance. I have created a SQL login/user that I only want to have SELECT permission to the aforementioned view. I have granted the login/user SELECT permission to the view, which resides in one of the two databases mentioned above. When logged into SSMS as the SQL Login and issuing a SELECT against the view, I get a "SELECT permission was denied on the object 'TableName', database 'DatabaseName', schema 'dbo'." The object referenced in the error is one of the tables located in the other database; the database that the view does not reside in.

    I thought the whole idea of the view is to not give SELECT permissions to any of the underlying tables referenced in the view. Could I get some help on what I am missing?

  • December 13, 2011 at 3:30 am

    #1420469

    You need to understand Ownership Chains in SQL Server. Here is one example for you (exactly your case).

    Ownership Chains

    http://msdn.microsoft.com/en-us/library/ms188676.aspx

  • December 13, 2011 at 8:28 am

    #1420660

    database permissions do not cross database boundaries by default. You can enable this, but be aware of the security implications.

Viewing 3 posts - 1 through 3 (of 3 total)

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