Re: deny users from accessing server from Management Studio

Audrey Ng (odd26uk@xxxxxxxxxxx) writes:
I have the same problem. Through an MS-Access application (with back-end
SQL 2005), users can select, insert and update. However, I don't want
them to have the same ability in Management Studio.

Erland, you mentioned

"There are ways to implement application to avoid this, for instance
using stored procedures. There are also application roles, and you can
use proxy logins."

Can you elaborate on what type of stored procedures?

No special type. The point is that you do not give users right to access
the tables directly. Instead you write stored procedures for the task
and grant users right to execute the procedures. The important point is
that the procedures must be owned by the same user that also owns the table.
Commonly all objects in a database are owner by dbo.

Note that if the users are able to find the stored procedures, they can
still run them from Mgmt Studio.

I also read up on application roles and it seems that that may be be the
way to go.

Keep in mind that application roles can never be safe in a two-tier
application. The application must somehow find the password for the
application role and send it to SQL Server. And if the application can
find it, the user can find it.

In fact, what are some of the best practices in this situation where
users can select, insert and update in one application BUT does have the
ability in other applications?

I think the safest way is to use a three-tier application with a proxy
login. That is, the middle tier connects to SQL Server with a proxy login,
and the users do not have any direct access to SQL Server. If it's
necessary to know who's who, this can be handled with EXECUTE AS or
context info.

I have an article on my web sites where I discuss these topics in more
detail, you may be interested: http://www.sommarskog.se/grantperm.html.



--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.

Relevant Pages

  • Extreme performance issues (SQL Server 2000/ADO.NET/C#)
    ... This process runs very quickly if run through Query ... same exact stored procedures and views, run in the same exact order, through ... system that runs SQL Server (a 4-cpu Xeons system with 2gigs of physical ... When I execute these steps manually through query analyser,, ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Querying on dates in dd/mm/yyyy hh/mm/ss format using Access2002 And SQL Server 7
    ... For parsing a string to a date/time, the 103 format doesn't limits the user ... > which is used as the row source for the results form). ... > other solutions assuming I have to use SQL Server 7? ... > I don't understand how I can use parameterized stored procedures to solve ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Help understanding Stored proc Level Secuirty?
    ... Jasper Smith (SQL Server MVP) ... I set permissions to my Stored Procedures, ... Access to stored procs fail ...
    (microsoft.public.sqlserver.security)
  • Re: Limiting views on data in a table
    ... returns the Windows login the user uses to connect to SQL Server. ... > The way I was implementing this was to use Windows Auth with a Windows ... > covered by which salespeople, ... > with it via the stored procedures. ...
    (microsoft.public.sqlserver.security)
  • Re: Scripting T-SQL CREATE Statements
    ... a backup of user-defined SQL Server objects. ... I am having a hard time finding the T-SQL functions and system stored procedures used to return the scripted ... like when you right-click an object and select Script Object to New Window as>> Create. ... I am trying to get it much like the instnwnd.sql file that comes with the .NET Framework SDK v1.1. ...
    (microsoft.public.sqlserver.programming)