mercredi 25 février 2015

How to give access to a user to a view, but not to the table the view is using?


I've a user (called myUser) which has db_datareader role on myCustomDatabase.


myCustomDatabase and MSDB are owned by sa user.


I've enable chaining (it's already enabled on MSDB):



ALTER DATABASE myCustomDatabase SET DB_CHAINING ON;


I created a view on myCustomDatabase as following:



CREATE VIEW myCustomDatabase.dbo.myView AS
SELECT run_date FROM msdb.dbo.sysjobhistory


I would like myUser be able to call this view, and not the following query:



SELECT * FROM msdb.dbo.sysjobhistory // Should not work
SELECT * FROM myCustomDatabase.dbo.myView // Should work


With described persmissions I get folowing error:



The SELECT permission was denied on the object 'sysjobhistory', database 'msdb', schema 'dbo'.


Where am I wrong with db chaining?





Aucun commentaire:

Enregistrer un commentaire