Click here to Skip to main content
Click here to Skip to main content

How to create Database User programmatically?

, 19 Nov 2009
Rate this:
Please Sign up or sign in to vote.
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

/// <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)

About the Author

Ramy Mahrous
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 PinmemberMember 448722920-Dec-09 21:35 
AnswerRe: how to create table sp and all function after create database PinmemberRamy Mahrous22-Dec-09 16:22 

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

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

| Advertise | Privacy | Mobile
Web04 | 2.8.140721.1 | Last Updated 19 Nov 2009
Article Copyright 2009 by Ramy Mahrous
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid