SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Help - sa dbo data a mess
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

GenSQL
Starting Member

USA
14 Posts

Posted - 12/20/2001 :  11:52:40  Show Profile  Reply with Quote
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

USA
1974 Posts

Posted - 12/20/2001 :  11:56:54  Show Profile  Visit chadmat's Homepage  Reply with Quote
run sp_changedbowner 'sa'

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

-Chad

Go to Top of Page

GenSQL
Starting Member

USA
14 Posts

Posted - 12/20/2001 :  12:01:27  Show Profile  Reply with Quote
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

USA
1974 Posts

Posted - 12/20/2001 :  12:07:25  Show Profile  Visit chadmat's Homepage  Reply with Quote
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

USA
14 Posts

Posted - 12/20/2001 :  12:14:58  Show Profile  Reply with Quote
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

USA
1974 Posts

Posted - 12/20/2001 :  12:20:25  Show Profile  Visit chadmat's Homepage  Reply with Quote
Yep,

Those SPs will fix the problem.

-Chad

Go to Top of Page

GenSQL
Starting Member

USA
14 Posts

Posted - 12/20/2001 :  14:58:32  Show Profile  Reply with Quote
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

USA
1974 Posts

Posted - 12/20/2001 :  15:32:32  Show Profile  Visit chadmat's Homepage  Reply with Quote
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

USA
14 Posts

Posted - 12/20/2001 :  16:09:46  Show Profile  Reply with Quote
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

USA
1974 Posts

Posted - 12/20/2001 :  16:15:28  Show Profile  Visit chadmat's Homepage  Reply with Quote
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

USA
14 Posts

Posted - 12/20/2001 :  16:34:04  Show Profile  Reply with Quote
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

USA
14 Posts

Posted - 12/20/2001 :  16:47:08  Show Profile  Reply with Quote

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

USA
1139 Posts

Posted - 12/20/2001 :  18:21:41  Show Profile  Send izaltsman an AOL message  Send izaltsman an ICQ Message  Reply with Quote
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

USA
14 Posts

Posted - 12/20/2001 :  18:29:52  Show Profile  Reply with Quote
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

USA
1139 Posts

Posted - 12/20/2001 :  18:43:10  Show Profile  Send izaltsman an AOL message  Send izaltsman an ICQ Message  Reply with Quote
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

USA
15670 Posts

Posted - 12/20/2001 :  19:00:47  Show Profile  Visit robvolk's Homepage  Reply with Quote
I often find that solves a lot of my SQL problems too!

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000