This blog is subject the DISCLAIMER below.

Tuesday, April 28, 2009

How to create SQL Server Login programmatically?

Q. How to create SQL Server Login programmatically?

I got this question from Daniweb C# forum, and of course when I need to do something against SQL Server object from .net I go to SMO (SQL Server Management Objects) which provides a great functionalities to do anything with SQL Server instead of going on SSMS and create some scripts then embedding them into stored procedure then call it…. off…

Anyway to avoid showing how much I’m talkative…

Create any C# project type (Windows, Class Library or even Console one), add reference to Microsoft.SqlServer.Smo, Microsoft.SqlServer.ConnectionInfo
and 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.Enable();             loginObj.Create("password"); //set the password             //there're many properties to do some tasks related to Login object...

If you used LoginType.WindowsUser, be sure to provide valid windows username and if you aren’t on Domain use the machine name instead.



If you need to create SQL login use LoginType.SqlLogin…



You can explore Login class more on http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.login.create.aspx

3 comments:

Shady Mostafa said...

بسم الله

Mohamed Meshref said...

By the way, you don't need to provide a connection object, empty constructor will go with windows auth and default server

Ramy Mahrous said...

Thanks, Meshref :)