Creating SQL Server Login programmatically






3.83/5 (8 votes)
Introduction I got this question from Daniweb C# forum, and of course whenever 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 scri
Introduction
I got this question from Daniweb C# forum, and of course whenever 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…
Using the Code
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
Here's the code to create SQL Server login (of type Windows User)
// connects to the local server
Server sqlServerInstance = new Server(
new Microsoft.SqlServer.Management.Common.ServerConnection(
new System.Data.SqlClient.SqlConnection(".")));
//initializes new login object on the server you connected by and
// we give the fullname of the login domain name \ user name
Login loginObj = new Login(sqlServerInstance, @"DomainName\UserName");
loginObj.DefaultDatabase = "Master"; // set the default database
// here's the type of login WindowsUser, WindowsGroup, SqlLogin, Certificate
// or AsymmetricKey.
// Be sure when you select WindowsUser to mention the domainname and username
loginObj.LoginType = LoginType.WindowsUser;
loginObj.Enable(); //to enable the login
loginObj.Create("password"); //set the password and create it on the server
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