I'm working with MSSQL Server Management Studio 2008 and I need to expose a view to a third party for their data reconciliation. I have created the appropriate view but I'm having trouble creating a user and giving that user appropriate permissions to select from the view.
I followed the wizards for creating a login and a user and then added my view in the Securables section with the grant box checked for select. Everything seemed fine but when I logged in as that user and tried to do a "Select * from MyViewName" it told me that the select permission was denied.
I just recreated the user (this time just using SQL instead of the wizard) and explicitly granted select permissions and now it is giving me the error: Msg 916, Level 14, State 1, Line 2
(I don't know why it's trying to access the unrelated database…)
The server principal "username" is not able to access the database "unrelated_db" under the current security context.
I really don't know where to go from here. Again, basically all I need is to create a user that I can give to the third party to have them connect to our database and select from this view.
Best Answer
Please don't use the UI for this. It's a confusing mess.
It sounds to me like what you want is to create a user in a database, for a specific login, who only has permissions to select from one view. So, since you already have the login created:
EDIT here is an example of a script that will lead to the error you mention.
First, create some table in the unrelated_db:
Now create a relatively restricted login:
Now create a database where the view will live, and add the login as a user:
Now create a function that will reference the table in the other database, and a synonym to the other table:
Now create a local table:
Now create a view that references the table, the function and the synonym, and grant
SELECT
tousername
:Now try to execute as
username
and select only the local column from the view:Result:
Now change the view to not reference any external objects, and run the above
SELECT
again, and it works:Short of showing us the scripts for the Payment Details, Account Details and MyView objects, maybe you can let us know if this query returns any results. You can find references to various objects through the catalog view
sys.sql_expression_dependencies
, but this view is not perfect - I believe it depends on all the views being refreshed (in the case where views reference other views, for example, or underlying schema has changed) in order to be accurate.SQL Server isn't just going to try to access
unrelated_db
for the fun of it... there must be some tie to that database from the view you're trying to use. Unfortunately if we can't see the view definition and more details about the objects it touches, all we can do is speculate. The two main things I can think of are synonyms or functions that use three-part names, but seeing the actual scripts will give us a much better idea instead of guessing. :-)You may also want to check
sys.dm_sql_referenced_entities
, however this function returns nothing useful in the example above.