October 24, 2018 at 2:26 pm
Hello,
We are attempting to give permissions to a vendor via a SQL login to be able to see and select from a couple of views on "DatabaseABC". DatabaseABC uses SQL views to access data from DatabaseXYZ, which is on the same SQL instance. We don't want the vendor to be able to log in and see DatabaseABC, or select from its tables directly; we only want them to be able to select from the views on DatabaseXYZ. Is this possible?
Any assistance is greatly appreciated!
October 24, 2018 at 3:47 pm
tarr94 - Wednesday, October 24, 2018 2:26 PMHello,
We are attempting to give permissions to a vendor via a SQL login to be able to see and select from a couple of views on "DatabaseABC". DatabaseABC uses linked servers to access data from DatabaseXYZ, which is on the same SQL instance. We don't want the vendor to be able to log in and see DatabaseABC, or select from its tables directly; we only want them to be able to select from the views on DatabaseXYZ. Is this possible?Any assistance is greatly appreciated!
I think options may depend on how the security is set on the linked server. Which option are you using?
Sue
October 25, 2018 at 11:15 am
Hi Sue,
I apologize for using the term "linked servers," that is inaccurate in this case. This issue has nothing to do with linked servers.
DatabaseABC and DatabaseXYZ reside on the same SQL instance. I want a login account to be able to connect to this SQL instance and see/run the SQL views on DatabaseABC, but even though these views are pulling data from DatabaseXYZ, I don't want to allow the account to see DatabaseXYZ or have direct access to its tables. Is this possible?
I've updated the topic to correct my error.
October 26, 2018 at 1:57 pm
Is what I'm asking not possible in SQL Server?
October 26, 2018 at 2:48 pm
tarr94 - Friday, October 26, 2018 1:57 PMIs what I'm asking not possible in SQL Server?
Wrap the select in a stored procedure and use execute as (or sign the stored procedure) using a login with the necessary permissions.
Additionally, if you don't want the vendor to even see the other database and you have not made any changes to public permissions at the server level, you need to explicitly deny that vendor login the view any database permissions.
Sue
October 30, 2018 at 11:21 am
Unfortunately, for what we're trying to do, these have to remain SQL views, not stored procedures.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply