Re: Error: 18456, Severity: 14, State: 16

No, sa has by default access to everything, including the internal data.

Jens K. Suessmeyer.

---
http://www.sqlserver2005.de
---

"Alastair" <alastair_anderson@xxxxxxxxxxx> wrote in message news:1174779869.212689.303860@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
On 23 Mar, 21:26, "Jens K. Suessmeyer" <J...@NoSpamhere-
sqlserver2005.de> wrote:
Normally this should work, did you try conencting with the same syntax that
you are using in the batch file via an interactive commandprompt ? Did you
try to leave out the quotes ?

Jens K. Suessmeyer.

---http://www.sqlserver2005.de
---

"Alastair" <alastair_ander...@xxxxxxxxxxx> wrote in message

news:1174655306.297982.325260@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx



> On 23 Mar, 13:00, "Jens K. Suessmeyer" <J...@NoSpamhere-
> sqlserver2005.de> wrote:
>> Does the password have any blank spaces or double quotes ?

>> Jens K. Suessmeyer.

>> ---http://www.sqlserver2005.de
>> ---

>> "Alastair" <alastair_ander...@xxxxxxxxxxx> wrote in message

>>news:1174642161.471601.110030@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

>> > Hi All,

>> > I am getting the following error in the ERRORLOG File when I am >> > trying
>> > to connect to SQL Server 2005 Express Edition from the command line.

>> > 2007-03-23 07:30:05.54 Logon Error: 18456, Severity: 14, >> > State:
>> > 16.
>> > 2007-03-23 07:30:05.54 Logon Login failed for user 'sa'.
>> > [CLIENT: xx.xx.xx.xx]

>> > I have a fresh install of SQL Server on my local machine, which is
>> > probably the source of my angst, but I am trying to create a >> > database
>> > from scratch through using a batch file that is run from the command
>> > line, the batch file looks like this:

>> > <snip>
>> > SQLCMD -U %USR% -P %PWD% -S %SVR% -d %DBF% -i CreateDatabase.sql
>> > <snip>

>> > The values of %USR% and %PWD% are correct as I frequently log into
>> > Management Studio with the details, %SVR% is "<ComputerName>
>> > \SQLEXPRESS,1433" and %DBF% is <DatabaseName>. The contents of
>> > CreateDatabase.sql when run in Management Studio successfully >> > creates
>> > the database that I require so I know the error doesn't lie there.

>> > The command line error that is showing reads as the following:

>> > Msg 4060, Level 11, State 1, Server <ComputerName>\SQLEXPRESS, Line >> > 1
>> > Cannot open database "<DatabaseName>" requested by the login. The
>> > login failed.
>> > Msg 18456, Level 14, State 1, Server <ComputerName>\SQLEXPRESS, Line >> > 1
>> > Login failed for user 'sa'.

>> > I have a feeling that it is related to 'sa' not having the correct
>> > permissions with regards to logging into or creating a database from
>> > scratch, but I find it odd that I can login as 'sa' in Management
>> > Studio and create the database from there, but when I try it from >> > the
>> > command-line (despite having TCP/IP enabled) it won't let me connect
>> > to create a database.

>> > Is there something I am missing here? I have been banging my head >> > off
>> > of walls for days over this and am unsure of the next steps to take.

>> > If any of this screams "why the hell are you doing this?" then >> > please
>> > let me know as at the moment I am punching in the dark with this
>> > problem, I have searched Usenet, Google Groups, the Web and although >> > I
>> > have found potential solutions to this particular problem I am >> > having,
>> > nothing I have done so far has actually managed to fix the issue.

>> > Related articles I have followed are:

>> > Login failed for user 'x'
>> >http://msdn2.microsoft.com/en-us/library/ms366351.aspx

>> > Change Server Authentication Mode
>> >http://msdn2.microsoft.com/en-us/library/ms188670.aspx

>> > Upgrading MSDE 2000 to SQL Server 2005 Express [Enabling network
>> > protocols section]
>> >http://www.microsoft.com/technet/prodtechnol/sql/2005/msde2sqlexpress...

>> > NOTE: I just listed these articles to show what path I am currently
>> > looking at with regards to this problem, but again I stress I have
>> > found nothing yet to get round this problem, and any further >> > articles
>> > or direction would be much appreciated.

>> > Yours Distressingly

>> > Alastair- Hide quoted text -

>> - Show quoted text -

> The only double quotes that it contains are those that surround the
> password in the .bat file.

> Should these be single quotes?- Hide quoted text -

- Show quoted text -

I've done some further hunting around for solutions, could it
potentially be related to "sa" not having any permissions in the
SYS.DATABASE_PERMISSIONS table?


.

Relevant Pages