| Author |
 Topic  |
|
|
GenSQL
Starting Member
USA
14 Posts |
 Posted - 12/20/2001 : 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
USA
1951 Posts |
Posted - 12/20/2001 : 11:56:54
|
run sp_changedbowner 'sa'
in all of your databases. That should fix the problem.
-Chad
|
 |
|
|
GenSQL
Starting Member
USA
14 Posts |
Posted - 12/20/2001 : 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.
|
|
|
|
chadmat
The Chadinator
USA
1951 Posts |
Posted - 12/20/2001 : 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
USA
14 Posts |
Posted - 12/20/2001 : 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.
|
|
|
|
chadmat
The Chadinator
USA
1951 Posts |
Posted - 12/20/2001 : 12:20:25
|
Yep,
Those SPs will fix the problem.
-Chad
|
|
|
|
GenSQL
Starting Member
USA
14 Posts |
Posted - 12/20/2001 : 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?
|
|
|
|
chadmat
The Chadinator
USA
1951 Posts |
Posted - 12/20/2001 : 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
|
|
|
|
GenSQL
Starting Member
USA
14 Posts |
Posted - 12/20/2001 : 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
|
|
|
|
chadmat
The Chadinator
USA
1951 Posts |
Posted - 12/20/2001 : 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
USA
14 Posts |
Posted - 12/20/2001 : 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
|
|
|
|
GenSQL
Starting Member
USA
14 Posts |
Posted - 12/20/2001 : 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...
|
|
|
|
izaltsman
A custom title
USA
1139 Posts |
Posted - 12/20/2001 : 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
USA
14 Posts |
Posted - 12/20/2001 : 18:29:52
|
I am now working great from everywhere, but
Visual Studio DB Projects.
I have MS on the phone now...
thanks guys !!!
|
|
|
|
izaltsman
A custom title
USA
1139 Posts |
Posted - 12/20/2001 : 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
USA
15557 Posts |
Posted - 12/20/2001 : 19:00:47
|
I often find that solves a lot of my SQL problems too! 
|
|
|
| |
Topic |
|
Help - sa dbo data a mess
