creating users in sql eypress

Hi,

wanted to add a login in via c# to sql server express.

Is this possible?

I treid this:

String connString = "data source=xtrlt027;Initial catalog=master;Integrated Security=true;";
SqlConnection conn= new SqlConnection();;
conn.ConnectionString = connString;

try
{
System.Console.WriteLine("Opening Connection...");
conn.Open();
System.Console.WriteLine("Connection opened!!!");
SqlCommand cmd = new SqlCommand("create login tommtk with Password='tomm2tomm'; use master; create user tommtk;", conn);
cmd.ExecuteNonQuery();
System.Console.WriteLine("Login created!!!");

}
catch (Exception deleteEx)
{
System.Console.WriteLine("SqlException Handle :{0}", deleteEx.ToString());
}
finally
{
conn.Close();
System.Console.WriteLine("Connection closed!!!");
}

But i get an exception, that there is an error near the keyword login and the keyword user.

So wahts wrong

can anybody help me?

Greetz
What is the exact error message ?

jens Suessmeyer.

http://www.sqlserver2005.de

|||Pretty sure each of you commands inside the cmd object need to be execute as separate statements/commands, not as one.|||Hi,

really looks like, that it is not possible to create logins in sql server express.
Downloaded sql studio management express and in an sql query it did not even recognized the key words.

The exception in my app was:

SQL syntax error near 'login'

So is it really not possible to create logins in sql express? i have the version that is included in visual Studio 2005 professional.

Greetz
|||It absolutely is possible can you please post the script you are trying to run in SSMS-E?|||

I assure you that it is possible to create Logins in SQL Express.

You need to separate certain commands for SQL to recognize them. All the CREATE commands fall into this category. You're code has them all run together as one big statement, which isn't going to work regardless of where you try to run them.

Here is some T-SQL that will accomplish what you're trying to do from the query window. You should be able to translate this to your code by simply running each statement separately. (e.g. Each thing between the GO keywords.)

USE master
GO

CREATE LOGIN tommtk
WITH PASSWORD = N'tomm2tomm'
GO

USE AdventureWorks
GO

-- For login tommtk, create a user in the database
CREATE USER tommtk
FOR LOGIN tommtk
WITH DEFAULT_SCHEMA = dbo
GO

-- Add user to the database owner role
EXEC sp_addrolemember N'db_owner', N'tommtk'
GO

I've actually changes the database context from master to AdventureWorks to create my user as I can't imagine why you were actually trying to create the User in master. If you really were tyring to create a user with permissions in master, just ignore the 'USE AdventureWorks' statement in your code. I've also added a call to give the User a specific database role, db_owner in this case, but that isn't required if you were not planning on assigning a specific role.

Regards,

Mike Wachal
SQL Express team

Mark the best posts as Answers!

|||Tried the following sql statement:

create login tommtk
with Password='tomm2tomm';
use master;
create user tommtk
|||Hi,

tried this statement in a developers edition:

create login tommtk with Password='tomm2tomm'; use master; create user tommtk

it worked fine, but not in the express edition. Or what edition ist it, included in VisualStudio 2005.

In the query window it does not even recognize the keyword login.

And in permission-properties i can not switch to mix of windows auth and sql server auth.
It is inactive

Greetz
|||Are you currently running windows Auth only?