Query data from another database

Question

Query data from another database

xxxxxxxxxxxxxx

SSC Rookie

Points: 41
More actions
March 11, 2019 at 6:19 am

#377669

Hi,
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.
Situation
Database A
User1 has rights to do anything on this database
ALTER view Mydata
SELECT * FROM DatabaseB.dbo.MyTable
GO
If the user User1 doe do a Select * FROM DatabaseA.dbo.MyData he/she should get the data from DatabaseB but as User2
Database B
User1 does not have rights to do something
User 2 has all the rights
MyTable
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

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 1

This is possible, but it takes a bit of reading and understanding to get there. I have an article on my web site which discusses the option in details, but i will take you some time to get to the end: Packaging Permissions in Stored Procedures.

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

xxxxxxxxxxxxxx SSC Rookie Points: 41 More actions · Answer 2

Erland Sommarskog - Monday, March 11, 2019 6:27 AM
This is possible, but it takes a bit of reading and understanding to get there. I have an article on my web site which discusses the option in details, but i will take you some time to get to the end: Packaging Permissions in Stored Procedures.

Also views are involved, is that also described is you article.

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 3

If the views goes goes cross-database, that is you have views in database A that accesses tables in database B, and you want users to be able to access the views directly, without calling stored procedures or multi-statement functions, the users will need to have access to at least the database. If you have the access to the views in stored procedures, this is no different from access to tables.

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

xxxxxxxxxxxxxx SSC Rookie Points: 41 More actions · Answer 4

Erland Sommarskog - Monday, March 11, 2019 6:42 AM
If the views goes goes cross-database, that is you have views in database A that accesses tables in database B, and you want users to be able to access the views directly, without calling stored procedures or multi-statement functions, the users will need to have access to at least the database. If you have the access to the views in stored procedures, this is no different from access to tables.

I understand but isn't there a possibility to do a sort of mapping of all users of database A to another user who has the rights an database B when executing stored procedures, or perform selects from a view or function.
So that every query going from database A to database B is always as a specific user. As there is the EXCUTE AS but that does not work for views.

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 5

I'm at a client and with limited time that I can help users in forum. And I did write this article so that I don't have to say the same thing over and over again. So I kindly refer you to my article.

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]