Avatar of Ora_Techie
Ora_Techie asked on

How to restrict access to SQL Server from Enterprise Manager, SQL Server Management Express etc

I am using SQL Server 2000. I want to restrict users from logging on trough Enterprise Manager/SQL Server Management Express etc (thery should be able to logon through application only).

I tried scheduling job whic finds the desired sessions from sysprocesses and kills them. But the problem is as long as the user navigates between databases/tables egc, a new connection is automatically established so practically this restriction is of no use.

Can anyone suggest better method?

Note: We can not make changes in the application.
Microsoft SQL Server

Avatar of undefined
Last Comment
Raja Jegan R

8/22/2022 - Mon
James Murrell

Here are several things you can look at:

1. Do not give the users permission to create procedures, views etc.

2. Use application roles (see sp_setapprole in Books Online)

3. Only allow access to data through stored procedures, and only grant
EXECUTE permissions on the procedures instead of granting permissions on the
tables (this may not be 100% possible if you require dynamic SQL, however)

4. Code your application to set the application name when it connects, then
use APP_NAME() in stored procedures to check the user is using an
'authorized' application (this is easy to fake, though, so it will only stop
an accidental or 'casual' attempt to connect)

5. Use a middle tier to authenticate users and manage connections instead of
allowing them to connect directly to the database server
ASKER
Ora_Techie

@@cs97jjm3
Thanks for your comment. Below are my replies (point to point)
(1) They don't have permissions to create procedures/views. But since they have permissions to modify tables, they can insert/change the data.
(2) We can't change the application since we don't have application code.
(3) This will be a major change IMO; further we don't have application code
(4) Same as (3)
(5) Needs more hardware which we are trying to avoid...
 
Oracle has after logon trigger, does SQL Server has anything similar like that?
 
thanks & regards,
 
Muhammad Riaz
Raja Jegan R

I hope your requirement is to restrict the users to login only through application.
That means all maintenance activities will be carried out using separate login.

So, in the application login grant only those that needs access.
In that way, the application user wont be able to edit or remove your scheduled jobs.

An user with the privilege of db_admin or sysadmin will be able to do jobs changes.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
ASKER
Ora_Techie

@rrjegan17,
Yes, you are right. The requirement is to restrict users to login only throufh application.
I was not talking about scheduled jobs in this context. My problem is:
In order to access the application, we need to create a user in database and grant him/her access on specific tables/views.
Now due to the fact that user exists in database, the user can connect to database (for example using Enterprise Manager) and directly modify the data (for which he/she has grants).
 
Hope this clarifies the situation...
Raja Jegan R

You mean to say that the users who have been given privileges to database objects should not be able to access only through the application and not through query analyzer or Enterprise manager.

If that is the case, then you wont be able to achieve that.
Granting permissions will allow them to access those objects through any mode.
If you block the Query analyzer and Enterprise manager, they can use third party clients and do those modifications.

Kindly revert if I mentioned above differs from your requirement.
ASKER
Ora_Techie

@rrjegan17,
Thats exactly what i need.
<Quote>
If you block the Query analyzer and Enterprise manager...
</Quote>
Could you please let me know how to block these programs? I know users are only using Enterprise Manager (specifically) and Query Analyzer (rarely) .
 
regards,
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Raja Jegan R

Did it once to achieve a domain wide restriction of Windows Media Player in Office Machines..

http://www.wyckedone.net/2005-06-24/controlling-application-access-via-windows-group-policies/

But be careful when you try to achieve this in your domain.
ASKER
Ora_Techie

@rrjegan17
Thanks for the answer. Domain users are using Windows XP; will this work? Is it possible to do it selectively for some specific users instead of whole domain?
 
Thanks again for your help and patience.
ASKER CERTIFIED SOLUTION
Raja Jegan R

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question