Author |
Topic |
GenSQL
Starting Member
14 Posts |
Posted - 2001-12-20 : 11:52:40
|
Article from MSDN Q218172PRB: Cannot Change SA Password in Enterprise Manager This has happend on one of our servers and I am notthat 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 |
|
|
GenSQL
Starting Member
14 Posts |
Posted - 2001-12-20 : 12:01:27
|
Yes, that is what the article describes, but it alsomentions that the incorrect null values will not be fixed.Of course I would like to fix that too...Thanks for the reply. |
|
|
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 |
|
|
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 mentionedarticle. Do you think I should go with what you previously posted?WORKAROUNDTo 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. |
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2001-12-20 : 12:20:25
|
Yep,Those SPs will fix the problem.-Chad |
|
|
GenSQL
Starting Member
14 Posts |
Posted - 2001-12-20 : 14:58:32
|
I was reading the BOL about:sp_change_users_login action, user, loginUse 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? |
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2001-12-20 : 15:32:32
|
use sp_changedbowner for sa/dbouse 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 |
|
|
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 public1 2 dbo2 2 guest3 0 INFORMATION_SCHEMA5 14 cbolyard6 2 iis16384 0 db_owner16385 0 db_accessadmin16386 0 db_securityadmin16387 0 db_ddladmin16389 0 db_backupoperator16390 0 db_datareader16391 0 db_datawriter16392 0 db_denydatareader16393 0 db_denydatawritersysxlogins-------------saBUILTIN\AdministratorsVR\cbolyardiisNULL |
|
|
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 |
|
|
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 accountsa can get in using the udl filesall accounts that I have tried via Visual Studio fail. sql error 18456 |
|
|
GenSQL
Starting Member
14 Posts |
Posted - 2001-12-20 : 16:47:08
|
udl files from my desktop connect finewhen working with new db projects in visual studioit brings up the same udl dialog, but failssaying DBNETLIB error SQL does not exist or access denied... |
|
|
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 |
|
|
GenSQL
Starting Member
14 Posts |
Posted - 2001-12-20 : 18:29:52
|
I am now working great from everywhere, butVisual Studio DB Projects.I have MS on the phone now...thanks guys !!! |
|
|
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! |
|
|
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! |
|
|
|