Create new Login

  • Hi,

    I'm trying to create a new login for SQL Server 2005 using SMO in .Net C# here is the code:

    [font="Courier New"]ServerConnection conn = new ServerConnection();

    conn.ServerInstance = @"ANDREY\SQLEXPRESS";

    conn.LoginSecure = false;

    conn.Login = "sa";

    conn.Password = "p@ssw0rd";

    Server srv = new Server(conn);

    Login db_login = new Login(srv, "newuser");

    db_login.LoginType = LoginType.SqlLogin;

    db_login.Create("p@ssw0rd", LoginCreateOptions.None);

    db_login.DefaultDatabase = "web";

    db_login.Alter();

    /*StringCollection sc = db_login.Script();

    foreach (string s in sc)

    {

    Console.WriteLine(s);

    }*/

    db_login.AddToRole("sysadmin");

    Database db1 = srv.Databases["master"];

    User db_user1 = new User(db1, db_login.Name);

    db_user1.UserType = UserType.SqlLogin;

    db_user1.Create();

    Database db2 = srv.Databases["model"];

    User db_user2 = new User(db2, db_login.Name);

    db_user2.UserType = UserType.SqlLogin;

    db_user2.Create();

    Database db3 = srv.Databases["msdb"];

    User db_user3 = new User(db3, db_login.Name);

    db_user3.UserType = UserType.SqlLogin;

    db_user3.Create();

    Database db4 = srv.Databases["web"];

    User db_user4 = new User(db4, db_login.Name);

    db_user4.UserType = UserType.SqlLogin;

    db_user4.Create();

    Database db5 = srv.Databases["tempdb"];

    User db_user5 = new User(db5, db_login.Name);

    db_user5.UserType = UserType.SqlLogin;

    db_user5.Create();

    db_user5.AddToRole("db_owner");[/font]

    But it throws me the following exceptions:

    Unhandled Exception: Microsoft.SqlServer.Management.Smo.FailedOperationException: Create failed for

    User 'newuser'. ---> Microsoft.SqlServer.Management.Smo.PropertyNotSetException: Set property Login

    to accomplish this action.

    at Microsoft.SqlServer.Management.Smo.SqlSmoObject.GetPropValue(String propName)

    at Microsoft.SqlServer.Management.Smo.User.ScriptCreate(StringCollection createQuery, ScriptingOp

    tions so)

    at Microsoft.SqlServer.Management.Smo.SqlSmoObject.CreateImpl()

    --- End of inner exception stack trace ---

    at Microsoft.SqlServer.Management.Smo.SqlSmoObject.CreateImpl()

    at Microsoft.SqlServer.Management.Smo.User.Create()

    at TestApp1.Program.Main(String[] args) in C:\Documents and Settings\cantsyr\Desktop\TestApp1\Pro

    gram.cs:line 81

    And I'm trying to view the SQL Script and here is the script:

    /* For security reasons the login is created disabled and with a random password. */

    *9pE+?<↑ ?o', DEFAULT_DATABASE=[web], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POL

    ICY=ON

    ALTER LOGIN [newuser] DISABLE

    I think that the exceptions are thrown because of security reasons, please help me to accomplish this task.

    Thx

  • This is failing when you create the USERs, not when you are creatign the LOGIN. The USER create is failing for exactly the reason that the error message states: you have not set the .Login property of the new USER objects:

    Database db1 = srv.Databases["master"];

    User db_user1 = new User(db1, db_login.Name);

    db_user1.UserType = UserType.SqlLogin;

    db_user1.Login = db_login;

    db_user1.Create();

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • thx

  • Glad I could help.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply