Create login and user

Hi,

I’m new in this.

I’m trying to create a user with the following code, but in the yellow part it throw an error saying the user can’t be created. Please if somebody can say what the problem is or correct the problem for me I will be thankful:

Public Sub CreateUser()

Dim conn As New ServerConnection("STATION01\SQLEXPRESS", "admin", "a9078564")

'conn.DatabaseName = "VideoDB"

Dim myServer As New Server(conn)

If Not myServer.Logins.Contains(TextBox2.Text) Then

Dim db_login As New Login(myServer, TextBox2.Text)

db_login.LoginType = LoginType.SqlLogin

db_login.Create(TextBox3.Text)

Dim sys_database As New Database(myServer, "VideoDB")

Dim db_user As New User(sys_database, TextBox2.Text)

db_user.Login = TextBox2.Text

db_user.Create()

If CheckBox18.Checked Then

db_login.AddToRole("securityadmin")

db_user.AddToRole("db_owner")

Else

Dim SelectPrivilege As New ObjectPermissionSet

SelectPrivilege.Select = True

Dim InsertPrivilege As New ObjectPermissionSet

InsertPrivilege.Insert = True

Dim UpdatePrivilege As New ObjectPermissionSet

UpdatePrivilege.Update = True

Dim DeletePrivilege As New ObjectPermissionSet

DeletePrivilege.Delete = True

Dim db_table As New Table

db_table.Schema = "admin"

db_table.Name = "customers"

If CheckBox1.Checked Then db_table.Grant(SelectPrivilege, TextBox2.Text)

If CheckBox2.Checked Then db_table.Grant(InsertPrivilege, TextBox2.Text)

If CheckBox3.Checked Then db_table.Grant(UpdatePrivilege, TextBox2.Text)

If CheckBox4.Checked Then db_table.Grant(DeletePrivilege, TextBox2.Text)

db_table.Name = "capturepicture"

If CheckBox1.Checked Then db_table.Grant(SelectPrivilege, TextBox2.Text)

db_table.Name = "items"

If CheckBox5.Checked Then db_table.Grant(SelectPrivilege, TextBox2.Text)

If CheckBox6.Checked Then db_table.Grant(InsertPrivilege, TextBox2.Text)

If CheckBox7.Checked Then db_table.Grant(UpdatePrivilege, TextBox2.Text)

If CheckBox8.Checked Then db_table.Grant(DeletePrivilege, TextBox2.Text)

If CheckBox9.Checked Then

db_login.AddToRole("securityadmin")

db_user.AddToRole("db_securityadmin")

db_user.AddToRole("db_accessadmin")

db_user.AddToRole("CreateUser")

End If

db_table.Name = "properties"

If CheckBox10.Checked Then db_table.Grant(SelectPrivilege, TextBox2.Text)

If CheckBox11.Checked Then db_table.Grant(InsertPrivilege, TextBox2.Text)

If CheckBox12.Checked Then db_table.Grant(UpdatePrivilege, TextBox2.Text)

If RadioButton1.Checked Then

db_user.AddToRole("TransactionsFullAccess")

Else

db_user.AddToRole("TransactionsRestrictedAccess")

End If

If CheckBox15.Checked Then db_user.AddToRole("Promotions")

If CheckBox18.Checked Then db_user.AddToRole("History")

End If

MsgBox(db_login.Script)

MsgBox(db_user.Script)

End If

End Sub

Regarding the samples located on http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=195387&SiteID=1 you did just fine beside the logon Type. Which error do you get during the creation of the user ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

The only thing I see wrong with your code is four lines above the highlighted line, where the code is:

db_login.Create(TextBox3.Text)

Now, everywhere else you're referring to TextBox2.Text, but in the create of the login you're using TextBox3.Text. It may be failing due to that mismatch. Otherwise the code looks correct.

|||

Hi,

In the textbox2 I refer the username in the textbox3 I refer the password.

I change the blue part and know it creates the user, but know the problem is in the tables privileges. It throws this error:

object Microsoft.SqlServer.Management.Smo.Table does not live under Server.

Public Sub CreateUser()

Dim conn As New ServerConnection("STATION01\SQLEXPRESS", "admin", "a9078564")

Dim myServer As New Server(conn)

If Not myServer.Logins.Contains(TextBox2.Text) Then

Dim db_login As New Login(myServer, TextBox2.Text)

db_login.LoginType = LoginType.SqlLogin

db_login.Create(TextBox3.Text)

Dim sys_database As Database = myServer.Databases("VideoDB")

Dim db_user As New User(sys_database, TextBox2.Text)

db_user.Login = TextBox2.Text

db_user.Create()

If CheckBox18.Checked Then

db_login.AddToRole("securityadmin")

db_user.AddToRole("db_owner")

Else

Dim SelectPrivilege As New ObjectPermissionSet

SelectPrivilege.Select = True

Dim InsertPrivilege As New ObjectPermissionSet

InsertPrivilege.Insert = True

Dim UpdatePrivilege As New ObjectPermissionSet

UpdatePrivilege.Update = True

Dim DeletePrivilege As New ObjectPermissionSet

DeletePrivilege.Delete = True

Dim db_table As New Table

db_table.Schema = "admin"

db_table.Name = "customers"

If CheckBox1.Checked Then db_table.Grant(SelectPrivilege, TextBox2.Text)

If CheckBox2.Checked Then db_table.Grant(InsertPrivilege, TextBox2.Text)

If CheckBox3.Checked Then db_table.Grant(UpdatePrivilege, TextBox2.Text)

If CheckBox4.Checked Then db_table.Grant(DeletePrivilege, TextBox2.Text)

db_table.Name = "capturepicture"

If CheckBox1.Checked Then db_table.Grant(SelectPrivilege, TextBox2.Text)

db_table.Name = "items"

If CheckBox5.Checked Then db_table.Grant(SelectPrivilege, TextBox2.Text)

If CheckBox6.Checked Then db_table.Grant(InsertPrivilege, TextBox2.Text)

If CheckBox7.Checked Then db_table.Grant(UpdatePrivilege, TextBox2.Text)

If CheckBox8.Checked Then db_table.Grant(DeletePrivilege, TextBox2.Text)

If CheckBox9.Checked Then

db_login.AddToRole("securityadmin")

db_user.AddToRole("db_securityadmin")

db_user.AddToRole("db_accessadmin")

db_user.AddToRole("CreateUser")

End If

db_table.Name = "properties"

If CheckBox10.Checked Then db_table.Grant(SelectPrivilege, TextBox2.Text)

If CheckBox11.Checked Then db_table.Grant(InsertPrivilege, TextBox2.Text)

If CheckBox12.Checked Then db_table.Grant(UpdatePrivilege, TextBox2.Text)

If RadioButton1.Checked Then

db_user.AddToRole("TransactionsFullAccess")

Else

db_user.AddToRole("TransactionsRestrictedAccess")

End If

If CheckBox15.Checked Then db_user.AddToRole("Promotions")

If CheckBox18.Checked Then db_user.AddToRole("History")

End If

MsgBox(db_login.Script)

MsgBox(db_user.Script)

End If

End Sub

|||

Hi,

Now it Works. Thanks for the help.

Code:

Public Sub CreateUser()

‘stablish connection with sqlserver authentication

Dim conn As New ServerConnection("STATION01\SQLEXPRESS", "admin", "a9078564")

Dim myServer As New Server(conn)

‘Verify if the user already exist

If Not myServer.Logins.Contains(TextBox2.Text) Then

‘Create the login

Dim db_login As New Login(myServer, TextBox2.Text)

db_login.LoginType = LoginType.SqlLogin

db_login.Create(TextBox3.Text, LoginCreateOptions.None)

Dim sys_database As Database = myServer.Databases("VideoDB")

‘Create the user

Dim db_user As New User(sys_database, TextBox2.Text)

db_user.Login = TextBox2.Text

db_user.UserType = UserType.SqlLogin

db_user.Create()

If CheckBox18.Checked Then

‘Grant roles

db_login.AddToRole("securityadmin")

db_user.AddToRole("db_owner")

Else

‘Grant privileges

Dim SelectPrivilege As New ObjectPermissionSet

SelectPrivilege.Select = True

Dim InsertPrivilege As New ObjectPermissionSet

InsertPrivilege.Insert = True

Dim UpdatePrivilege As New ObjectPermissionSet

UpdatePrivilege.Update = True

Dim DeletePrivilege As New ObjectPermissionSet

DeletePrivilege.Delete = True

Dim db_table As New Table(sys_database, "customers", "admin")

If CheckBox1.Checked Then db_table.Grant(SelectPrivilege, TextBox2.Text)

If CheckBox2.Checked Then db_table.Grant(InsertPrivilege, TextBox2.Text)

If CheckBox3.Checked Then db_table.Grant(UpdatePrivilege, TextBox2.Text)

If CheckBox4.Checked Then db_table.Grant(DeletePrivilege, TextBox2.Text)

db_table = New Table(sys_database, "capturepicture", "admin")

If CheckBox1.Checked Then db_table.Grant(SelectPrivilege, TextBox2.Text)

db_table = New Table(sys_database, "items", "admin")

If CheckBox5.Checked Then db_table.Grant(SelectPrivilege, TextBox2.Text)

If CheckBox6.Checked Then db_table.Grant(InsertPrivilege, TextBox2.Text)

If CheckBox7.Checked Then db_table.Grant(UpdatePrivilege, TextBox2.Text)

If CheckBox8.Checked Then db_table.Grant(DeletePrivilege, TextBox2.Text)

If CheckBox9.Checked Then

db_login.AddToRole("securityadmin")

db_user.AddToRole("db_securityadmin")

db_user.AddToRole("db_accessadmin")

db_user.AddToRole("CreateUser")

End If

db_table = New Table(sys_database, "properties", "admin")

If CheckBox10.Checked Then db_table.Grant(SelectPrivilege, TextBox2.Text)

If CheckBox11.Checked Then db_table.Grant(InsertPrivilege, TextBox2.Text)

If CheckBox12.Checked Then db_table.Grant(UpdatePrivilege, TextBox2.Text)

If RadioButton1.Checked Then

db_user.AddToRole("TransactionsFullAccess")

Else

db_user.AddToRole("TransactionsRestrictedAccess")

End If

If CheckBox15.Checked Then db_user.AddToRole("Promotions")

If CheckBox18.Checked Then db_user.AddToRole("History")

End If

End If

End Sub