May 27, 2008 at 3:57 am
i have sql server 2005 installed on a server and i dont want other dba to use sql server on this server ....he can login through windows authentication mode , i just want to restrict the use to one person and with a password which would be sql server login mode , so how do i remove windows authentication mode or do something to keep access to only one user and no other to use it
thank you
May 27, 2008 at 9:37 am
Since there's no such thing as SQL Server only authentication, you'll have to change it to mixed authentication mode and remove the Windows login that the other DBA uses to connect. You can change the authentication mode by right-clicking on the instance name in SSMS, selecting Properties, selecting Security, and choosing the authentication mode.
You can also start SQL Server in single-user mode from a command prompt, though you should read the cautions in BooksOnLine (see "single-user mode[SQL Server]).
Greg
May 28, 2008 at 2:48 am
I thought this was possible simply by removing the Windows groups and users from the Sysadmin server role (Windows groups and users are those with a backslash in them). Note - I have not tested this.
May 28, 2008 at 7:28 am
Why are you trying to restrict another DBA, just out of curiosity?
"Got no time for the jibba jabba!"
-B.A. Baracus
May 28, 2008 at 10:26 am
As previously mentioned, you can't block Windows based logins from inside SQL Server. SQL Server-based logins can be blocked, but not Windows-basde ones.
The rule in SQL Server is if security isn't specifically granted, it's already blocked. In other words, if the other DBA's login hasn't been granted access either directly via the Windows user or via a Windows group, the DBA doesn't have the ability to login.
By default the only group which is granted access is the BUILTIN\Administrators group. This group corresponds to the local Administrators group on that system (at the Operating System level). If the other DBA is a member of that group, you can impede his/her ability to access your SQL Server but not completely prevent it.
The way to do this is to first ensure you know the SQL Server login you are going to use. However, it if it is a SQL Server only you should use, I would recommend adding your Windows user account and making it a member of the sysadmin fixed server role. It is preferable to use Windows logins whenever possible. Once you've done that, verify the service account running SQL Server and SQL Server Agent have been made as logins and are members of the sysadmin fixed server role as well. Then you should be able to safely remove the BUILTIN\Administrators login from SQL Server. The one proviso is if you're on a cluster, but based on what you've described thus far, I am guessing that's not the case.
Again, consider the ramifications of deploying a security model of this sort. If you're out sick or out of pocket on vacation and someone needs access to the databases on this system, you've forced them to use the known backdoors to get in. That's not exactly good from a business continuity perspective.
K. Brian Kelley
@kbriankelley
May 28, 2008 at 10:33 am
I agree with what Brian has written on the technical side.
But, why do you want to block this? Think about that and honestly rather than creating issues with a one-off security item, I'd handle this administratively if someone "shouldn't" access the server. Take this to a manager, say that rights are there in an emergency, but they shouldn't normally be used.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply