Creating SQL Server Stored Procedure from Access 2007 - change schema

Question

I have an Access 2007 Project with a SQL Server 2005 backend. However, when creating a new stored procedure from within Access, it comes up with an error:

ADO error: The specific schema name DOMAIN\username either does not exist or you do not have permission to use it

How can you create a stored procedure using the 'dbo' schema instead? The stored procedure properties dialog has the 'owner' dropdown set to the username and it can't be changed to dbo, even though the user is in the 'db_owner' role.

Answer 1

OK I would have the people create the procs on the SQL Server instead where you can add dbo to the create proc code. If you can't do that perhaps you could run code to change the owner after it is created using sp_changeobjectowner

Answer 2

Try making the user a member of the dbo schema rather than db_owner. This is how I do it.

As an aside you may want to look at the specific SQL Server management tool (Management Studio which comes with SQL Server 2005 or either the 2005 or 2008 express versions which are free downloads), this provides a richer interface for DDL type tasks.

Answer 3

Managed to solve it. If you make the user the owner of the database (rather than a user with the db_owner role), when you create a stored procedure in Access 2007, it creates it under the dbo schema rather than DOMAIN\username.

Steps to do so (in Management Studio):

• Right click database, select Properties
• Click File
• Change Owner in the textbox
• OK to confirm

Downside - other users under db_owner role will still have their username appended. So schemas have to be created for these users.