Create new Login

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » CLR Integration and Programming. » Create new Login

Create new Login

Rate Topic

Display Mode

Topic Options

Author

Message

andrei_cantsyr

Posted Friday, May 23, 2008 5:30 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, June 23, 2008 12:32 PM
Points: 2, Visits: 9

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

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");

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

Post #505760

RBarryYoung

Posted Wednesday, June 04, 2008 1:24 PM

SSCrazy Eights

Group: General Forum Members
Last Login: Wednesday, June 12, 2013 11:17 AM
Points: 9,855, Visits: 9,376

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();

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc. "Performance is our middle name."

Post #511725

andrei_cantsyr

Posted Monday, June 23, 2008 12:34 PM

Forum Newbie

Group: General Forum Members
Last Login: Monday, June 23, 2008 12:32 PM
Points: 2, Visits: 9

thx

Post #522125

RBarryYoung

Posted Monday, June 23, 2008 2:29 PM

SSCrazy Eights

Group: General Forum Members
Last Login: Wednesday, June 12, 2013 11:17 AM
Points: 9,855, Visits: 9,376

Glad I could help.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc. "Performance is our middle name."

Post #522180

« Prev Topic | Next Topic »

Permissions