Solved

SQL Stored Proc Permissions

Posted on 2013-06-25
8
288 Views
Last Modified: 2013-07-01
Hi Experts

I have a SQL Server that I need to run some stored procs on, however... I believe it to be a permissions issue as the Stored Proc creates successfully..

here it is..

-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE InsertGuests

@G_One_FirstName varchar (28) , 
@G_One_SecondName varchar (28), 
@G_Two_FirstName varchar (28) , 
@G_Two_SecondName varchar (28) ,
@Children decimal,
@Teens decimal,
@GuestType varchar (12),
@AddressLine1 varchar (64),
@AddressLine2 varchar (64),
@AddressLine3 varchar (64),
@Postcode varchar (10),
@Responded varchar (3) , 
@Attending Varchar (24) 
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	Insert Into dbo.WeddingGuest (G_One_FirstName, G_One_SecondName, G_Two_FirstName, 
	G_Two_SecondName, Children, Teens, GuestType, AddressLine1, AddressLine2,
	AddressLine3, Postcode, Responded, Attending)
	Values (@G_One_FirstName, @G_One_SecondName, @G_Two_FirstName, 
	@G_Two_SecondName, @Children, @Teens, @GuestType, @AddressLine1, @AddressLine2,
	@AddressLine3, @Postcode, 'No', 'TBC');
END
GO
 

Open in new window


What I am after is how do I get the sa account or create another account in order to allow the insert statement to be executed from an ASP.Net website from the Internet..

thanks

and yes.. I can connect into the database as have tested this already..
0
Comment
Question by:SimonPrice33
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 2
8 Comments
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 39276360
Easiest way is to create a user with DBO permissions to the DB in question.

add this to your webconfig:
<configuration>
  <connectionStrings>
<add name="ConnectionString" connectionString="Provider=SQLOLEDB;Data Source=<DB SERVER NAME>;Password=<SQL PASSWORD>;User ID=<SQL USER>;Initial Catalog=<THE DATABASE>;Connect Timeout=<TIME IN SECONDS>;" providerName="System.Data.OleDb" />
  </connectionStrings>

and in your code when specifying the Connection you would do:

SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ToString());

Hope that answers your question.  How are you connecting to the DB now?

have you executed the SP via an execute statement on the DB and does that work?
0
 

Author Comment

by:SimonPrice33
ID: 39276375
the connection to open the database connection works perfectly...  I will copy my code up in a second..

i didnt have .tostring on the end so maybe that will make a difference, however I already have it declared as a string ..
0
 

Author Comment

by:SimonPrice33
ID: 39276379
nope... didnt work..

Imports System.Data.SqlClient
Imports System.Data.Sql
Imports System.Data
Imports System.IO

Public Class guests
    Inherits System.Web.UI.Page


    Dim sqldr As SqlDataReader
    Dim sqlcmd As New SqlCommand
    Dim connstr As String = ConfigurationManager.ConnectionStrings("Database").ConnectionString.ToString
    Dim SqlConn As New SqlConnection
    Dim sqlstr As String = Nothing




    Protected Sub bttnSubmit_Click(sender As Object, e As System.EventArgs) Handles bttnSubmit.Click

        Dim G1Name1 As New SqlParameter("@G_One_FirstName", SqlDbType.NVarChar)
        G1Name1.Value = tbFirstGuestName1.Text
        Dim G1Name2 As New SqlParameter("@G_One_SecondName", SqlDbType.NVarChar)
        G1Name2.Value = tbFirstGuestName2.Text
        Dim G2Name1 As New SqlParameter("@G_Two_FirstName", SqlDbType.NVarChar)
        G2Name1.Value = tbSecondGuestName1.Text
        Dim G2Name2 As New SqlParameter("@G_Two_SecondName", SqlDbType.NVarChar)
        G2Name2.Value = tbSeconsGuestName2.Text
        Dim Children As New SqlParameter("@Children", SqlDbType.Decimal)
        Children.Value = ddlChildren.SelectedValue
        Dim Teens As New SqlParameter("@Children", SqlDbType.Decimal)
        Teens.Value = ddlTeens.SelectedValue
        Dim GuestType As New SqlParameter("@Teens", SqlDbType.NVarChar)
        GuestType.Value = ddlGuestType.SelectedValue
        Dim Address1 As New SqlParameter("@Address1", SqlDbType.NVarChar)
        Dim Address2 As New SqlParameter("@Address2", SqlDbType.NVarChar)
        Dim Address3 As New SqlParameter("@Address3", SqlDbType.NVarChar)
        Address1.Value = tbAddress1.Text
        Address2.Value = tbAddress2.Text
        Address3.Value = tbAddress3.Text
        Dim PostCode As New SqlParameter("@PostCode", SqlDbType.NVarChar)
        PostCode.Value = tbPostCode.Text




        Try

            With sqlcmd
                .CommandType = CommandType.StoredProcedure
                .CommandText = "InsertGuests"
                .Parameters.Add(G1Name1)
                .Parameters.Add(G1Name2)
                .Parameters.Add(G2Name1)
                .Parameters.Add(G2Name2)
                .Parameters.Add(Children)
                .Parameters.Add(Teens)
                .Parameters.Add(GuestType)
                .Parameters.Add(Address1)
                .Parameters.Add(Address2)
                .Parameters.Add(Address3)
                .Parameters.Add(PostCode)
                .Connection = SqlConn
            End With
            SqlConn.ConnectionString = connstr
            SqlConn.Open()
            sqlcmd.ExecuteNonQuery()
            SqlConn.Close()

            tbFirstGuestName1.Text = Nothing
            tbFirstGuestName2.Text = Nothing
            tbSecondGuestName1.Text = Nothing
            tbSeconsGuestName2.Text = Nothing
            ddlChildren.SelectedIndex = 0
            ddlTeens.SelectedIndex = 0
            ddlGuestType.SelectedIndex = 0
            tbAddress1.Text = Nothing
            tbAddress2.Text = Nothing
            tbAddress3.Text = Nothing
            tbPostCode.Text = Nothing
        Catch ex As Exception
            Response.Write("Record Not Saved " & e.ToString)
        End Try

    End Sub

    Protected Sub Page_Load(sender As Object, e As System.EventArgs) Handles Me.Load


        

    End Sub

    Protected Sub GridView1_SelectedIndexChanged(sender As Object, e As System.EventArgs) Handles GridView1.SelectedIndexChanged

    End Sub
End Class

Open in new window

0
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 39276430
For VB it shouldn't matter you're fine there.

Are you getting an exception?  

Just as a note you can do this
    Dim SqlConn Dim connstr As String = ConfigurationManager.ConnectionStrings("Database").ConnectionString.ToString
    Dim SqlConn As New SqlConnection
in one step:

 As SqlConnection = new SqlConnection(ConfigurationManager.ConnectionStrings("Database").ConnectionString.ToString()
)

Also just another note you're passing Nvarchars (vs varchar) in your code vs the stored proc . . . one is unicode the other is not and may be causing issues.


Besides that looks okay at first glance.  Leaving for today, let me know if you get an exception or what you're seeing/ not seeing.
0
 

Author Comment

by:SimonPrice33
ID: 39276431
could be the declarations.. will check them now...
0
 

Author Comment

by:SimonPrice33
ID: 39276447
changed the sqldbtype.nvarchar to sqldbtype.varchar but still get the same error

System.EventArgs

which is useless for me...
0
 

Accepted Solution

by:
SimonPrice33 earned 0 total points
ID: 39277642
it seems it didnt like the int values... changed them to varchar too and has gone through ok...
0
 

Author Closing Comment

by:SimonPrice33
ID: 39289349
.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

631 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