I have several databases but I only want to give a user the rights to do something on one database.
In the database the user has rights he can select from a view, a stored procedure etc...
But in that view or stored procedure there is a query which gets data from another database where the user does not have rights.
User1 has rights to do anything on this database
ALTER view Mydata
SELECT * FROM DatabaseB.dbo.MyTable
If the user User1 doe do a Select * FROM DatabaseA.dbo.MyData he/she should get the data from DatabaseB but as User2
User1 does not have rights to do something
User 2 has all the rights
In fact any user from database A should be translated to User2 when getting data from database B
How must I do this in SQL 2008