When it comes to securing the databases, we need to have a small number of accounts with 'sysadmin' fixed server role, which means that only the DBA and someone else should be assigned to that role inside the database engine. So, I highly recommend disabling or removing 'sa' account from 'sysadmin' fixed server role in SQL Server 2000. Nevertheless, this task is not so easy to do because any change on 'sa' account is protected internally by SQL Server. Furthermore, Microsoft says that this account cannot be changed at all
http://msdn.microsoft.com/en-us/library/aa905197(SQL.80).aspx.
System administrator (sa) is a special login provided for backward compatibility. By default, it is assigned to the sysadmin fixed server role and cannot be changed. Although sa is a built-in administrator login, do not use it routinely. Instead, make system administrators members of the sysadmin fixed server role, and have them log on using their own logins. Use sa only when there is no other way to log in to an instance of Microsoft® SQL Server™ (for example, when other system administrators are unavailable or have forgotten their passwords).
Because I also know that "
In theory there is no difference between theory and practice. But, in practice, there is.” (Jan L.A. van de Snepscheut), I was digging deep into this issue and found out that, fortunately, what Microsoft says is true up to a point, but we can take control of it by making small changes internally. In other words, the famous 'sa' account can be changed in SQL Server 2000. Today's post is going to show how to achieve it with ease. (The demonstration is done on SQL Server 2000 Service Pack 3a.)
First of all, we do need to know that the logic to manage the 'sa' account is stored inside the 'master' system database of SQL Server. Consequently, I decided to look into the code of sp_dropsrvrolemember, sp_addsrvrolemember, and sp_droplogin system stored procedures. What I figured out is that all of them contain the following validation based on the name of the account to prevent from any change.
According to that code, only if we change that name will SQL Server allow us to make other changes on it. Likewise, I also noticed that SQL Server does not allow to delete this account if its SID is equal to '0x01', so, if I we also change that SID, we will be able to delete the account. This is what we are going to do now.
Naturally, we will get the following error when we try to remove the 'sa' account from 'sysadmin' fixed server role:
exec sp_dropsrvrolemember sa,‘sysadmin’
Server: Msg 15405, Level 11, State 1, Procedure sp_dropsrvrolemember, Line 40
Cannot use the reserved user or role name ‘sa’.
Trying to drop the 'sa' account:
Server: Msg 15405, Level 11, State 1, Procedure sp_droplogin, Line 39
Cannot use the reserved user or role name ‘sa’.
In order to be able to make those changes, we firstly need to enable 'allow updates'.
exec sp_configure 'allow updates', 1
go
reconfigure with override
Having done that, we can remove the 'sa2' account from 'sysadmin' fixed server role.
exec sp_dropsrvrolemember sa2, 'sysadmin'
Changing the SID to '0x02':
update sysxlogins set sid=0x02 where name='sa2'
Finally, only now are we able to delete 'sa2' account.
Having successfully executed each step, we have succeeded in deleting the 'sa' account. As you have seen, it was not overly complicated to get this account deleted. On the other hand, how can we create that account again? if we try to create again the 'sa' account, we will get this error:
Server: Msg 15405, Level 11, State 1, Procedure sp_addlogin, Line 49
Cannot use the reserved user or role name ‘sa’.
Do not worry about that, we can also create it again by executing this code as follow:
exec sp_addlogin 'sa2'
go
exec sp_addsrvrolemember sa2,'sysadmin'
go
update sysxlogins set name='sa' where name='sa2'
go
update sysxlogins set sid=0x01 where name='sa'
That is all for now. I hope you find this tip helpful. Let me know any remarks you may have. Thanks for reading. Stay tuned.
