<div>
Here are several things you can look at:<br />
<br />
1. Do not give the users permission to create procedures, views etc.<br />
<br />
2. Use application roles (see sp_setapprole in Books Online)<br />
<br />
3. Only allow access to data through stored procedures, and only grant<br />
EXECUTE permissions on the procedures instead of granting permissions on the<br />
tables (this may not be 100% possible if you require dynamic SQL, however)<br />
<br />
4. Code your application to set the application name when it connects, then<br />
use APP_NAME() in stored procedures to check the user is using an<br />
'authorized' application (this is easy to fake, though, so it will only stop<br />
an accidental or 'casual' attempt to connect)<br />
<br />
5. Use a middle tier to authenticate users and manage connections instead of<br />
allowing them to connect directly to the database server
</div>


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

</div>


<div>
I hope your requirement is to restrict the users to login only through application.<br />
That means all maintenance activities will be carried out using separate login.<br />
<br />
So, in the application login grant only those that needs access.<br />
In that way, the application user wont be able to edit or remove your scheduled jobs.<br />
<br />
An user with the privilege of db_admin or sysadmin will be able to do jobs changes.<br />

</div>


<div>
@rrjegan17,<br />
Yes, you are right. The requirement is to restrict users to login only throufh application.<br />
I was not talking about scheduled jobs in this context. My problem is:<br />
In order to access the application, we need to create a user in database and grant him/her access on specific tables/views.<br />
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).<br />
&nbsp;<br />
Hope this clarifies the situation...<br />

</div>


<div>
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.<br />
<br />
If that is the case, then you wont be able to achieve that.<br />
Granting permissions will allow them to access those objects through any mode.<br />
If you block the Query analyzer and Enterprise manager, they can use third party clients and do those modifications.<br />
<br />
Kindly revert if I mentioned above differs from your requirement.
</div>


<div>
@rrjegan17,<br />
Thats exactly what i need.<br />
&lt;Quote&gt;<br />
If you block the Query analyzer and Enterprise manager...<br />
&lt;/Quote&gt;<br />
Could you please let me know how to block these programs? I know users are only using Enterprise Manager (specifically) and Query Analyzer (rarely) .<br />
&nbsp;<br />
regards,<br />

</div>


<div>
Did it once to achieve a domain wide restriction of Windows Media Player in Office Machines..<br />
<br />
<a href="http://www.wyckedone.net/2005-06-24/controlling-application-access-via-windows-group-policies/" rel="ugc">http://www.wyckedone.net/2005-06-24/controlling-application-access-via-windows-group-policies/</a><br />
<br />
But be careful when you try to achieve this in your domain.
</div>


<div>
@rrjegan17<br />
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?<br />
&nbsp;<br />
Thanks again for your help and patience.<br />

</div>


<div>
<div class="content wysiwyg-content">
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).<br />
<br />
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.<br />
<br />
Can anyone suggest better method?<br />
<br />
Note: We can not make changes in the application.
</div>
</div>


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.

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

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.