Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Kimi86
Yak Posting Veteran
79 Posts |
 Posted - 2013-03-12 : 11:06:32
|
| I have a view which is selecting rows from a table in a different database. I'd like to grant select access to the view, but not direct access to the base table. The view has a where clause restricting the number of rows.Can I grant select to the view and not the base table, or do I need to switch to a stored procedure (not preferred at all). |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-03-12 : 12:15:08
|
| Whether you use a stored proc or a view, in order for the user to be able to retrieve the rows from a table in another database, you will need to do one of two things: a) grant select permission to the user on the table in the remote database (which in turn means that that user must exist in that database as well).b) enable cross-database ownership chaining and make sure that the owner of the view is the same as the owner of the table.A bit more information about cross database ownership chaining is available here: http://msdn.microsoft.com/en-us/library/ms188676%28v=sql.105%29.aspxCross database ownership chaining has security implications, especially so if the two databases are owned by different entities, or if there are highly privileged users in either database who should not have access to data in the other database. |
 |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2013-03-12 : 18:45:13
|
There is actually another way to do this for a Function or SP: Certificates. You don't need cross database ownership chaining and the user does not need access to the other database. It can't be a view though it only works for functions, triggers, and stored procedures. It is not for the faint of heart but DBAs and control boards may appreciate the limited user access.Certificates and signed routines allow a local user to call a function or procedure that depends on access to remote database tables. The local user does not need to even be a user in that remote database. If you want to pursue this here are the basic steps needed to make it work:certificates for cross db access without explicit user permissions to the dependent database or any tables therein------------------------------------------------------------------IN REMOTE DB (database with object to permission by certificates)1. create a certificate 2. backup the certificate to files so we can create the same certificate in another database3. create a user from the certificate (doesn't need to correspond to a login)4. remove the private key from the certificate for added security (optional)5. grant privs to this certificate user to the remote objects that you don't want regular users to access directly------------------------------------------------------------------IN LOCAL DB (database where the regular user will be calling SPs and functions)6. create a certificate from the file backups in step 27. delete the files for security and general clean up reasons (optional)8. "sign" [add signature] the SP/Function that needs permission to remote tables from the certificate9. remove the private key of the signature for added security (optional)------------------------------------------------------------------Assuming the local user has permission to exec the local procedure they can now execute iteven though they don't have permissions themselves to the dependent remote tables or evenhave access to the remote database. Be One with the OptimizerTG |
|
|
|
Kimi86
Yak Posting Veteran
79 Posts |
Posted - 2013-03-13 : 05:40:00
|
| thank for all ideas |
|
|
|
|
|
|
grant READ access to view