Systems Engineering and RDBMS

Disabling a login account

Posted by decipherinfosys on December 26, 2008

One of the readers had asked us recently about how he can disable the sysadmin account in SQL Server since he did not want anyone to use the “sa” login account. This team has over 20+ DBAs/DB Developers in the team and as per him, even in the application connection strings, the teams were using the “sa” login to make the connections.

One can easily disable any account in SQL Server 2005 and above by using the ALTER LOGIN command:

If you want to disable the “sa” login account, make sure that you are logged in using windows authentication and also make sure that you have other accounts set up with sysadmin privs as well.

ALTER LOGIN [sa] DISABLE;

will disable the login and replacing DISABLE with ENABLE and executing it will enable it. This can be done via the GUI as well. If you right click on the login in SSMS, select properties and go to the last page called “Status”, over here you will see the option of enabling/disabling the login as shown in the image below:

login_ed_1

About these ads

2 Responses to “Disabling a login account”

  1. [...] you can set it up there under the Status Page).  We had covered disabling an account in a previous post – you can simply replace “Disable” with “Enable” in that post and the [...]

  2. [...] Post re-starting the service, it did change the authentication mode to mixed mode, however due to un-successful attempts and the password policy in place, the “sa” account was locked out/disabled.  One can easily enable it but first one needs to be able to get in with sysadmin privs. and then make the changes.  Best option was to get one of the IT team members to login and post it, add additional logins, enable the “sa” account, set it’s password and then make whatever changes needed for the logins/their access levels etc.  The user did that and was on his way to managing the newly created instance.  One can enable a disabled/locked SQL Server account through the GUI or by using the “ALTER LOGIN” statement as we had shown in a previous post here. [...]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 74 other followers

%d bloggers like this: