locked
Disable sysadmin RRS feed

  • Question

  • Hi Team,

    Few queries on Sysadmin.

    Is it possible to disable 'sysadmin' role for SQL instance?

    Is it recommended to disable 'Sysadmin' if its possible?

    What is the approach, create one more user, assign it 'sysadmin' role and then disable the default sysadmin?

    If i have multiple instance of SQL and have Dbs, do i need to disable for every instance or disabling from 1 instance would disable it for other instance also (i don't think so but just want to confirm if anyone has tried this)?

    Any pointers will be appreciated. Thanks

    Regards,

    Friday, August 12, 2016 8:38 AM

Answers


  • What is the approach, create one more user, assign it 'sysadmin' role and then disable the default sysadmin?

    If i have multiple instance of SQL and have Dbs, do i need to disable for every instance or disabling from 1 instance would disable it for other instance also (i don't think so but just want to confirm if anyone has tried this)?

    You cannot remove built in accounts , for example you cannot remove SA but can disable it. You can create a login assign it sysadmin rights and then disable the login but what would be point int that.

    In SQL Server instances operate mutually exclusive so logins on instance one has no relation to instance 2, you would have to disable on both of them


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Friday, August 12, 2016 8:54 AM
  • I assume by 'sysadmin' you meant the 'sa' login. It depends on your company's security policy on how you deal with sa login. Disabling the sa login is considered one of the best security practices besides renaming or changing its password to a difficult one. The SID of sa login is always '0x01'. Hence, after renaming, you could query the sys.sql_logins to get the original sa login name. 

    Also, note that disabling sa account will not break down anything as the impersonation still works even after the sa has been disabled. Databases owned by sa still work fine, any jobs owned by sa work fine too.

    You could consider having an alternate sa login if you plan to disable the default one.

    The only gotcha when considering disabling sa account is during upgrading/applying service packs/CUs. Make sure to enable sa  before applying any patches/CUs as there have been cases of issues if the sa is disabled when applying patches/CU. After the update operation is done, disable it back again.

    As for your second part of question, if there is more than one instance run independently, you would have to disable on all the instances.

    Hope this helps.

    Mak

    Friday, August 12, 2016 12:54 PM

All replies

  • AFAIK, is not possible to remove you only can revoke permissions for logins and so on..

    Or at least in a documented way...

    Friday, August 12, 2016 8:50 AM

  • What is the approach, create one more user, assign it 'sysadmin' role and then disable the default sysadmin?

    If i have multiple instance of SQL and have Dbs, do i need to disable for every instance or disabling from 1 instance would disable it for other instance also (i don't think so but just want to confirm if anyone has tried this)?

    You cannot remove built in accounts , for example you cannot remove SA but can disable it. You can create a login assign it sysadmin rights and then disable the login but what would be point int that.

    In SQL Server instances operate mutually exclusive so logins on instance one has no relation to instance 2, you would have to disable on both of them


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Friday, August 12, 2016 8:54 AM
  • I assume by 'sysadmin' you meant the 'sa' login. It depends on your company's security policy on how you deal with sa login. Disabling the sa login is considered one of the best security practices besides renaming or changing its password to a difficult one. The SID of sa login is always '0x01'. Hence, after renaming, you could query the sys.sql_logins to get the original sa login name. 

    Also, note that disabling sa account will not break down anything as the impersonation still works even after the sa has been disabled. Databases owned by sa still work fine, any jobs owned by sa work fine too.

    You could consider having an alternate sa login if you plan to disable the default one.

    The only gotcha when considering disabling sa account is during upgrading/applying service packs/CUs. Make sure to enable sa  before applying any patches/CUs as there have been cases of issues if the sa is disabled when applying patches/CU. After the update operation is done, disable it back again.

    As for your second part of question, if there is more than one instance run independently, you would have to disable on all the instances.

    Hope this helps.

    Mak

    Friday, August 12, 2016 12:54 PM