SQL Server execute permission denied on object

[Roger Sipson]

I have an Access FE to a SQL BE that has been running fine for several
years. Recently the SQL part was moved to a new server. I changed the
connect string to reflect the new server name and my user can get to the db
but she has started getting

SQL Server execute permission denied on object

messages that did not appear previously.

Example problem VB code is

Dim cmd As New ADODB.Command
With cmd
.ActiveConnection = glbConnection
.CommandText = "uspStudentExistsArchive"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("RETURN_VALUE", adInteger,
adParamReturnValue)
.Parameters.Append .CreateParameter("@DragonID", adChar,
adParamInput, 8, strDragonID)
.Execute
End With

uspStudentExistsArchive exists and the call is correct.

I am using an ADODB.Connection

I have no problems when I use the BE on my computer, which runs on SQL
Server Developer edition. The problem only appears when my user is
connecting to a BE running on the new server. Since I am out of state I can
not run against the SQL server that she is using.

This is the only FE to a lot of data so I REALLY would appreciate any help.

Roger Sipson

 

[Sylvain Lafontaine]

This is a permission problem: the Login account or the required permissions
have not been set up properly on the remote database. However, permission
problems are one of these things that cannot be set up graphically (ie.
using the GUI interface) from Access ADE; so you have to use DDL statement
or use something else like Enterprise Manager.

I don't understand what you mean by "Since I am out of state I can not run
against the SQL server that she is using.". EM or any other client tool
shouldn't have any problem to make a remote connection if you don't have a
firewall blocking the port 1433 somewhere along the line.

 

[Roger Sipson]

Sylvain,

Thanks for your reply. I also think that it is a premission problem but the
SQL administrator thinks that the permissions have not changed since it was
on the other server and cmd.execute worked. I have seen some other sources
for this message posted, like having cmd.prepared set to true. I am going to
have the permissions double checked. Table modification does not seem to be
a problem, just .Execute.

I wonder if something might have gone wrong when the SQL BE was moved from
the old machine to the new one?

I don't know how to run the Access FE on my machine and have the data source
be the one on a remote server. I only have dialup where I am now. I have
been developing use SQL Developer edition and, when I did the work, I could
go on campus and repoint my FE to use the network SQL. I would be
interested in being able to run on my machine with the BE on the remote
machine, even over a slow dialup, since I might be able to debug.