SQL works, SP doesn't (RESOLVED)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

SQL works, SP doesn't (RESOLVED)

Author

Topic

Pinto
Aged Yak Warrior

590 Posts

Posted - 2006-03-02 : 09:45:34

If I run the following SQL it works. if I run the sp I get an error message saying that it cannot find the stored procedure. I have granted permissions to the sp and the owner is the same as all the other sp's. The only difference is that vweEmpDetails is in another sql database but on the same server. Any suggestions gratefully accepted.

I can populate the dataset by r/h clicking on the adapter and previewing the data, that works fine.

'Populate the DataSet THIS WORKS
Me.DsPCCCoreUsers11.Clear()

Dim strSQL As String = "SELECT *, EmpFirstname + ' ' + EmpSurname as FName FROM vweEmpDetails WHERE (EmpPayrollPK = '" & Me.txtPaynumber.Text & "') and (EmpSurname = '" & Me.txtSurname.Text & "')"
Dim myDataAdapter As SqlDataAdapter = New SqlDataAdapter(strSQL, strConnPCCCore)

myDataAdapter.Fill(Me.DsPCCCoreUsers11, "vweEmpDetails")

************THIS DOESN'T

Dim MySQL As String = "spRB_GetUserPCCCore"
Dim MyConn As New SqlConnection(strConnPCCCore)
Dim Cmd As New SqlCommand(MySQL, MyConn)
Cmd.CommandType = CommandType.StoredProcedure
Cmd.Parameters.Add(New SqlParameter("@strSurname", Me.txtSurname.Text))
Cmd.Parameters.Add(New SqlParameter("@intPaynumber", Me.txtPaynumber.Text))

SqlDataAdapter1.SelectCommand = Cmd
Me.DsPCCCoreUsers11.Clear()

SqlDataAdapter1.Fill(Me.DsPCCCoreUsers11, "vweEmpDetails")

Kristen
Test

22859 Posts

Posted - 2006-03-02 : 09:54:41

"The only difference is that vweEmpDetails is in another sql database but on the same server"

User will have to have explicit permissions to that object AFAIK (i.e. Execute permissions on the SProc won't be enough).

If that is a security problem then you could have an SProc on the other database that returns the [relevant] data from vweEmpDetails - and giving the user execute permission on that SProc TOO should do the trick.

I don't know anything about database chaining, but that may let you set up permission to cover this sort of scenario.

Kristen

Pinto
Aged Yak Warrior

590 Posts

Posted - 2006-03-02 : 10:03:00

Sorry for my ignorance, but why does the SQL statement work ?

Kristen
Test

22859 Posts

Posted - 2006-03-02 : 10:06:16

My take would be that you are using Query Analyser, and you logged on as someone with high enough permission to have Table permissions on the other DB when you ran the SQL statement.

But then if you executed the SProc in the same QA session too those same permissions would still be in place ...

If the raw SQL Statement was in QA (logged in as SA or similar) and the SProc execute was in your application (logged on with [lesser] End User Permissions) then I'm more sure of my assumptions!!!

Kristen

Pinto
Aged Yak Warrior

590 Posts

Posted - 2006-03-02 : 10:09:20

Both are in my asp.net application in the btnLogin.Click event. If I run the sp I get the error message, if I comment that code out and run the sql statement it works !

At least I can get it to work with the sql but I use sp for everything else.

Kristen
Test

22859 Posts

Posted - 2006-03-02 : 10:20:25

Could it be that the user who executed the CREATE PROCEDURE (or ALTER PROCEDURE) stuff did NOT have permissions for vweEmpDetails?

An SProc has permissions for Tables etc. of the user that created the Sproc, rather than those of the user that is running the SProc.

Doesn't stack up for me on a table/view in another DB ... but lets just check this first pls.

Kristen

Pinto
Aged Yak Warrior

590 Posts

Posted - 2006-03-02 : 10:29:01

I created the sp and executed it. I cannot run it in QA either - same message. I didn't create vweEmpDetails. This is a view of all staff that IT use to validate logins on all applications. I will get someone to check its permissions.

Thanks

Kristen
Test

22859 Posts

Posted - 2006-03-02 : 11:47:38

You may be able to see the permissions with:

MyOtherDatabase.dbo.sp_helprotect 'vweEmpDetails'

Kristen

Pinto
Aged Yak Warrior

590 Posts

Posted - 2006-03-03 : 05:32:59

With help from my colleagues I needed to change the sp as follows. I also had the connection string pointing to where the data was, NOT to where the sp was.

CREATE Procedure [dbo].[spRB_GetUserPCCCore]

@strSurname nvarchar (100),
@intPaynumber numeric
as
Select *, EmpFirstname + ' ' + EmpSurname as FName
from OPENDATASOURCE('SQLOLEDB','User ID=PCCCOREUser;Password=PasswordHere').PCCCore.[dbo].vweEmpDetails
where EmpSurname = @strSurname and
EmpPayrollPK = @intPaynumber

GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources