Restrict the users to join two SQL views

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

Hi All,

We were facing the performance issue in SQL and later identified that it is because of the users running customer queries. The users are having restricted access on the DB and they can only run the SQL views.

Later on analysing we noticed that users are joining the multiple views and making it a big query and causing performance issue.

Now, we wanted to restrict the users to run only the views and should not be able to join the views. Can you please share if you have any ideas on achieving this.

Thanks in advance.
Regards
Prashant

What I have tried:

1. Googled to find if we can restrict the access to the users but understood that there is no way to restrict the business.

2. Checked with other people if they had come across same situation but no luck.

Posted 19-Dec-16 11:09am

Prashant Bangaluru

Updated 19-Dec-16 12:20pm

Add a Solution

Comments

Afzaal Ahmad Zeeshan 19-Dec-16 17:51pm

1) Upgrade your machines, that is better option.

2) Create the views for them, then revoke their access to define new queries.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Answer 1 · 2016-12-19T12:20:00

Solution 1

In general combining multiple views in a single query is difficult for the optimizer especially if the conditions are used from outside the views.

If the users need access to both views, then I'm afraid you cannot restrict how the views are used (joined or not). What you should ensure is:
- the joining columns in both views are properly indexed
- commonly used filter conditions are used
- ensure that the ad-hoc queries go through some kind of logical validation in order to prevent erroneous queries
- consider using fixed condition sets, perhaps table valued functions implementing them
- if possible, don't use view, instead use the underlying tables.

Posted 19-Dec-16 12:20pm

Wendelius

Comments

Prashant Bangaluru 19-Dec-16 19:19pm

Thank you for the reply . Looks like we can't restrict the joining of 2 sql views.

Wendelius 20-Dec-16 0:34am

That's right. Your question sounds like your users are running data analysis queries in a transactional database (OLTP). If this is the case I would recommend building a separate database for reporting and transfer the data over there in a format easier to report.

Have a look at Data warehouse - Wikipedia[^] and Extract, transform, load - Wikipedia[^]

Prashant Bangaluru 21-Dec-16 17:37pm

Yes, the users are running the data analysis query and causing the load on the server.
I would definitely think of this. My other plan is to load the one day old data into UAT system and request them to use the system.