Change the password of sa_test<\/b> to “54321” via Enterprise Manager or using:
sp_password: sp_password @new=’54321′,@loginame=’sa_test’<\/li>\n<\/ol>\nNow, forget you ever knew the password was “12345”. All you know is that the original password was:<\/p>\n
“0x01005B20054332752E1BC2E7C5DF0F9EBFE486E9BEE063E8D3B332752E1BC2E7C5DF0F9EBFE
486E9BEE063E8D3B3″. <\/p>\n
Changing the password back <\/h3>\n
Next is the fun. There are a couple of ways to set the password back to the forgotten\/unknown password. The first is to update the sysxlogins<\/b> table directly, which, of course, is ill-advised. If it is possible to do so, and there is even a special configuration to allow such practices, it can be used as a last resort.<\/p>\n
The second technique I will demonstrate involves use of the sp_password<\/b> stored procedure, but with a slight modification.<\/p>\nAllowing direct updates on sysxlogins <\/h4>\n
The option I am referring to is Allow Updates<\/b>. You can see this option by executing sp_configure<\/b>, see Figure 1.<\/p>\n
![\"409-Figure1.gif\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/409-Figure1.gif\")
<\/p>\n
If this option is off, indicated by a run value of 0, you can not directly update system tables via an ad-hoc query, like the following:<\/p>\n
<\/pre>\nUPDATE <\/code>master..sysxlogins <\/code>SET <\/code>
<\/code>password <\/code>= <\/code>0x01005B20054332752E1BC2E7C5DF0F9EBFE486E9BEE063E8D3B332752
E1BC2E7C5DF0F9EBFE486E9BEE063E8D3B3 <\/code>
<\/code>WHERE <\/code>name <\/code>= <\/code>'sa_test'<\/code><\/p>\n\n
If you execute an update statement that updates the sysxlogins<\/b> table directly, using a hashed password, you will get the following error:<\/p>\n<\/pre>\nServer: Msg 259, Level 16, State 2, Line 1<\/p>\n
Ad hoc updates to system catalogs are not enabled. The system
administrator must reconfigure SQL Server to allow this.<\/p>\n
However, having set the Allow Updates<\/b> option to 1, or True, the same query will work successfully, as shown in Figure 2.<\/p>\n
![\"409-Figure2.gif\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/409-Figure2.gif\")
<\/i><\/p>\n
Now that we can update system tables directly, it is possible to issue the same update statement to set the password back to “12345” at the appropriate time, see Figure 3.<\/p>\n
![\"409-Figure3.gif\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/409-Figure3.gif\")
<\/i><\/p>\n
To test the connectivity with the former password, “12345”, use Enterprise Manager or SQL Server Management Studio to connect to the instance as sa_test<\/b>.<\/p>\n
Because this type of request is very rare, there is nothing more to do than set the Allow Updates<\/b> back to 0 for security.<\/p>\nUsing a modified sp_password procedure <\/h4>\n
Being inclined to go off-road for the hell of it, I wanted to show how this same task can be accomplished with a slight alteration to the sp_password<\/b> stored procedure. As discussed earlier, it is not possible, unlike for sp_addlogin<\/b>, to bypass the pwdencrypt()<\/b> function. However, you can take the base sp_passsword<\/b> stored procedure and alter the section that encrypts the passed in password. In all fairness, sp_password<\/b> was designed to use legitimate alpha-numeric and character values similar to:<\/p>\n<\/pre>\nsp_password <\/code>@old<\/code>=<\/code>'12345'<\/code>, <\/code>@new<\/code>=<\/code>'54321'<\/code>,<\/code>@loginame<\/code>=<\/code>'sa_test'<\/code><\/p>\nFortunately for our purposes, the variable @old<\/b> for sp_password is not required. The section of code in sp_password<\/b> that controls the updating of the new password is in Listing 2. This section is not optional. The pwdencrypt()<\/b> will always be called:<\/p>\n
Listing 2<\/b>:<\/p>\n<\/pre>\n <\/code>-- CHANGE THE PASSWORD -- <\/code>
UPDATE <\/code>master.dbo.sysxlogins <\/code>
<\/code>SET <\/code>password <\/code>= <\/code>CONVERT<\/code>(<\/code>varbinary<\/code>(<\/code>256<\/code>),
<\/code>pwdencrypt<\/code>(<\/code>@new<\/code>)),
<\/code>xdate2 <\/code>= <\/code>GETDATE<\/code>(),
<\/code>xstatus <\/code>= <\/code>xstatus <\/code>& (~2048) <\/code>
where name = @loginame and srvid IS NULL<\/code><\/p>\nThe modification to the “Change the Password” section of sp_password<\/b>, which will reside in a new stored procedure called sp_password_omg<\/b>, is in Listing 3.<\/p>\n
Listing 3<\/b>:<\/p>\n<\/pre>\n <\/code>-- CHANGE THE PASSWORD -- <\/code>
UPDATE <\/code>master.dbo.sysxlogins <\/code>
<\/code>SET <\/code>password <\/code>= <\/code>CONVERT<\/code>(<\/code>varbinary<\/code>(<\/code>256<\/code>),<\/code>@new<\/code>) <\/code>
<\/code>WHERE <\/code>name <\/code>= <\/code>@loginame <\/code>
<\/code>AND <\/code>srvid <\/code>IS <\/code>NULL<\/code><\/p>\nAll that we are doing is removing the requirement for the pwdencrypt()<\/b> function. The conversion to varbinary is fine since that is the defined length of the column in sysxlogins<\/b>. The added benefit of creating the sp_password_omg<\/b> stored procedure is that it circumvents the need to reconfigure the Allow Updates<\/b> option for ad-hoc queries.<\/p>\n
The statement to execute sp_password_omg<\/b> is:<\/p>\n<\/pre>\nsp_password_omg <\/code>@new<\/code>=<\/code>0x01005B20054332752E1BC2E7C5DF0F9EBFE486E9BEE063E
8D3B332752E1BC2E7C5DF0F9EBFE486E9BEE063E8D3B3<\/code>,<\/code>@loginame<\/code>=<\/code>'sa_test'<\/code><\/p>\nTo this point, I have shown a way to change a SQL user’s password to a new value with the facility to revert back to an unknown value if required. This has worked well for our test user, sa_test<\/b>, which was really important to do prior to moving onto the all-powerful ‘sa’. The same techniques work the same for ‘sa’ with one minor caveat: the sp_help_revlogin<\/b> stored procedure automatically excludes ‘sa’ from the list of accounts to create logins for. Listing 3 shows the exclusion code from sp_help_revlogin<\/b>.<\/p>\n
Listing 3<\/b><\/p>\n<\/pre>\nIF <\/code>(<\/code>@login_name <\/code>IS <\/code>NULL) <\/code>
DECLARE <\/code>login_curs <\/code>CURSOR FOR <\/code>
SELECT <\/code>sid<\/code>, <\/code>name<\/code>, <\/code>xstatus<\/code>, <\/code>password <\/code>
<\/code>FROM <\/code>master..sysxlogins <\/code>
<\/code>WHERE <\/code>srvid <\/code>IS <\/code>NULL <\/code>
AND <\/code>name <\/code><> <\/code>'sa'<\/code><\/p>\nIn order to allow the above steps to work with ‘sa’ as easy as we worked with “sa_test”, we simply comment out the exclusion, as shown in Listing 4.<\/p>\n
Listing 4<\/b><\/p>\n<\/pre>\nIF <\/code>(<\/code>@login_name <\/code>IS <\/code>NULL) <\/code>
DECLARE <\/code>login_curs <\/code>CURSOR FOR <\/code>
SELECT <\/code>sid<\/code>, <\/code>name<\/code>, <\/code>xstatus<\/code>, <\/code>password <\/code>
<\/code>FROM <\/code>master..sysxlogins <\/code>
<\/code>WHERE <\/code>srvid <\/code>IS <\/code>NULL <\/code>\/* AND name <> 'sa' *\/<\/code><\/p>\nUsing sp_help_revlogin on SQL Server 2005 <\/h2>\n
So, now that we know how to temporarily change an unknown password of the SA account in SQL Server 2000, let’s do the same in SQL Server 2005. Fortunately, the procedure is almost identical, save for the sp_help_revlogin<\/b> stored procedure. Yes, we will still need to add the same comment to omit the ‘sa’ account as we did in Listing 4. The slight variation of the driving SELECT<\/b> statement can be seen in Listing 5.<\/p>\n
Listing 5<\/b><\/p>\n<\/pre>\nSELECT
<\/code>p.sid<\/code>,
<\/code>p.name<\/code>,
<\/code>p.type<\/code>,
<\/code>p.is_disabled<\/code>,
<\/code>p.default_database_name<\/code>,