Create an SQL Server Login using C#

[sachintha81]

I have SQL Server Express 2008 installed in my computer. I need to create a Login to this using C# code. That is, I need to implement the same functionality that is performed when you create a Login to SQL Server using the wizard.
Also I need the program to be able to create a login to not just SQL EXpress 2008, but any other version of both Express and Standart editions.

Can this be done?
Help appreciated!

[Ramy Mahrous]

Yes, you can create Stored Procedure and pass any parameters you need to create login

[sachintha81]

Ramy, can you please show me how? A code example will be really helpful.
I'm new to this whole Sotred Procedures thing, some code will be appreciated.
Thanks!

[Ramy Mahrous]

If you've local copy of MSDN
Create Login using SQL statement
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/eb737149-7c92-4552-946b-91085d8b1b01.htm
Create Login using SMO
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/smo9mref/html/b327d041-e7c3-6396-8a72-200e9507f97b.htm

Online MSDN
Create Login using SQL statement
http://msdn.microsoft.com/en-us/library/ms189751.aspx
Create Login using SMO
http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.login.create.aspx

[sachintha81]

Ramy, thanks a lot for the quick reply.

I tried the SMO code as follows though I'm not sure if it's the correct way.

private void button1_Click(object sender, EventArgs e)
        {
            Login loginAcc = new Login(Environment.MachineName, "MyLogin");
            loginAcc.Create("abcd");            
        }

But that gives me a lot of errors, of which first one is as follows:

Assembly settings are used where the type 'Microsoft.SqlServer.Management.Sdk.Sfc.ISfcValidate' is not referenced. Please add a reference to the Assembly 'Microsoft.SqlServer.Management.Sdk.Sfc, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91'

Also, how to I used those Transact-SQL statements to create a login. I need to create a Windows based login. How do I run those?

Thanks in advance!

[Ramy Mahrous]

Seems you added wrong assemblies, you should add
Micrsoft.SqlServer.Smo and Microsoft.SqlServer.ConnectionInfo

I'm going to do a sample and in couple of hours I'll show you how to do it using SMO.

[sachintha81]

That'll be very nice of you mate.
Thanks.

[Ramy Mahrous]

Here you're as I promised
Again you need to add references
1- Microsoft.SqlServer.Smo
2- Microsoft.SqlServer.ConnectionInfo
3- Microsoft.SqlServer.SqlEnum

Server sqlServerInstance = new Server(
                new Microsoft.SqlServer.Management.Common.ServerConnection(
                new System.Data.SqlClient.SqlConnection("Data Source=.;Initial Catalog=Master;Integrated Security=True")));// your connection string I place mine for illustration..
            Login loginObj = new Login(sqlServerInstance, @"DomainName\UserName");
            loginObj.DefaultDatabase = "Master";
            loginObj.LoginType = LoginType.WindowsUser;
            loginObj.Create("password"); //set the password
//there're many properties to do some tasks related to Login object...

[sachintha81]

Thanks a lot!
But it still gives me an exception saying "Create faliled for login 'CF5\MyLogin'"

Here, I gave DomainName as my machine name and UserName as the new login name that I want to create.

What have I done wrong?

[Ramy Mahrous]

Check the Inner Exception, what it says?
It works with me...

[sachintha81]

The Inner Exception says :
"Windows NT user or group 'CF5\MyLogin' not found. Check the name again"

I'm confused, isn't "MyLogin" the name of the Login that I'm supposed to create? So, why is it saying it's not found?
The exception generates in the line

loginObj.Create("abc");

Let me see if I got all the parameters right again. Here's what I entered:

Data Source : The current account that I'm logged into the SQL Server.
Initial Catalog : The DB that I'm associating this program with.

Domain Name : My Machine Name.
User Name : The name of the login that I want to create.

loginObj.DefaultDatabase : The current DB this program is associated with.

Did I get those wrong?
Help is appreciated!
Regards

[Ramy Mahrous]

There's not user called CF5\MyLogin on your machine!!
Do you have this user?! Sure you don't!

[sachintha81]

So that has to be a user taht exists?
then what is the name of hte new login Im creating?

[Ramy Mahrous]

Sure it must exist!
How to tell SQL Server create windows authentication login to windows user which doesn't exist?!

what is the name of hte new login Im creating?

??????????????? I don't know, I can't tell you that piece of information?? And how can I!! If you need more details about SQL Login read post about it http://ramymahrous.wordpress.com/2009/04/28/how-to-create-sql-server-login-programmatically/