Restricting schema while connecting to a SQL DB

Question

 Hello Team,

I'm sure you would have come across kind of my bellow situation. Can you give me some idea/tip on how to handle this.

I have an user in my sql system (userA) and having access to 2 schemas (Schema1, Schema2) in a DB. Say for example, schema1 has 50 tables and schema2 has another 50 tables. Now if the user tries to connect to the db from power pivot is there any way he can choose which schema he wants to look and query on, instead of showing all the tables to which he has access..

Thank you.

Arvind

---

Arvind

Answer 1

In the  Table Import wizard of Powerpivot, after giving connection details, he can choose "select from a list of tables and views to choose the data to Import" and in the third screen, he can sort the tables by schema names and he can easily choose only the tables, belonging to a particular schema for his powerpivot data model.

HTH,

Venkat

---

Venkataraman R Be Blessed by Divine

Answer 2

Hi Venkat,

But still the user will see the whole list of tables in the 3rd screen. My problem is, I'm having nearly 800 tables across 7 schema's and certain end user's (sql user account) has access to all the schema's. So, in this case if they want to do a reporting using power pivot on the tables present in a single schema, I'm not seeing an option to view those tables alone. Basically, I'm trying to pass the schema information in the connection string (or a work around for this) so that my list of tables/views from which I can see,pick and choose is narrowed down.

Thank you. 

---

Arvind

Answer 3

Can't he choose "Write a query that will specify the data to import"?

---

-- This posting is provided "AS IS" with no warranties, and confers no rights

Answer 4

My end users are not that technical.

---

Arvind

Answer 5

Hi Arvind,

Currently, there is no build-in feature to let the user tries to choose which schema he wants to look and query on, thanks for your understanding.

---

Challen Fu

TechNet Community Support

Answer 6

please follow the link given here for your reference. May it help you.

http://docs.sqlalchemy.org/en/latest/core/schema.html