I just spent 3 hours on the phone with Microsoft tech support to resolve this.
The patch installation uses the SQL SA user, which has sysadmin privileges. If you download and attempt to install the patch manually, you get a dialog box where you are asked to enter the SA password.
My SQL Server 2000 installation is the MSDE version bundled into a commercial product. The developers of the product will not tell the purchasers what the SA password is, and for good reason. Users could do some serious damage to the product's underlying database.
If you don't know the SA password, what can you do?
The manual installation gives you the option of logging on with Windows Authentication instead. It will look in the SQL installation to see if your Windows user ID has SQL sysadmin privileges. My user ID has Windows Administrator privileges, so it is automatically included in the SQL "BUILTIN\Administrators" group. But the developers of the commercial product had removed sysadmin privileges from the SQL "BUILTIN\Administrators" group as another way to protect the database.
The Microsoft rep used the OSQL utility, available even in the MSDE version, to logon to the server with my Windows user ID. The command for that is:
C:\>OSQL -S COMPUTER-NAME\SQL-SERVER-NAME -U WindowsID -P WindowsPassword
Next, we looked for SQL users that had sysadmin privileges:
1> exec sp_helpsrvrolemember 'sysadmin'
2> go
I was very, very lucky that the list returned by this command included another user I recognized as part of the commercial product, AND I knew how to get the password for it. By the way, "exit" is how to get out of the OSQL utility.
We logged back on to the server with OSQL using that SQL user I recognized:
C:\>OSQL -S COMPUTER-NAME\SQL-SERVER-NAME -U SQLUser -P SQLPassword
Now we could give my Windows user ID the sysadmin privilege (the single quotes are required):
1> exec sp_addsrvrolemember 'COMPUTER-NAME\WindowsUser','sysadmin'
2> go
''COMPUTER-NAME\WindowsUser' added to role 'sysadmin'.
Finally, we ran the patch installation again and this time, just clicked Next to accept the default authentication, which is Windows, not SQL. It worked! The patch installed. No special command line switches were necessary; I just typed the name of the file I downloaded.
I didn't want to leave that security window open, so I used OSQL to remove the sysadmin privilege from my Windows user when I was finished (the single quotes are required):
1> exec sp_dropsrvrolemember 'COMPUTER-NAME\WindowsUser','sysadmin'
2> go
I haven't had good experiences with Microsoft support in the past, but this representative did an excellent job. He was located in India, but his English, troubleshooting skills, and product knowledge were refreshingly good. And he used his real name!
Linear Mode
