Odd permissions error with ADO and VBA

[DjangMan]

I've got a VBA macro that connects to the Accpac MSSQL database (v5.3). I'm using a UDL file that contains the connection information. The UDL file is logging on to the database using the sa account and it used by other processed without an issue.

I'm getting permissions errors and see that the stored procedure simply isn't running when I sign on to the network as a user. When I log on as an administrator account the code runs as planned.

This code checks for the existance of the stored procedure:

Dim lQry As New ADODB.Recordset
lQry.Open "SELECT count(*) FROM sysobjects WHERE [name] = 'OEItemSalesHistory'", cn
If lQry.Fields(0).Value = 1 Then GoTo ExecuteIt

When the user account runs this the query returns 0. Then it attempts to add the stored procedure and I get the following error: CREATE PROCEDURE permission denied in database 'ABCDAT'.

I'm using the connection object to run the script directly (sSql being the script text):

cn.Execute sSql

When the user account runs nothing happens. When the administrator account runs then the stored procedure runs.

I've tried hardcoing the connection string in case the UDL file contents were being misinterpreted but no dice.

Any ideas?

 

[SkipVought]

This code checks for the existance of the stored procedure:

There is no 'Stored Procedure' in the code you posted. YOU supplied the SQL code.

What is stored in sSql?

I ususlly run my SQL...

    rst.Open sSQL, cnn, adOpenStatic, adLockReadOnly, adCmdText

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[DjangMan]

Right - I left out the stored procedure code. The problem isn't with the stored procedure but rather it appears to depend on how I'm logged on to the network. Almost like SQL is reverting to NT Authentication.

I'm pretty sure it is my connection string. The connection string is:

Provider=MSDASQL.1;Password=somepassword;Persist Security Info=True;User ID=sa;Data Source=ABCDAT;Initial Catalog=ABCDAT

Which looks like it is refering to an ODBC DSN which is probably using NT Authentication...

I'll mark this up as solved. Thanks for responding Skip!

 

[SkipVought]

Check out...

http://www.carlprothman.net/Default.aspx?tabid=81

http://www.connectionstrings.com/

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}