Permissions problem between access 2000 and sql server 2005 with stored procedures
We recently updated our database from sql server 7 to sql server 2005. We use access as a frontend for our main application which exists on a terminal server.
When I log into the terminal server as an admin and open the application; the stored procedures work fine. When I log in as a normal user most of the storedprocedures fail. Also when I am logged in as a user access renames the stored procedures and adds a ";1" at the end of them or adds a prefix of "fn" or "dt".
Obviously this is some sort of permissions issue, however we do not know where to change the permissions for this.
When I log into the terminal server as an admin and open the application; the stored procedures work fine. When I log in as a normal user most of the storedprocedures fail. Also when I am logged in as a user access renames the stored procedures and adds a ";1" at the end of them or adds a prefix of "fn" or "dt".
Obviously this is some sort of permissions issue, however we do not know where to change the permissions for this.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Trying to follow link from Microsoft from Marten Rue link from above and link no longer exists.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Glad to hear you solved it.
If you want to read up on the security model.
Here is another link: http://msdn.microsoft.com/en-us/library/ms189121.aspx
/Marten
If you want to read up on the security model.
Here is another link: http://msdn.microsoft.com/en-us/library/ms189121.aspx
/Marten
ASKER
We use ADP's
The users login into access via a login in screen (username and password is identical to their windows login) so in essence windows authentication once again to access the sql.
The only place the stored procedures names change is in the access adp logged in as a user. If I log in as myself (administrator) the stored procedures are named correctly.