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

  • Hello,

    How do I find which Admin had disable/enabled a login in SQL 2005?

    Thanks,
    Abhilash

    Reply
  • Hola.

    Me di cuenta de algo bastante raro en sql server 2005 y necesito corroborarlo, porque me parece un bug de sql server 2005.

    1.- Cree un login con la instruccion alter login …
    2.- Luego lo cree como usuario en la BD que le corresponde con la instruccion CREATE USER ….
    3.- Luego se me pidio hacer un SP ( stored procedure) , en donde los usuarios pudieran cambiar sus password ( contraseñas).
    4.- Realice dicho SP, ocupando ALTER LOGIN …..
    5.- Forme una instruccion dinamica, como sigue :

    select @v_query = “ALTER LOGIN ” + @fld_cod_usu + ” WITH PASSWORD = ‘” + @fld_pas_new + “‘ OLD_PASSWORD = ‘” + @fld_pas_old + “‘”

    y luego se executa como sigue :

    EXECUTE sp_executesql @v_query

    6.- Hasta aqui todo bien y de hecho funciona bien.

    7.- La raro que descrubrí, haciendo pruebas, es que en sql server 2005 al crear el login por primera vez, éste tiene la capacidad de cambiar su contraseña, y la de CUALQUIER OTRO USUARIO. !!!!! uppsss

    8.- Ante este escenario, le denegué el permiso de hacer eso vía la instruccion : deny alter any login to

    9.- Esto a su vez implico, que el usuario ya no puede cambiar su clave, dado que es requisito segun sql servr 2005 que para ocupar alter login, se tenga el permiso de alter any login.

    10.- Esto lo comprobé creando 3 usuarios desde la nada y SIN ocupar la intruccion grant alter any login al momento de la creacion.

    El script exacto de la creacion del login para su revisión, es :

    USE master
    go
    CREATE LOGIN [usu_003]
    WITH PASSWORD=N’12345678’ ,
    DEFAULT_DATABASE = db_cheq,
    DEFAULT_LANGUAGE = us_english,
    CHECK_EXPIRATION = OFF,
    CHECK_POLICY = OFF
    go
    ALTER LOGIN [usu_003] ENABLE
    go
    GRANT CONNECT SQL TO [usu_003]
    go

    Esperando sus comentarios, atte claudiok.

    Reply
  • sir u r the ultimate authoriy , wether microsoft itself unable to solve my problem.
    thanks,

    Reply
  • Great info Thanks

    Reply
  • with apropos to Mr. Ranjeet, I completely agree !!! HatsOff !!!!!!

    Reply
  • hi SIR,,PLS HELP ME VERY URGENT,,,,,I LOGGED IN INTO SSMS AND IN THE SECURITY TAB I MADE LOGIN DISABLE,,,,NOW I M TRYING TO OPEN DATEABASE IT IS SHOWING AN ERROR,,,LOGIN IS DISABLED,,,PLS HELP ME,,,I M NEW TO SQL
    PLS HELP….

    Reply
  • Sir, all the answers based on after connecting to Management Studio. What to do if the SQL server it self get disabled and preventing to connect to the Database. This is happening in my case sir. Pl. read.
    Sir,
    Our front end software which is in VB has disabled the SQL. Now i am unable to start Management Studio with my Administrator account or with any other logins. Because of this I am unable to get the data through tables directly. Our software makers deliberately did this to use their report generation menus from their front end software(VB) only. But I become handicapped because of this. For me the enabling/Disabling an user only possible after getting connected to Management Studio. In my case I cant get connected to Management Studio. I tried in different levels through services.msc etc. If I reinstall the SQL then I am able to connect but the problem starts after installation of our software. I feel that there might be a solution for this. The screen shot for the same is .Pl. give me a sollution.

    Specifications:- Windows Server 2003 Service Pack 2, SQL 2008.

    Reply
  • How do disable SA on SQL 2000?

    Reply
  • How we can enable all the login in a single shot instead of doing one by one.

    Reply
  • Want to Check the Log of the SA LOGIN ACCOUNT?
    Script if any.

    Reply
  • BrigidaScholl51
    January 6, 2016 6:23 am

    Thank you for the auspicious writeup. It in reality was once a entertainment account it. Glance advanced to more brought agreeable from you! However, how could we keep in touch?

    Reply
  • gabriel batistuta
    November 11, 2018 9:02 am

    hi mister,

    i have this scenarios;
    i need to disable certain users connecting to db
    but allowing certain users connected.

    can we do that?

    tq.

    Reply

Leave a Reply