33 thoughts on “SQL SERVER – Enable Login – Disable Login using ALTER LOGIC – Change name of the ‘SA’

  1. 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

    Like

  2. 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.

    Like

  3. 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 :( ??

    Like

  4. 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 :))

    Like

  5. 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.

    Like

    • Hi Pinal,

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

      Regards,
      Nishit Shah

      Like

  6. 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!

    Like

  7. 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

    Like

  8. 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.

    Like

  9. 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….

    Like

  10. 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 http://imageshack.us/a/img59/5246/76643895.png .Pl. give me a sollution.

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

    Like

  11. Pingback: SQL SERVER – Weekly Series – Memory Lane – #027 | SQL Server Journey with SQL Authority

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