Go Premium for a chance to win a PS4. Enter to Win


vb.net, control if user has right to stored procedure on SQL Server

Posted on 2004-10-25
Medium Priority
Last Modified: 2010-04-23
Before calling a stored procedure I would like to check if the user has rights to it. I use Integrated security and SQL Server 2000, Windows 2003 Server. Or maybe there is a better way to avoid failure in the application if the user has not right to run this SP.

Thanks in advance.
Question by:lagneskog
  • 2
LVL 11

Expert Comment

ID: 12408870
Why not just execute it and handle the exception?

If you check if the user has the rights you would have to querry the DB 2 times to get the job done. One time in order to check if he is allowed and once in order to get the work done. This is one wasted trip in case you succeed.

If you just try to execute it and handle the exception properly then you have not wasted a trip to the sql server since you accomplish both questions with one action. An exception is not allwas an evil thing to generate. See it as some information service also. There are places where you can efficently work with errors and i think this is a valid scenario for this :)

Author Comment

ID: 12414864
Thanks rdrunnder

Could you please provide a smal snippet of vb.net code as you thought it would look like.
LVL 11

Accepted Solution

rdrunner earned 1000 total points
ID: 12415306

        Dim oRead As Data.SqlClient.SqlDataReader
        SqlCommand1.Parameters(1).Value = "HILAA"
            oRead = SqlCommand1.ExecuteReader()
        Catch ex As SqlClient.SqlException
            If ex.Number = 229 Then
                MsgBox("No rights on the SP....")
            End If
        Catch ex As Exception

        End Try

Featured Post


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: jpaulino
XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String (http://msdn.microsoft.com/en-us/library/system.string.aspx) Literal, only instead of starting and ending with w…
Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
Suggested Courses

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question