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

Create Error when Exporting to Excel

Is there a way to purposly throw an error when exporting to excel?
What I'm wanting is a way for reports to show up correctly in the browser,
but when users right click and export to excel, it will throw an error or
display garbled.
I posted a question early today regarding disabling right-click, but if that
is not possible, then this may be a work around.
(I still prefer the disabling of right click however ;).
Any suggestions?On Mar 1, 1:40 pm, labsRc...@.community.nospan
<labsRcoolcommunitynos...@.discussions.microsoft.com> wrote:
> Is there a way to purposly throw an error when exporting to excel?
> What I'm wanting is a way for reports to show up correctly in the browser,
> but when users right click and export to excel, it will throw an error or
> display garbled.
> I posted a question early today regarding disabling right-click, but if that
> is not possible, then this may be a work around.
> (I still prefer the disabling of right click however ;).
> Any suggestions?
As far as I know, there's not a way to accomplish this without
sacrificing exporting to the remaining formats. What are you trying to
accomplish in general?
Enrique Martinez
Sr. SQL Server Developer|||Thank you for responding!
This is the background and what we are trying to accomplish:
Background:
We are creating reports that we want certain users to be able to view
regional reports, and drill down to employee detail. The report was great.
However, we do not want users to be tempted to save these reports to their
computer. I already disabled the "export" options from the built in RS
toolbar.
But someone pointed out that users can still right-click with their mouse on
the RS report and select "Export to Excel" - which is undesirable.
Therefore, I thought of two possible solutions:
1) Find a way to capture the mouse right-click event to disable it.
(Like you can via javascript through static html pages, or by setting the
oncontextmenu=false.)
Those methods don't work with RS to disable the click, therefore I'm
searching for a different method to disable it..
OR
2) Another approach I thought of, if I can't disable the click, is find
someone to format the RS Reports, through hidden fields, or something, that
is not compatable with Excel, so that even if they do right click and export
- it won't show them what they want.
The reason for all this is that we don't want user personal information to
be saved on their personal computer.
Any suggestions would be appreciated, thanks!
"EMartinez" wrote:
> On Mar 1, 1:40 pm, labsRc...@.community.nospan
> <labsRcoolcommunitynos...@.discussions.microsoft.com> wrote:
> > Is there a way to purposly throw an error when exporting to excel?
> >
> > What I'm wanting is a way for reports to show up correctly in the browser,
> > but when users right click and export to excel, it will throw an error or
> > display garbled.
> >
> > I posted a question early today regarding disabling right-click, but if that
> > is not possible, then this may be a work around.
> >
> > (I still prefer the disabling of right click however ;).
> >
> > Any suggestions?
> As far as I know, there's not a way to accomplish this without
> sacrificing exporting to the remaining formats. What are you trying to
> accomplish in general?
> Enrique Martinez
> Sr. SQL Server Developer
>|||On Mar 1, 9:07 pm, labsRc...@.community.nospan
<labsRcoolcommunitynos...@.discussions.microsoft.com> wrote:
> Thank you for responding!
> This is the background and what we are trying to accomplish:
> Background:
> We are creating reports that we want certain users to be able to view
> regional reports, and drill down to employee detail. The report was great.
> However, we do not want users to be tempted to save these reports to their
> computer. I already disabled the "export" options from the built in RS
> toolbar.
> But someone pointed out that users can still right-click with their mouse on
> the RS report and select "Export to Excel" - which is undesirable.
> Therefore, I thought of two possible solutions:
> 1) Find a way to capture the mouse right-click event to disable it.
> (Like you can via javascript through static html pages, or by setting the
> oncontextmenu=false.)
> Those methods don't work with RS to disable the click, therefore I'm
> searching for a different method to disable it..
> OR
> 2) Another approach I thought of, if I can't disable the click, is find
> someone to format the RS Reports, through hidden fields, or something, that
> is not compatable with Excel, so that even if they do right click and export
> - it won't show them what they want.
> The reason for all this is that we don't want user personal information to
> be saved on their personal computer.
> Any suggestions would be appreciated, thanks!
> "EMartinez" wrote:
> > On Mar 1, 1:40 pm, labsRc...@.community.nospan
> > <labsRcoolcommunitynos...@.discussions.microsoft.com> wrote:
> > > Is there a way to purposly throw an error when exporting to excel?
> > > What I'm wanting is a way for reports to show up correctly in the browser,
> > > but when users right click and export to excel, it will throw an error or
> > > display garbled.
> > > I posted a question early today regarding disabling right-click, but if that
> > > is not possible, then this may be a work around.
> > > (I still prefer the disabling of right click however ;).
> > > Any suggestions?
> > As far as I know, there's not a way to accomplish this without
> > sacrificing exporting to the remaining formats. What are you trying to
> > accomplish in general?
> > Enrique Martinez
> > Sr. SQL Server Developer
Have you considered using a custom front-end ASP.NET application to
accomplish this, since it would be much more dynamic?
Enrique Martinez
Sr. SQL Server Developer|||I will try this, thanks!
"EMartinez" wrote:
> On Mar 1, 9:07 pm, labsRc...@.community.nospan
> <labsRcoolcommunitynos...@.discussions.microsoft.com> wrote:
> > Thank you for responding!
> >
> > This is the background and what we are trying to accomplish:
> >
> > Background:
> > We are creating reports that we want certain users to be able to view
> > regional reports, and drill down to employee detail. The report was great.
> > However, we do not want users to be tempted to save these reports to their
> > computer. I already disabled the "export" options from the built in RS
> > toolbar.
> >
> > But someone pointed out that users can still right-click with their mouse on
> > the RS report and select "Export to Excel" - which is undesirable.
> >
> > Therefore, I thought of two possible solutions:
> > 1) Find a way to capture the mouse right-click event to disable it.
> > (Like you can via javascript through static html pages, or by setting the
> > oncontextmenu=false.)
> > Those methods don't work with RS to disable the click, therefore I'm
> > searching for a different method to disable it..
> >
> > OR
> >
> > 2) Another approach I thought of, if I can't disable the click, is find
> > someone to format the RS Reports, through hidden fields, or something, that
> > is not compatable with Excel, so that even if they do right click and export
> > - it won't show them what they want.
> >
> > The reason for all this is that we don't want user personal information to
> > be saved on their personal computer.
> >
> > Any suggestions would be appreciated, thanks!
> >
> > "EMartinez" wrote:
> > > On Mar 1, 1:40 pm, labsRc...@.community.nospan
> > > <labsRcoolcommunitynos...@.discussions.microsoft.com> wrote:
> > > > Is there a way to purposly throw an error when exporting to excel?
> >
> > > > What I'm wanting is a way for reports to show up correctly in the browser,
> > > > but when users right click and export to excel, it will throw an error or
> > > > display garbled.
> >
> > > > I posted a question early today regarding disabling right-click, but if that
> > > > is not possible, then this may be a work around.
> >
> > > > (I still prefer the disabling of right click however ;).
> >
> > > > Any suggestions?
> >
> > > As far as I know, there's not a way to accomplish this without
> > > sacrificing exporting to the remaining formats. What are you trying to
> > > accomplish in general?
> >
> > > Enrique Martinez
> > > Sr. SQL Server Developer
> Have you considered using a custom front-end ASP.NET application to
> accomplish this, since it would be much more dynamic?
> Enrique Martinez
> Sr. SQL Server Developer
>|||It sounds to me like you are trying to lock this down too much... I
mean, if a user really wants to save the data, then they can...Select
all, copy and paste or print screen etc. There will always be a
workaround. Hence, I think the goal is unrealistic and you may just
have to trust the users to act responsibly to some degree.
However, if you really need to, you could create your own interface to
the reporting services web service as someone already suggested.
however, there will be many things you have to think of if you really
want to prevent saving the report data locally.