Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Help - sa dbo data a mess

Author  Topic 

GenSQL
Starting Member

14 Posts

Posted - 2001-12-20 : 11:52:40
Article from MSDN Q218172
PRB: Cannot Change SA Password in Enterprise Manager

This has happend on one of our servers and I am not
that familiar with the system tables.

How can I recover?

All praise the person that sends help!

chadmat
The Chadinator

1974 Posts

Posted - 2001-12-20 : 11:56:54
run sp_changedbowner 'sa'

in all of your databases. That should fix the problem.

-Chad

Go to Top of Page

GenSQL
Starting Member

14 Posts

Posted - 2001-12-20 : 12:01:27
Yes, that is what the article describes, but it also
mentions that the incorrect null values will not be fixed.

Of course I would like to fix that too...


Thanks for the reply.

Go to Top of Page

chadmat
The Chadinator

1974 Posts

Posted - 2001-12-20 : 12:07:25
I'm not sure what you mean by NULL values. sp_changedbowner 'sa' will map sa back to the dbo user. If you have more users that are orphaned (not mapped to a login) then you need to use sp_change_users_login to remap them to the correct login.

HTH
-Chad

Go to Top of Page

GenSQL
Starting Member

14 Posts

Posted - 2001-12-20 : 12:14:58
I am just a programmmer trying to be a DBA :)

This is what I am working with from the previously mentioned
article.
Do you think I should go with what you previously posted?

WORKAROUND
To work around this problem, use the sp_changedbowner stored procedure to change the owner of any databases owned by a login that does not exist on the new server to a login that does exist on the server.

NOTE: When this problem occurs, you can still use the sp_password stored procedure to change the SA password. However, this option is not recommended because it does nothing to resolve the situation of the missing DBOs.



Go to Top of Page

chadmat
The Chadinator

1974 Posts

Posted - 2001-12-20 : 12:20:25
Yep,

Those SPs will fix the problem.

-Chad

Go to Top of Page

GenSQL
Starting Member

14 Posts

Posted - 2001-12-20 : 14:58:32
I was reading the BOL about:

sp_change_users_login action, user, login

Use this procedure to link the security account for a user in the current database with a different login. If the login for a user has changed, use sp_change_users_login to link the user to the new login without losing the user’s permissions.

login cannot be sa, and user cannot be the dbo, guest, or INFORMATION_SCHEMA users.


Am I just out of luck?


Go to Top of Page

chadmat
The Chadinator

1974 Posts

Posted - 2001-12-20 : 15:32:32
use sp_changedbowner for sa/dbo

use sp_change_Users_login for any other user that is ophaned.

In other words, run sp_change_users_login 'report'

if dbo is output, run sp_changedbowner 'sa'

if another user(or list of users) is output use sp_change_users_login for them(Update_one for each one.)

if both dbo and other users are output, do both.

HTH
-Chad

Go to Top of Page

GenSQL
Starting Member

14 Posts

Posted - 2001-12-20 : 16:09:46
When I run:
EXEC sp_change_users_login 'Report'

No rows are returned....

Thanks for hanging in there with me!


sysusers
---------------------
0 0 public
1 2 dbo
2 2 guest
3 0 INFORMATION_SCHEMA
5 14 cbolyard
6 2 iis
16384 0 db_owner
16385 0 db_accessadmin
16386 0 db_securityadmin
16387 0 db_ddladmin
16389 0 db_backupoperator
16390 0 db_datareader
16391 0 db_datawriter
16392 0 db_denydatareader
16393 0 db_denydatawriter

sysxlogins
-------------
sa
BUILTIN\Administrators
VR\cbolyard
iis
NULL


Go to Top of Page

chadmat
The Chadinator

1974 Posts

Posted - 2001-12-20 : 16:15:28
Is that just for Master?

Try the report in each of your databases.

Can you still not change the sa password?

-Chad

Go to Top of Page

GenSQL
Starting Member

14 Posts

Posted - 2001-12-20 : 16:34:04
That is for each db.

I don't get this stuff yet...
I created some udl files for testing access.

I can get in using my Windows account
sa can get in using the udl files
all accounts that I have tried via Visual Studio fail.
sql error 18456

Go to Top of Page

GenSQL
Starting Member

14 Posts

Posted - 2001-12-20 : 16:47:08

udl files from my desktop connect fine

when working with new db projects in visual studio
it brings up the same udl dialog, but fails

saying DBNETLIB error SQL does not exist or access denied...



Go to Top of Page

izaltsman
A custom title

1139 Posts

Posted - 2001-12-20 : 18:21:41
Before you start setting up all sorts of udl files, it is probably a good idea to try to connect with Query Analyzer (using the same account information you plan on using in your udl). This will allow you to verify that all logins and db access rights are set up correctly in SQL. If you did followed Chad's advice, you should be able to login with no problems.

Then, if you want to figure out why your new udl files don't work -- compare them to the ones that do work (just open them side-by-side in Notepad and you will probably spot the differences right away).



Edited by - izaltsman on 12/20/2001 18:22:40
Go to Top of Page

GenSQL
Starting Member

14 Posts

Posted - 2001-12-20 : 18:29:52
I am now working great from everywhere, but
Visual Studio DB Projects.

I have MS on the phone now...


thanks guys !!!

Go to Top of Page

izaltsman
A custom title

1139 Posts

Posted - 2001-12-20 : 18:43:10
Weird! When they sort this out for ya, would you mind posting their diagnosis and resolution -- I'd love to know what to do if I come across this sort of thing.
Thanks!



Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2001-12-20 : 19:00:47
I often find that solves a lot of my SQL problems too!

Go to Top of Page
   

- Advertisement -