'sa' login

[rpeoplesjr]

I was under the impression that there is no difference between the 'sa' login and a login with sysadmin privileges. I was recently told that, that is not true. Can anyone tell me the reasons why the two logins would be different?

[rmiao]

I didn't see any permission difference. What was told?

[rpeoplesjr]

Here is the scoop. We aree running SQL Server 7.o with PeopleSoft Databases. PeopleSoft needs to access the database with 'sa' privileges. We had a user hack our 'sa' password, so we wanted to change it. Well, with PeopleSoft that is kind of a nightmare. So I asked PeopleSoft if we could use another login that has 'sa' privileges. Their senior DBA told me that you could not because SQL Server sees 'sa' different than a login with 'sa' privileges. He says that it has nothing to do with PeopleSoft. However, everything I have read and looked up says that there is no difference, but PeopleSofts DBA says that there is. He says that he has been a SQL Server DBA for 6 years and is MCSE certified. I just wanted a second opinion on this.

[rmiao]

Only difference as I know is sid for login, and sa has sid 0x01. Unless app checks sid, otherwise shouldn't have difference. You can double check with Microsoft.

[rpeoplesjr]

Thanks, that is what I thought.

[wileyjack]

My two cents worth....

I agree completely with rmiao in his statement but I'll go a step further as my experience on a similar problem may relate.

I have two SQL Servers supplied by vendors that I inherited to maintain. I quickly discovered that the sa account had NO PASSWORD. Well being conscious my a$$ could be liable for a breach of security I requested the vendor change the password. No problem for one but the other offered every excuse in the book as to why they could not.

Long story short, they had setup ODBC on MANY client computers that held the sa account info (WTF?). And they didn't find it "cost beneficial" to come in and have to change all the client DSNs.

With my management's support and our organization's dollars put on the line for them, we had them in shortly to make the changes. Touche'

[aoguy]

You can make a new login act just like SA, but that isn't your problem. You probably had a poor password.

It's easy to change your SA password in PeopleSoft if you follow the directions. Always experiment on a test database first.

-- You must be logged in the Bootstrap mode to execute this script
-- Change the OWNERID, ACCESSID and ACCESSPSWD Accordingly
update PSLOCK set OWNERID = 'SA'
update PSOPRDEFN set ACCESSID = 'SA' ACCESSPSWD = 'NEWPASS', OPERPSWD = '0000000000000000' where OPRTYPE = 0;
update PSACCESSPRFL set ACCESSID = 'SA', ACCESSPSWD = 'NEWPASS', VERSION = 0, ENCRYPTED = 0;
update PSOPTIONS set SPLITDATABASE = 'N';

grant_user *;
encrypt_password *;