0

I have a requirement wherein I need to remove sysadmin server role from sa login.

I created 1 more user and gave the sysadmin role to the user.

When I uncheck sysadmin from sa login it throws an error.

Cannot use the special principal sa error 15405.

Can anyone have any idea how to do this?

3
  • That's a rash requirement, MS have had bugs where upgrades (SPs, new versions) have failed if SA login is removed. A more normal practice is to disable the SA login - maybe you could double check the requirement. – Stephen Morris - Mo64 Dec 3 '20 at 10:13
  • Well, actually, you must give sa long, forgettable/non-rememberable password, pack it in an envelope, and put it in the safe. And for everyday administrative tasks, you must create a different account. – Akina Dec 3 '20 at 12:34
  • 1
    You can throw away the password. A Windows Admin can always change it. docs.microsoft.com/en-us/sql/database-engine/configure-windows/… – David Browne - Microsoft Dec 3 '20 at 17:43
6

You cannot remove sysadmin from this login. The login with sid 0x01 (generally named "sa") is always sysadmin.

I assume that you have disabled sa in the first place, btw.

How about renaming sa to some other name?

4
  • 1
    Or disabling it :) – Shanky Dec 3 '20 at 13:16
  • Absolutely, Shanky! That was implied in my thinking. I assumed that the mere fact of sa being sysadmin (even if disabled) was the problem. I'll add a note on that in my answer! :-) – Tibor Karaszi Dec 3 '20 at 16:26
  • Please don't rename sa for Mo64's reason in his comment. – John K. N. Dec 8 '20 at 9:57
  • I remember the timeframe where there were som bugs with renamed sa. I believe it was around 2005 (or was it even 7.0/2000)? I haven't seen any bugs related to this for at least 10 years. It doesn't mean they don't exist, but I have a feeling that this might be one of those things that sticks even if not applicable anymore... – Tibor Karaszi Dec 8 '20 at 13:08
0

I have a better approach:

  1. Change the sysadmin password
  2. Give your sysadmin user with the new password to your new DBA
-1

You cannot drop the sa login and change the permissions of sa.But you can change its password, rename and disable it.

2
  • 2
    Welcome to DBA.SE. While your answer might be valid, it does help if there is some context is given or material referenced. Please have a look at How to Answer. – John K. N. Dec 8 '20 at 9:56
  • Also, please don't post answers that are more or less duplicates of existing ones (Tibor's answer). – LowlyDBA - John M Dec 8 '20 at 13:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.