SQL SERVER – Enable Login – Disable Login using ALTER LOGIN – Change name of the ‘SA’

In SQL SERVER 2005, all the login (including ‘sa’ ) can be enabled and disabled using ALTER LOGIN command. This is a very popular request quite a lot of time as a user want to secure their system administrator’s account ‘SA’. The best possible scenario is either to disable SA username or change it to some new username.

SQL SERVER - Enable Login - Disable Login using ALTER LOGIN - Change name of the 'SA' changesa

 

In this blog post we will learn how we can disable the SA login or disable the same. In this blog post we will also see that we can change the SA to a new name. Make sure that you change the new name in such fashion that users can’t guess them easily.

To disable ‘sa’ login:

ALTER LOGIN sa DISABLE
GO

To enable ‘sa’ login:

ALTER LOGIN sa ENABLE
GO

Also for additional security (prevent educated guess from hackers) the name of the ‘sa’ account can be changed.

ALTER LOGIN [sa] WITH NAME = [AdminUser]
GO

Well, that’s it. I can tell you that most of the database out there has either SA enabled with a simple password. It is our responsibility that we change the SA user to another name so potentially bad people can’t guess it easily.

Please leave your views in the comments section.

Reference: Pinal Dave (https://blog.sqlauthority.com)

, SQL Scripts, SQL Server, SQL Server Security
Previous Post
SQL SERVER – FIX : ERROR 1101 : Could not allocate a new page for database because of insufficient disk space in filegroup
Next Post
SQL SERVER – DBCC Commands to Free SQL Server Memory Caches

Related Posts

35 Comments. Leave new

  • Ricky Shahzelle
    July 13, 2007 6:22 pm

    Lovely…
    excellent articles.

    Reply
  • Great stuff.

    I have a question … How do I check if a login is disabled or enabled ?

    Thanks

    Reply
  • Hey,
    Is there a way to do the same in sql2k ? without using xp_revoke ?

    FP

    Reply
  • SELECT is_disabled from sys.server_principals WHERE name=@login

    Reply
  • Login faild for “user” Reason: The account is disabled. (microsoft sql server, Error :18470)

    Can you please suggest a solution for the above error

    thanks

    eppa

    Reply
  • Great info! Thanks!

    Reply
  • Could anyone help – I want an operator to be able to chnage their password when logged into a database using their own login. I need to do this with both SQL2K or SQL 2005 databases from the same app.

    Reply
  • Gracias!

    Reply
  • Is there any dependency table to check, all the dependencies of a Login account before disabling it?

    I will check for jobs (owner) … what else?
    Ravi

    Reply
  • Good, Thanks;;

    But how can I enable and disable ‘sa’ account using command line?

    1- I’m using windows 2003 + MS SQL 2005 Enterprice Edition
    2- I have a full administrator prevoligies on the server
    3- I’ve removed the builtin/adminstrators from the SQl 2005 Logins :(

    in brief I have the ‘sa’ user and the password, but the sa account is disabled :(

    how can i access the sql database cause i can’t login now :( ??

    Reply
  • How do I enable for all users at one time

    Reply
  • Thanks :)

    Reply
  • Hello my friends!
    The interesting name of a site – blog.sqlauthority.com
    I recently 1 hours
    looked in a network So I have found your site :)
    The interesting site but does not suffice several sections!
    However this section is very necessary!
    Best wishes for you!
    Forgive I is drunk :))

    Reply
  • FP, did anyone answer this?

    Is there a way to disable the sa account in SQL 2000?
    I want to disable but not revoke right now.
    thanks, Laura

    Reply
  • I’m trying to disable a SQL2K Login. The only thing I can think of right now is to change the password, since the one I want to disable is using SQL Server Authentication.

    Reply
  • Hello Rod,

    You can disable a login by right click the login > Properties > Status page > Disbaled

    Regards,
    Pinal Dave

    Reply
    • Hi Pinal,

      I understand that we can not disable a login (SQL Server authenticated) by the method mentionded in your reply.

      Regards,
      Nishit Shah

      Reply
  • Pinal,

    How to check if a login is diabled/enabled in SQL 2k?

    Reply
  • Hi,
    My computer cannot log in the sa account, too. My SQL server 2005 was installed with VS2008. I didn’t care it at that time. I want to use it now. So I don’t know how to let it work?

    Another question: If I use the Windows Authorizations to log in my SQL Server 2005. Use which account in my C# program?

    Regarsd,

    Bing
    Ths!

    Reply
  • how i can disable particular record from table of database.

    i am using
    1.Microsoft sql server 2005
    2.project is in c#.net
    3.microsoft visual studio 2008

    Reply
  • thanks a lot, your blog is really help me a lot.

    I’m sure many people do.

    Reply

Leave a Reply