Can't get into sa account

In SQL Server 2005, I renamed a database but it did not rename it in the
login default database. Since this is the default database for the sa
signon, I can't sign into the SQL Server. I am in a catch-22. I have only
one other login to the SQL Server and it also defaults to the same database
that apparently didn't get renamed :(.
Please help me get access to my SQL Server again,
TerryThis is a multi-part message in MIME format.
--030709040002030508020607
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
I answered this in microsoft.public.sqlserver.security.
--
*mike hodgson*
http://sqlnerd.blogspot.com
Terry wrote:
>In SQL Server 2005, I renamed a database but it did not rename it in the
>login default database. Since this is the default database for the sa
>signon, I can't sign into the SQL Server. I am in a catch-22. I have only
>one other login to the SQL Server and it also defaults to the same database
>that apparently didn't get renamed :(.
>Please help me get access to my SQL Server again,
>Terry
>
>
--030709040002030508020607
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>I answered this in microsoft.public.sqlserver.security.</tt><br>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2"><a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
Terry wrote:
<blockquote cite="mid600A72B6-BFBE-4475-BB1A-F6EACEED0CBC@.microsoft.com"
type="cite">
<pre wrap="">In SQL Server 2005, I renamed a database but it did not rename it in the
login default database. Since this is the default database for the sa
signon, I can't sign into the SQL Server. I am in a catch-22. I have only
one other login to the SQL Server and it also defaults to the same database
that apparently didn't get renamed :(.
Please help me get access to my SQL Server again,
Terry
</pre>
</blockquote>
</body>
</html>
--030709040002030508020607--

Can't get in to SQLServer 2005 express with a Privileged account

Bummer. I can't remember the SA password. I had setup a user account, but I can't change anything or add any new accounts using this login. I can't get in using the windows authentication method no matter how I am logged into this machine.

Any suggestions? I have never been able to use Windows Authentication. There must be something I'm missing here. I have spent hours and hours trying to get into this machine. I just want to replicate a database. This is very frustrating.

Thanks guys.

How are you connecting to SQL Server? On Express edition, Windows authentication should always work even if you are non-admin.

You can also try using Windows Authentication with a user account that is a machine admin.

Alternatively, members of this Windows Group: SQLServer2005MSSQLUSER$<machine_name>$<instance_name> should also have access. Please replace the <machine_name> and <instance_name> variables with the appropriate values for your installation. So if you add your Windows user to this group, you should also have access.

What error message are you seeing?

Sung

|||

It was telling me that I couldn't access the default database. I found where someone said to do:

sqlcmd -E -d master

That allowed me to get the SQLCMD prompt. I didn't know what to do after that. I found in the SQL Server 2005 Administrator's Pocket Consultant that you can make any user an administrator by issuing the command:

sp_addsrvrolemember 'login', 'sysadmin'

That did the trick. I was able to elevate the one login that I knew to sysadmin status. From there I had full access and privileges.

Thanks for your response.

can't drop login

can't drop login

icrosoft SQL Server, Error: 15141

The server principal owns an event notification and cannot be dropped.

notification services do not show anything, where else could it be ?

Did you opened up the Service Broker node in the database node ? There might be a Queue open for that principal that you want to delete.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||

Have you checked sys.server_event_notifications? Is there any notification there owned by the login?

Queues are owned by database principals, so this error is not caused by the presence of a queue.

Thanks
Laurentiu

|||

thanks a lot

sys.server_event_notifications showed a row related to user.

I droped notification and and was able to drop login

can't drop login

can't drop login

icrosoft SQL Server, Error: 15141

The server principal owns an event notification and cannot be dropped.

notification services do not show anything, where else could it be ?

Did you opened up the Service Broker node in the database node ? There might be a Queue open for that principal that you want to delete.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||

Have you checked sys.server_event_notifications? Is there any notification there owned by the login?

Queues are owned by database principals, so this error is not caused by the presence of a queue.

Thanks
Laurentiu

|||

thanks a lot

sys.server_event_notifications showed a row related to user.

I droped notification and and was able to drop login

can't deploy, SSRS 2K5

Hi kids:
I'm sure this is an easy one, sorry for posting a knob question...
Whenever I attempt to deploy a report, I get prompted for a login. I
don't remember setting SSRS to run as anyting except Local System, and
the configuration tool bears that out. When I click "deploy" on my
(very simple) report, it prompts me for a username & password. If I
click cancel, it declares the deployment a failure and stops. I have
tried entering my domain\user & password, user & password, sa &
password, but all fail.
Suggestions?
Thanks;
DuncanIs RS on your machine or a different one? It doesn't matter what RS uses to
run, it implements security and you have to have the correct rights to
deploy.
First, even without a report deployed you need to be able to see Report
Manager (the portal that ships with RS). Does that work for you?
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Duncan A. McRae" <google.com@.mcrae.ca> wrote in message
news:1187288669.801335.68890@.q3g2000prf.googlegroups.com...
> Hi kids:
> I'm sure this is an easy one, sorry for posting a knob question...
> Whenever I attempt to deploy a report, I get prompted for a login. I
> don't remember setting SSRS to run as anyting except Local System, and
> the configuration tool bears that out. When I click "deploy" on my
> (very simple) report, it prompts me for a username & password. If I
> click cancel, it declares the deployment a failure and stops. I have
> tried entering my domain\user & password, user & password, sa &
> password, but all fail.
> Suggestions?
> Thanks;
> Duncan
>

Can't Create and Connect to a new Database (.mdf file)

Unfortunately I was sent to this product by my Hosting Site since all I was trying to do was Create and Manage a User Login with authentication to my subsites in MS Frontpage. (rant: Why doesn't Frontpage support this technology?)

Anyway, I have now spent (wasted?) 8 hours downloading, installing, and tyring to configure SQL Express along with MS Visual Web Developer Express for ASP.NET 2.0 ... For the life of me I can't find detailed configuration informatin and setup information to build a SIMPLE database to connect to the VW product. Folk: this HAS to be simpler to do than what you present in the product! This is way too difficult to deal with compare with frontpage.

Is there a simple "how to" link someplace that is specific enough to preclude me from uninstalling this entire suite ?

Hi Cody,

Sounds like you've already put some effort into this, so I really hate to redirect you again, but I believe it will be worth your while to go over and check out ASP.net web. This site is all about web development using ASP.NET and Visual Web Developer. You will find a number of resources on the site which I think should help you. Of particular interest, I think, will be the How Do I video series and the ASP.NET 2.0 Quickstart tutorials. Both of these are available from the Learn tab if the links above don't work and contain samples about handling logins.

Hope this gets you moving in the right direction.

Regards,

Mike Wachal
SQL Express team

-
Mark the best posts as Answers!

Can't connect using sa login and don't have any other administrator logins

I connected successfully in SQL Server Management studio using the 'sa' login and changed the authentication mode to Windows NT because I could not connect using my windows login but now I can't get any connection at all because 'sa' is no longer trusted.

I changed the loginmode registry setting to 2 and rebooted my PC but this has made no difference.

Can anyone help me get the 'sa' login back?

Unless you explicitly removed the default privileges (members of sysadmin) to builtin\Administrators, you should be able to connect to SQL Server using a Windows account that is member of the machine administrator s group.

NOTE: If you are using Windows Vista, you probably have UAC enabled, and you will need to use a full administrator token before connecting to SQL Server in the way I described above. You can use the right-click menu in Windows to “Run as Administrator” in order to get an unrestricted token before connecting to SQL Server.

Let us know if this information helped.

-Raul Garcia

SDE/T

SQL Server Engine

|||

I am on Windows XP, the engine is SQL Server 2000 and I am using SQL Server 2005 Management Studio. The problem started when I upgraded the software we are developing to the latest version which I suspect may have removed the existing SQL Server logins which did have full rights. It also changed the login mode from Windows Authentication to System Administration. My big mistake was to change the mode back to Windows Authentication in SQL Server Management Studio before checking the other logins and it was then that the problems started. My windows username has administrative rights but I still couldn't connect to the server using windows login.

I tried just now changing the LoginMode in the registry back to 1 and starting windows again but I still can't log in with 'sa'. I did notice that the default user registry key is set to 'guest' but don't know if that makes any difference.

I think that I may have to resort to re-installing SQL Server 2005. I would prefer not to do that but It doesn't really matter if I lose my local database as I do have a copy of it elsewhere.

Can you think of any other solutions?

|||

Try switching the LoginMode value in the registry to 2 (if I remember correctly, 2 should mean "Mixed mode"), restart SQL Server and try to connect with the SA login again.

-Raul Garcia

SDE/T

SQL Server Engine

|||

Thanks, that worked perfectly.

FYI...

The reason it didn't work the first time is that I changed it in registry key:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.2\MSSQLServer\LoginMode

which I assume is the key for SQL Server Management Studio 2005 but I didn't realise that there was another key, i.e.

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\LoginMode

which would be for the SQL Server 2000 engine.

Anyway, I have learned my lesson now and am going to add my windows login.

Can't connect to SQl Server 2005 with sa Login

Hello,

I recently installed SQL2005 and SQL2000. I only created SA login permissions with Admin access to the database (There are no Windows Users that have access).

I then installed SQL2000 and it overwrote the "mixed connection mode" . whenever I try to connect with the sa login I get the following error

Login failed for user 'sa'. The user is not associated with a trusted SQL Server connection. (Microsoft SQL Server, Error: 18452)

The problem is explained in a fix http://support.microsoft.com/kb/269587

The only problem is that the fix requires access rights that I don't have. My only logins are "BuiltIn\Users" that have public access rights and then a "sa" login (that doesn't work) that has Admin rights within the db engine.

So my question is... Can I create an sysadmin account with windows authentication (for a normal Windows Admin Account) without having the proper rights within the database engine?

-Tom

I would recommend taking a look to the followign post:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1353448&SiteID=1

If the information there doesn't help you, please let us know, we will be glad to help.

Thanks,

-Raul Garcia

SDE/T

SQL Server Engine

Can't Connect to SQL 2005 Using Remote Desktop and Local Administr

I installed SQL 2005 Standard on Windows 2003 R2, and when I connect to the
server (no firewall involved) using Remote Desktop, I can't login to
Management Studio using the local administrator account. However, I can log
in using the domain administrator account. The local administrator account i
s
not locked out. I get the following error when trying to access Management
Studio using the local admin. account:
An error has occurred while establishing a connection to the server. When
connecting to SQL Server 2005, this failure may be caused by the fact that
under the default settings SQL Server does not allow remote connections.
(provider: Named Pipes Provider, error: 40 - Could not open a connection to
SQL Server) (.Net SqlClient Data Provider)
Any ideas?***UPDATE***
Actually, it has nothing to do with remote connectivity. I tried to log in
to Management Studio locally as the local administrator, and it still
wouldn't let me and gave the same error. As mentioned previously, the domain
admin. works.
Thanks.
"Marks70" wrote:

> I installed SQL 2005 Standard on Windows 2003 R2, and when I connect to th
e
> server (no firewall involved) using Remote Desktop, I can't login to
> Management Studio using the local administrator account. However, I can lo
g
> in using the domain administrator account. The local administrator account
is
> not locked out. I get the following error when trying to access Management
> Studio using the local admin. account:
> An error has occurred while establishing a connection to the server. When
> connecting to SQL Server 2005, this failure may be caused by the fact that
> under the default settings SQL Server does not allow remote connections.
> (provider: Named Pipes Provider, error: 40 - Could not open a connection t
o
> SQL Server) (.Net SqlClient Data Provider)
> Any ideas?