PLEASE HELP: Force SQL Login only using SQL SERVER Autherntication.

Hi SQL Gurus,
I am still not a master in SQL Server.
How can we restrict user from login using "WINDOWS AUTHENTICATION".
We don't want anyone who has the server windows login to access SQL Server.
We have seen some users logged in to the database server, when they should
not have access.
Please help me how to restrict them accessing the server.
Thanks for any help or suggestion you can provide.
Mark.Hi
If the users have not directly been granted a login, then they probably have
been granted logins through a windows group. Make sure that they are not
members of a group they should not be in or a group with a wide membership
has been granted a login. You can use sp_denylogin to deny access to a given
login.
John
"Mark" <Mark@.nospaml.com> wrote in message
news:%236TpQHZmIHA.2268@.TK2MSFTNGP02.phx.gbl...
> Hi SQL Gurus,
> I am still not a master in SQL Server.
> How can we restrict user from login using "WINDOWS AUTHENTICATION".
> We don't want anyone who has the server windows login to access SQL
> Server.
> We have seen some users logged in to the database server, when they should
> not have access.
> Please help me how to restrict them accessing the server.
> Thanks for any help or suggestion you can provide.
> Mark.
>|||> How can we restrict user from login using "WINDOWS AUTHENTICATION".
Well, you didn't bother to mention which version of SQL Server you are
using... if you are using SQL Server 2005, you could put the sa password in
a very, very, very safe place, and then create a LOGON TRIGGER, e.g.
USE master;
GO
CREATE TRIGGER BlockLogin
ON ALL SERVER
WITH EXECUTE AS 'sa'
FOR LOGON
AS
BEGIN
IF ORIGINAL_LOGIN() LIKE '%\%'
ROLLBACK
END
GO
> We have seen some users logged in to the database server, when they should
> not have access.
Do you mean logged onto the physical server (e.g. through terminal
services), or SQL Server itself? Where have you seen them?
The former, I assume, would be controlled through Active Directory etc., and
not by SQL Server.
A|||We have deleted the login "BUILDIN\ADMINISTRATOR", and this seems to work
fine
Do you think there are any isses if I delete this login?
Thanks, Mark
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:OKzCy%23ZmIHA.5660@.TK2MSFTNGP02.phx.gbl...
> Hi
> If the users have not directly been granted a login, then they probably
> have been granted logins through a windows group. Make sure that they are
> not members of a group they should not be in or a group with a wide
> membership has been granted a login. You can use sp_denylogin to deny
> access to a given login.
> John
> "Mark" <Mark@.nospaml.com> wrote in message
> news:%236TpQHZmIHA.2268@.TK2MSFTNGP02.phx.gbl...
>> Hi SQL Gurus,
>> I am still not a master in SQL Server.
>> How can we restrict user from login using "WINDOWS AUTHENTICATION".
>> We don't want anyone who has the server windows login to access SQL
>> Server.
>> We have seen some users logged in to the database server, when they
>> should not have access.
>> Please help me how to restrict them accessing the server.
>> Thanks for any help or suggestion you can provide.
>> Mark.
>|||Mark,
The only issue is to make sure that someone still has administrator rights
so that it is still possible to administer the server. Getting
BUILTIN\ADMINISTRATOR out of your SQL Server is an excellent practice.
http://www.sqlservercentral.com/articles/Security/10securingyoursqlserver/701/
RLF
"Mark" <Mark@.nospaml.com> wrote in message
news:OerAowbmIHA.4712@.TK2MSFTNGP04.phx.gbl...
> We have deleted the login "BUILDIN\ADMINISTRATOR", and this seems to work
> fine
> Do you think there are any isses if I delete this login?
> Thanks, Mark
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:OKzCy%23ZmIHA.5660@.TK2MSFTNGP02.phx.gbl...
>> Hi
>> If the users have not directly been granted a login, then they probably
>> have been granted logins through a windows group. Make sure that they are
>> not members of a group they should not be in or a group with a wide
>> membership has been granted a login. You can use sp_denylogin to deny
>> access to a given login.
>> John
>> "Mark" <Mark@.nospaml.com> wrote in message
>> news:%236TpQHZmIHA.2268@.TK2MSFTNGP02.phx.gbl...
>> Hi SQL Gurus,
>> I am still not a master in SQL Server.
>> How can we restrict user from login using "WINDOWS AUTHENTICATION".
>> We don't want anyone who has the server windows login to access SQL
>> Server.
>> We have seen some users logged in to the database server, when they
>> should not have access.
>> Please help me how to restrict them accessing the server.
>> Thanks for any help or suggestion you can provide.
>> Mark.
>>
>|||"Mark" <Mark@.nospaml.com> wrote in message
news:OerAowbmIHA.4712@.TK2MSFTNGP04.phx.gbl...
> We have deleted the login "BUILDIN\ADMINISTRATOR", and this seems to work
> fine
> Do you think there are any isses if I delete this login?
> Thanks, Mark
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:OKzCy%23ZmIHA.5660@.TK2MSFTNGP02.phx.gbl...
>> Hi
>> If the users have not directly been granted a login, then they probably
>> have been granted logins through a windows group. Make sure that they are
>> not members of a group they should not be in or a group with a wide
>> membership has been granted a login. You can use sp_denylogin to deny
>> access to a given login.
>> John
>> "Mark" <Mark@.nospaml.com> wrote in message
>> news:%236TpQHZmIHA.2268@.TK2MSFTNGP02.phx.gbl...
>> Hi SQL Gurus,
>> I am still not a master in SQL Server.
>> How can we restrict user from login using "WINDOWS AUTHENTICATION".
>> We don't want anyone who has the server windows login to access SQL
>> Server.
>> We have seen some users logged in to the database server, when they
>> should not have access.
>> Please help me how to restrict them accessing the server.
>> Thanks for any help or suggestion you can provide.
>> Mark.
Hi Mark
I think the issue you should address is why these users are over privileged
as Russell suggests. Removing anyone who does not need the local
administrators privilege should be an urgent priority, in the worst case
they will also be domain administrators.
John|||Thanks Guys.
I got the answer I was looking for.
Thanks again, Mike
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:uQR%23lUimIHA.944@.TK2MSFTNGP05.phx.gbl...
> "Mark" <Mark@.nospaml.com> wrote in message
> news:OerAowbmIHA.4712@.TK2MSFTNGP04.phx.gbl...
>> We have deleted the login "BUILDIN\ADMINISTRATOR", and this seems to work
>> fine
>> Do you think there are any isses if I delete this login?
>> Thanks, Mark
>>
>> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
>> news:OKzCy%23ZmIHA.5660@.TK2MSFTNGP02.phx.gbl...
>> Hi
>> If the users have not directly been granted a login, then they probably
>> have been granted logins through a windows group. Make sure that they
>> are not members of a group they should not be in or a group with a wide
>> membership has been granted a login. You can use sp_denylogin to deny
>> access to a given login.
>> John
>> "Mark" <Mark@.nospaml.com> wrote in message
>> news:%236TpQHZmIHA.2268@.TK2MSFTNGP02.phx.gbl...
>> Hi SQL Gurus,
>> I am still not a master in SQL Server.
>> How can we restrict user from login using "WINDOWS AUTHENTICATION".
>> We don't want anyone who has the server windows login to access SQL
>> Server.
>> We have seen some users logged in to the database server, when they
>> should not have access.
>> Please help me how to restrict them accessing the server.
>> Thanks for any help or suggestion you can provide.
>> Mark.
> Hi Mark
> I think the issue you should address is why these users are over
> privileged as Russell suggests. Removing anyone who does not need the
> local administrators privilege should be an urgent priority, in the worst
> case they will also be domain administrators.
> John
>