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
