CODEWITHSUNDEEP
sqlsql-server-2008
jnoel10
jnoel10

Reputation: 295

SQL is it possible to grant permission to the view and not the user to access other db?

Hello and thank you for your time,

I am using sql 2008 I created a db that has nothing of relevance in. Here I want to add views that access other databases. I will then add a user to the db with no relevance and give access to the view.

My obvious problem is that when the user tries to run the view, errors occur where they do not have permission to view the information. (when I use the admin type user, i can access the view and see the results no problem)

Can I create permissions to the view to all SELECT on the two seperate db's so the user can see the results. Or does the user require the read permissions on all the tables queried.

Thank you again.

This is what I tried (and many others but it did not work)

USE no-relevance-server
GRANT REFERENCES ON OBJECT[important-server::dbo.table] TO [the view]

Upvotes: 0

Views: 3343

Answers (1)

podiluska
podiluska

Reputation: 51494

Your issue here is one of cross database ownership chains

You can make it work by following the instructions at http://support.microsoft.com/kb/810474

EXEC sp_configure 'Cross DB Ownership Chaining', '1'; RECONFIGURE 
EXEC sp_dboption 'YourDatabase', 'db chaining', 'true'

But be sure to read up about Cross Database Ownership Chaining and the associated risks.

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

Upvotes: 2

Related Questions