SQL SERVER – DISABLE and ENABLE user SA

Just a day ago, I received question from blog reader Mike McDonald.

“How can I modify permissions for SA user? I tried to modify dbo users permission but now I am having problems.”

First of all, there may be no relation between dbo user and SA user. They are different and should be left separate.

Modifying the permission of SA user is not possible. However, SA can be disable or enabled using following script. Make sure that you are logged in using windows authentication account.

/* Disable SA Login */
ALTER LOGIN [sa] DISABLE
GO
/* Enable SA Login */
ALTER LOGIN [sa] ENABLE
GO

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

17 thoughts on “SQL SERVER – DISABLE and ENABLE user SA

  1. Pingback: SQL SERVER - Fix : Error : Msg 15151, Level 16, State 1, Line 2 Cannot alter the login ’sa’, because it does not exist or you do not have permission Journey to SQL Authority with Pinal Dave

  2. Pingback: SQL SERVER - Add Any User to SysAdmin Role - Add Users to System Roles Journey to SQL Authority with Pinal Dave

  3. Hi,

    I had scheduled a job of four steps.

    step 1: I try to insert four records to a table.
    success: goto next step
    failure: go to step 4

    Step2: display the inserted records
    success: go next step 3
    failure: go to step 4
    write into the records to txt file

    step3:delete all records.
    success: quit the job reporting success
    failure: goto step4
    attach a file to write into the records.

    step4: Printing the reason of failure statements
    sucess:quit the job reporting failure
    failure: quit the job reporting failure

    “Print the statement into text field stating the reason for failure in the Txt field.

    thanks,
    Ravi

  4. Hi,

    I’m trying this, I’m logged on to the server using Windows auth as the Domain Admin and when I try enabling SA with

    ALTER LOGIN [sa] ENABLE

    I get

    Msg 15151, Level 16, State 1, Line 1
    Cannot alter the login ‘sa’, because it does not exist or you do not have permission.

    Any thoughts?

  5. I am getting a permission to registry error installing SQL Server 2008 on a machine that have already Dynamics POS installed.

    Does this has any relation to “sa” user?

    I am installing as administrator.

    Thoughts?
    Thank you!

  6. If I disable a login, what will happen to the jobs which are owned by it?
    for instance, a backup job we have is owned by sa. If I disable the login, will the job fail to run?

  7. Hi Pinal,
    I am consolidating all database users for reporting purpose, and I need to know if database users are disabled or enabled.
    Can database users (not logins) can be disabled? Using sysusers or sys.sysusers, you can know if a user hasdbaccess. Can similar information can be found using database_principals?

    Any help is appreciated.

  8. Hi,
    Does anybody know how to suppress the message like :

    “Configuration option ‘show advanced options’ changed from 0 to 1. Run the RECONFIGURE statement to install.”

    after executing sp_configure ‘show advanced options’, 1?

  9. I am attempting to disable or lock the SA account in SQL Server 2000. Is there a procedure by which I can do this? I am running mixed authentication. I need to make the SA account unavailable, essentially to render it unusable. We are required to change the password each time a DBA leaves, on 200 intances. Would rather disable the account than continually have to change the password.

  10. Hi there,

    I have been assign to display list of users who has DBO access and remove the DBO acces… However i was unable to create require script to complete work. Please advice…

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

  12. hey All ,,, required your help on urjent basis … new bee to sql server .
    requirement is – need to create one new user on SQL Server2008R2 that would be having select right for only 2 tables .. what is happening user is having rights for other tables too … that i m not able to restrict. achieved on different version . let me know… what i m missing

  13. Can you please give me some reason why to disable ‘sa’ account?
    Special, if you have one sql login sysadmin like ‘sa’ and one sysadmin domain login.

  14. Hello, i have a nightly data load job, and it runs under a specific db user. I would like to keep all other users out until the loads are done. Is there a way to have just one user running the loads, and setup a job that runs every 10 minutes or so, to kill all other connections until the loads are done. Currently, i have a job that runs once, and kills all SIDs at midnight, before the loads start. However, there are times that users forget and schedule jobs past that time and it delays my data loads. thanks in advance.

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