deny users from accessing server from Management Studio

deny users from accessing server from Management Studio

Post by RGFydGhTaW » Fri, 26 Oct 2007 20:57:02


hello,
i've a question.
I need to grant access to some users, cos' one application uses windows
integrated security but, and this is a big BUT, i don't want them to access
to sql server from management studio.... Is there any way to stop them from
accessing the server in this way? i want them to access only from some X
application.
the same question goes for sql server 2000 if there is some answer too.

thanks in advance!!

Edward.
 
 
 

deny users from accessing server from Management Studio

Post by Uri Diman » Fri, 26 Oct 2007 21:39:54

Take a look at VIEW ANY DATABASE and VIEW DEFINITION commands in the BOL

 
 
 

deny users from accessing server from Management Studio

Post by Erland Som » Sat, 27 Oct 2007 07:01:28

DarthSidious ( XXXX@XXXXX.COM ) writes:

No, not without changing the application. If the application requires
users to have SELECT access to tables, they have SELECT acecss from
Mgmt Studio as well.

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.



--
Erland Sommarskog, SQL Server MVP, XXXX@XXXXX.COM

Books Online for SQL Server 2005 at
http://www.yqcomputer.com/
Books Online for SQL Server 2000 at
http://www.yqcomputer.com/
 
 
 

deny users from accessing server from Management Studio

Post by Audrey N » Sat, 10 Nov 2007 12:53:26

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?

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

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?



*** Sent via Developersdex http://www.yqcomputer.com/ ***
 
 
 

deny users from accessing server from Management Studio

Post by Erland Som » Sat, 10 Nov 2007 20:56:50

Audrey Ng ( XXXX@XXXXX.COM ) writes:

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.


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.


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.yqcomputer.com/



--
Erland Sommarskog, SQL Server MVP, XXXX@XXXXX.COM

Books Online for SQL Server 2005 at
http://www.yqcomputer.com/
Books Online for SQL Server 2000 at
http://www.yqcomputer.com/
 
 
 

deny users from accessing server from Management Studio

Post by Audrey N » Sat, 10 Nov 2007 22:54:27


ok, here's another alternative. So right now, the user can log in to the
MS-ACCESS application using Windows Authentication. How about if I
create a SQL Login for that same user with only datareader rights, so
when they connect to the database via Mgmt Studio, they will select SQL
Authentication and use the SQL Login assigned to them.

HOWEVER, I know when I try to connect to the server, it gives me the
option of Windows Authentication or SQL Authentication, is there anyway
that I can turn off/disable Windows Authentication option?

Please advise?


*** Sent via Developersdex http://www.yqcomputer.com/ ***
 
 
 

deny users from accessing server from Management Studio

Post by Erland Som » Sun, 11 Nov 2007 07:28:36

Audrey Ng ( XXXX@XXXXX.COM ) writes:

No, you cannot turn off Windows authentication, neither in SQL Server,
nor in Management Studio.

But you can of course drop the Windows login for the user from SQL Server,
but I am not sure that I see the point.

One thing you can do is install a logon trigger (available only in SQL 2005,
and I don't think well documented) that checks the application name, and
on basis of that accepts the login or denies access. This could stop
accidental attempts from Management Studio, but a skilled user will know
how to work around it.

--
Erland Sommarskog, SQL Server MVP, XXXX@XXXXX.COM

Books Online for SQL Server 2005 at
http://www.yqcomputer.com/
Books Online for SQL Server 2000 at
http://www.yqcomputer.com/