Error when trying to create new SQL Server login via ado.Net

I get an error when I try to execute the following code using ADO.Net
in VB.Net:
conn.open
sqlString="CREATE LOGIN test WITH PASSWORD = '1qaz2wsx'"
command=new SqlCommand(sqlString)
command.connection = conn
command.ExecuteNonQuery()
The error occurs when executing the ExecuteNonQuery line and is:
"Line1: Incorrect syntax near 'LOGIN' "
The database connection is connected to the master database of SQL 2005
Express using the sa account. This line executes just fine in Sql
Server Studio Manger when connected as sa. Am I not allowed to create
database logins via ado.net?!?! I need to for my application.
Thx,
MarcusHi
It seems to be something wrong with quoatations. Print the sqlString and see
if you can execute it in QA
"holysmokes99" <holysmokes99@.hotmail.com> wrote in message
news:1143588567.138045.218660@.j33g2000cwa.googlegroups.com...
>I get an error when I try to execute the following code using ADO.Net
> in VB.Net:
> conn.open
> sqlString="CREATE LOGIN test WITH PASSWORD = '1qaz2wsx'"
> command=new SqlCommand(sqlString)
> command.connection = conn
> command.ExecuteNonQuery()
> The error occurs when executing the ExecuteNonQuery line and is:
> "Line1: Incorrect syntax near 'LOGIN' "
> The database connection is connected to the master database of SQL 2005
> Express using the sa account. This line executes just fine in Sql
> Server Studio Manger when connected as sa. Am I not allowed to create
> database logins via ado.net?!?! I need to for my application.
> Thx,
> Marcus
>|||I suggest you double check your connection string to make sure you are
connected to the SQL Express instance since this message indicates you are
connected to a pre-SQL 2000 instance. You can insert code like the example
below to query the instance version. My SQL Express instance returns
"9.00.1399.06".
command=new SqlCommand("SELECT SERVERPROPERTY('ProductVersion')")
Dim version as String = command.ExecuteScalar().ToString()
MessageBox.Show(version)
Hope this helps.
Dan Guzman
SQL Server MVP
"holysmokes99" <holysmokes99@.hotmail.com> wrote in message
news:1143588567.138045.218660@.j33g2000cwa.googlegroups.com...
>I get an error when I try to execute the following code using ADO.Net
> in VB.Net:
> conn.open
> sqlString="CREATE LOGIN test WITH PASSWORD = '1qaz2wsx'"
> command=new SqlCommand(sqlString)
> command.connection = conn
> command.ExecuteNonQuery()
> The error occurs when executing the ExecuteNonQuery line and is:
> "Line1: Incorrect syntax near 'LOGIN' "
> The database connection is connected to the master database of SQL 2005
> Express using the sa account. This line executes just fine in Sql
> Server Studio Manger when connected as sa. Am I not allowed to create
> database logins via ado.net?!?! I need to for my application.
> Thx,
> Marcus
>|||> It seems to be something wrong with quoatations. Print the sqlString and
> see if you can execute it in QA
No need to print the SQL statement here since it is in clear text in Marcus'
code snippet:
CREATE LOGIN test WITH PASSWORD = '1qaz2wsx'
Hope this helps.
Dan Guzman
SQL Server MVP
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eVeLQavUGHA.4436@.TK2MSFTNGP10.phx.gbl...
> Hi
> It seems to be something wrong with quoatations. Print the sqlString and
> see if you can execute it in QA
>
> "holysmokes99" <holysmokes99@.hotmail.com> wrote in message
> news:1143588567.138045.218660@.j33g2000cwa.googlegroups.com...
>|||Hi,Dan
Yep, I got it. thanks
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:uahPs5yUGHA.5900@.tk2msftngp13.phx.gbl...
> No need to print the SQL statement here since it is in clear text in
> Marcus' code snippet:
> CREATE LOGIN test WITH PASSWORD = '1qaz2wsx'
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:eVeLQavUGHA.4436@.TK2MSFTNGP10.phx.gbl...
>|||Oh, Jeesh, My bad!!! Dan you were spot on. I was connecting to an
instance of SQL Server 2000, and not Express. I have both on my machine
and forgot to reference it as "Marcus\SQLEXPRESS", instead using
"Marcus" only. CREATE LOGIN is new for SQL Server 2005 and thus I got a
syntax error when connecting to SQL 2000. Thanks for your insight.
On a sort-of-related note, is there a way for me to iterate through all
the SQL Server Express instances on the network? Using SQL-DMO, the
code below gives me the names of the servers, but some of the SQL
Express servers are named "[server_name]\SQLEXPRESS", while others are
just "[server_name]" (even though they do not have anything other that
SQL Express on them). If they were all suffixed with "SQLEXPRESS" then
that would be easy, but it doesn't seem to work that way.
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button2.Click
Dim oNames As SQLDMO.NameList
Dim oSQLApp As SQLDMO.Application
Dim oServer As SQLDMO.SQLServer2
oSQLApp = New SQLDMO.Application
oNames = oSQLApp.ListAvailableSQLServers
For i As Integer = 1 To oNames.Count
TextBox1.AppendText(oNames.Item(i) + Environment.NewLine)
Next
End sub
Thanks!
Marcus|||Check the following:
1) SQL Brower service is enabled and running
2) Port 1434 isn't blocked (e.g. Windows firewall)
3) The SQL Express instance is configured for remote connections
Hope this helps.
Dan Guzman
SQL Server MVP
"Marcus" <holysmokes99@.hotmail.com> wrote in message
news:1143659183.191249.143360@.e56g2000cwe.googlegroups.com...
> Oh, Jeesh, My bad!!! Dan you were spot on. I was connecting to an
> instance of SQL Server 2000, and not Express. I have both on my machine
> and forgot to reference it as "Marcus\SQLEXPRESS", instead using
> "Marcus" only. CREATE LOGIN is new for SQL Server 2005 and thus I got a
> syntax error when connecting to SQL 2000. Thanks for your insight.
> On a sort-of-related note, is there a way for me to iterate through all
> the SQL Server Express instances on the network? Using SQL-DMO, the
> code below gives me the names of the servers, but some of the SQL
> Express servers are named "[server_name]\SQLEXPRESS", while others are
> just "[server_name]" (even though they do not have anything other that
> SQL Express on them). If they were all suffixed with "SQLEXPRESS" then
> that would be easy, but it doesn't seem to work that way.
> Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As
> System.EventArgs) Handles Button2.Click
> Dim oNames As SQLDMO.NameList
> Dim oSQLApp As SQLDMO.Application
> Dim oServer As SQLDMO.SQLServer2
> oSQLApp = New SQLDMO.Application
> oNames = oSQLApp.ListAvailableSQLServers
> For i As Integer = 1 To oNames.Count
> TextBox1.AppendText(oNames.Item(i) + Environment.NewLine)
> Next
> End sub
>
> Thanks!
> Marcus
>|||Thanks, Dan. You were right again. On the machine with SQL Express
only, Windows Firewall was on. When I disabled it, the server name and
sql server instancen name came up when I ran the code above, i.e.
"training\sqlexpress", not just the server name as before when firewall
was active. Why did the server name come up at all then when the
firewall was enabled, when using the SQL-DMO code above?
Cheers,
Marcus|||> Why did the server name come up at all then when the
> firewall was enabled, when using the SQL-DMO code above?
IIRC, before SQL Server 2000, network broadcast packets sent to facilitate
server enumeration. According to Perter's blog
(http://blogs.msdn.com/sql_protocols.../24/473502.aspx), legacy
behavior can show default instances even with UDP 1434 blocked.
Hope this helps.
Dan Guzman
SQL Server MVP
"Marcus" <holysmokes99@.hotmail.com> wrote in message
news:1143736505.471840.78380@.i39g2000cwa.googlegroups.com...
> Thanks, Dan. You were right again. On the machine with SQL Express
> only, Windows Firewall was on. When I disabled it, the server name and
> sql server instancen name came up when I ran the code above, i.e.
> "training\sqlexpress", not just the server name as before when firewall
> was active. Why did the server name come up at all then when the
> firewall was enabled, when using the SQL-DMO code above?
> Cheers,
> Marcus
>sql

Error when changing sa password or when trying to access DB via sa

Hi,
Two weird things with the sa account, and I found a solution on
Microsoft.com, but it's not working.
First, when I go to change the SA password in Enterprise Manager, I
get the following error:
Error 21776: [SQL-DMO] The name 'dbo' was not found in the Users
collection. If the name is a qualified name, use [] to separate
various parts of the name, and try again.
So, I checked the knowledgebase and found this:
http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q218/1/72.ASP&NoWebContent=1#appliesto
This is the exact issue I'm having, but when I run: sp_changedbowner
sa
... I get the following message in Query Analyzer:
Server: Msg 15109, Level 16, State 1, Procedure sp_changedbowner, Line
22
Cannot change the owner of the master database.
So, I searched for this error and found this:
http://support.microsoft.com/default.aspx?scid=kb;en-us;328173
Catch-22... I need to change the Master DB to be owned by 'sa', yet it
won't let me change it since it's not already owned. Am I missing
something? This did happen just after I restored a database, just as
discribed in the first support doc, but it gives no exact syntax on
what to plug into Query Analyzer to resolve the issue, other then
using the sp_changedbowner stored procedure. And I must be using it
incorrectly to get the error, or it's another bug in MS SQL.
Can someone point me to the right direction? What should be an easy
back-up and restore is turning into a multi-hour ordeal.
Thanks,
Alex.If your a local admin or domain admin connect to the SQL Server using Query
Analyser and ask for a 'WinNT Authentication' then issue the following
command
A. Change the password of a login without a former password
This example changes the password for the login Victoria from a NULL
password to "B3r12-36".
EXEC sp_password NULL, 'B3r12-36', 'Victoria'
In EnterPrise manager Delete and Re-Register the troublesome server, when
registering use the new password.
--
HTH
Ryan Waight, MCDBA, MCSE
"Alex" <alex@.totallynerd.com> wrote in message
news:2ba4b4eb.0310270835.36d6e245@.posting.google.com...
> Hi,
> Two weird things with the sa account, and I found a solution on
> Microsoft.com, but it's not working.
> First, when I go to change the SA password in Enterprise Manager, I
> get the following error:
> Error 21776: [SQL-DMO] The name 'dbo' was not found in the Users
> collection. If the name is a qualified name, use [] to separate
> various parts of the name, and try again.
> So, I checked the knowledgebase and found this:
>
http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q218/1/72.ASP&NoWebContent=1#appliesto
> This is the exact issue I'm having, but when I run: sp_changedbowner
> sa
> ... I get the following message in Query Analyzer:
> Server: Msg 15109, Level 16, State 1, Procedure sp_changedbowner, Line
> 22
> Cannot change the owner of the master database.
> So, I searched for this error and found this:
> http://support.microsoft.com/default.aspx?scid=kb;en-us;328173
> Catch-22... I need to change the Master DB to be owned by 'sa', yet it
> won't let me change it since it's not already owned. Am I missing
> something? This did happen just after I restored a database, just as
> discribed in the first support doc, but it gives no exact syntax on
> what to plug into Query Analyzer to resolve the issue, other then
> using the sp_changedbowner stored procedure. And I must be using it
> incorrectly to get the error, or it's another bug in MS SQL.
> Can someone point me to the right direction? What should be an easy
> back-up and restore is turning into a multi-hour ordeal.
> Thanks,
> Alex.|||Yes, you cannot change database owner to MASTER.
If you run sp_helpuser on your restored database, should
show the "LoginName" as NULL if 'sa' do not own it.
You only needed to run sp_changedbowner on your restored
database. -->
use <restored_db>
go
sp_changedbowner 'sa'
go