Click here to Skip to main content
15,883,901 members
Articles / Database Development / SQL Server
Tip/Trick

How to create Database User programmatically?

Rate me:
Please Sign up or sign in to vote.
3.67/5 (5 votes)
19 Nov 2009CPOL 21.4K   13   2
Well... we need to assign a server login to has access on a database, let's say the SQL Server Login we had created programmatically should has access on database "Database1" and for some reasons we should do that programmatically Well. Create any C# project type (Windows, Class Library or even Cons

Well... we need to assign a server login to has access on a database, let's say the SQL Server Login we had created programmatically should has access on database "Database1" and for some reasons we should do that programmatically Well. Create any C# project type (Windows, Class Library or even Console one), add reference to Microsoft.SqlServer.Smo, and Microsoft.SqlServer.ConnectionInfo

C#
/// <summary>
/// Creates database user
/// </summary>
private void CreateDatabaseUser(Database database, String username)
{
    // initializes new User object and we say to which database it belongs
    // and its name
    User sqlServerUser = new User(datawarehouseDatabase, username); 
    sqlServerUser .UserType = UserType.SqlLogin; //SqlLogin not anything else
    //associated the user to login name, login name should be valid login name
    sqlServerUser .Login = "login_name"; 
    // here's we create the user on the database and till now the user
    // don't have any permission on database objects
    sqlServerUser .Create();
    //or any role like db_databasereader, db_databasewriter,...
    sqlServerUser .AddToRole("db_owner"); 
}

P.S: There's catastrophic mistake in Microsoft documentation of User.Login as they documented it as ReadOnly field although it's not.

sqllogin

How we can call the above method?

Server sqlInstance = new Server(
new Microsoft.SqlServer.Management.Common.ServerConnection(
new System.Data.SqlClient.SqlConnection("."))); //connects to the local server
//initialize new object from database  adventureworks
Database database = sqlInstance.Databases["adventureworks"]; 
//creates user RamyMahrous on database adventureworks
CreateDatabaseUser(database, "RamyMahrous");

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer LINK Development
Egypt Egypt
Business Intelligence Developer at LINK Development which is a wholly subsidiary of LINKdotNET, graduated from Faculty of Computers and Information | Helwan University, Cairo, Egypt | 2003 - 2007, Computer Science as a Major, he has a passion for community, latest knowledge delivering and Q&A forums.

Comments and Discussions

 
Questionhow to create table sp and all function after create database Pin
Member 448722920-Dec-09 21:35
Member 448722920-Dec-09 21:35 
AnswerRe: how to create table sp and all function after create database Pin
Ramy Mahrous22-Dec-09 16:22
Ramy Mahrous22-Dec-09 16:22 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.