Changed default table for sa signon and can't get authenticated

In SQL Server 2005, I renamed a database that is also the default database
for the sa signon and now I can't sign into the SQL Server, because although
I have the correct sa password, the default database is not there anymore so
I can't pass the authentication in order to change the default sa database t
o
one that exists. I am in a catch-22. Seems when I changed the database
name, it should have changed it in the default logins as well. At this
point, I have only one other login to the SQL Server and it also defaults to
the database that doesn't exist anymore .
Please help me get access to my SQL Server again,
TerryJust specify which DB to use when you connect with sqlcmd.exe (the
commandline SQL client that replaces osql.exe). Get a DOS prompt, cd to
the \Program Files\Microsoft SQL Server\90\Tools\Binn (if it's not on
your path), and run "sqlcmd -S <servername> -U sa -P <password> -d
master". (The -d option specifies the DB to use.) That should log you
into your SQL instance with your 'sa' login and start you off in the
master database. From there just change your default DB for 'sa' with
"exec sp_defaultdb 'sa', 'master'". Or you could just run the whole
command on the cmdline like:
sqlcmd -S <servername> -U sa -P <password> -d master -Q "exec
sp_defaultdb 'sa', 'master'"
This leads to a couple points:
1) why are you using your 'sa' login anyway (this should pretty much be
a last resort).
2) why did you need to change the default DB for this login.
3) why aren't you using trusted logins? (although the SQL login in SQL
2005 are more secure that with SQL 2000)
Anyway, good luck with it.
*mike hodgson*
http://sqlnerd.blogspot.com
Terry wrote:

>In SQL Server 2005, I renamed a database that is also the default database
>for the sa signon and now I can't sign into the SQL Server, because althoug
h
>I have the correct sa password, the default database is not there anymore s
o
>I can't pass the authentication in order to change the default sa database
to
>one that exists. I am in a catch-22. Seems when I changed the database
>name, it should have changed it in the default logins as well. At this
>point, I have only one other login to the SQL Server and it also defaults t
o
>the database that doesn't exist anymore .
>Please help me get access to my SQL Server again,
>Terry
>
>

Change user login

A users name has changed. In active directory the account has been renamed.
How do you associate this renamed NT account with the SQL login? The orgina
l
SID still exists so I cant add the new NT user name. Can I modify the
sysxuser table? : (Doug:
You can remove a login from the server, but leave the user with rights to
each database. Then, when you add the login again with the new name and gran
t
them access to specific databases, you can map that new Domain name to the
old user in the database.
For example: MyDomain\Bob is a login to the server and a user of the
database. I remove MyDomain\Bob as a login (leaving him as a user in the db)
.
Then I add MyDomain\Robert as a login to the server. When I assign him acces
s
as a user to the database, I map him to MyDomain\Bob.
It's not elegant, but it might work (no guarantees!)
Good luck.
Todd C
"doug" wrote:

> A users name has changed. In active directory the account has been rename
d.
> How do you associate this renamed NT account with the SQL login? The orgi
nal
> SID still exists so I cant add the new NT user name. Can I modify the
> sysxuser table? : (

Change Reporting Services server name

I renamed the computer name for the server running both SQL Server and
Reporting Services. I used sp_dropserver and sp_addserver to correct the
servername for SQL Server, but now I cannot use the Management Studio to
connect to the Reporting Server. I am running SQL 2005. Any ideas how to
fix the name without re-installing?
Thanks,
MattThe Reporting Services Configuration Manager tool has a Database Setup tab
that you can use to set the server name for the ReportServer database
repository.
Steve MunLeeuw
"Matthew Bando" <MatthewBando@.discussions.microsoft.com> wrote in message
news:D3CF59E6-B63C-4B7D-9C0B-DED2357DF561@.microsoft.com...
>I renamed the computer name for the server running both SQL Server and
> Reporting Services. I used sp_dropserver and sp_addserver to correct the
> servername for SQL Server, but now I cannot use the Management Studio to
> connect to the Reporting Server. I am running SQL 2005. Any ideas how to
> fix the name without re-installing?
> Thanks,
> Matt