Using ACCESS 2000 to create SQL Server view and Store Procedures

Using ACCESS 2000 to create SQL Server view and Store Procedures

Post by Greg Larse » Wed, 19 Jun 2002 23:49:09



I'm using ACCESS 2000 as a front end to a SQL Server 7.0
database.  I'm connecting to SQL Server using SQL Server
Authentication.  I'm in the "db_owner" role in the
database I'm connecting too.  Here is the issue I am
having.  When creating a view or stored procedure ACCESS
creates the views and stored procedures fine, although the
owner of the views and stored procedures is my user name
in the database, and not DBO.  My question is, how do I
configuration ACCESS 2000, or my connection to create all
objects with DBO as the owner, and not my user name in the
SQL Server database.  Note I am not allowed, and do not
have access to logon to SQL Server using the "SA" account.
 
 
 

Using ACCESS 2000 to create SQL Server view and Store Procedures

Post by Mary Chipma » Thu, 20 Jun 2002 00:21:05


Have the system administrator run the sp_changeobjectowner system
stored procedure on the objects you create to make dbo the owner--you
don't have permissions to change object ownership, and neither does
Access.

-- Mary
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/exec/obidos/ASIN/0672319446

On Tue, 18 Jun 2002 07:49:09 -0700, "Greg Larsen"


>I'm using ACCESS 2000 as a front end to a SQL Server 7.0
>database.  I'm connecting to SQL Server using SQL Server
>Authentication.  I'm in the "db_owner" role in the
>database I'm connecting too.  Here is the issue I am
>having.  When creating a view or stored procedure ACCESS
>creates the views and stored procedures fine, although the
>owner of the views and stored procedures is my user name
>in the database, and not DBO.  My question is, how do I
>configuration ACCESS 2000, or my connection to create all
>objects with DBO as the owner, and not my user name in the
>SQL Server database.  Note I am not allowed, and do not
>have access to logon to SQL Server using the "SA" account.


 
 
 

Using ACCESS 2000 to create SQL Server view and Store Procedures

Post by Greg Larse » Thu, 20 Jun 2002 00:41:55


Yes it is true you can do that, but that seems kind of a
kludge.  Is access so STUPID, that it will not allow you
to save an object as dbo.xxxxx!!!
Quote:>-----Original Message-----
>Have the system administrator run the

sp_changeobjectowner system
>stored procedure on the objects you create to make dbo
the owner--you
>don't have permissions to change object ownership, and
neither does
>Access.

>-- Mary
>Microsoft Access Developer's Guide to SQL Server
>http://www.amazon.com/exec/obidos/ASIN/0672319446

>On Tue, 18 Jun 2002 07:49:09 -0700, "Greg Larsen"

>>I'm using ACCESS 2000 as a front end to a SQL Server 7.0
>>database.  I'm connecting to SQL Server using SQL Server
>>Authentication.  I'm in the "db_owner" role in the
>>database I'm connecting too.  Here is the issue I am
>>having.  When creating a view or stored procedure ACCESS
>>creates the views and stored procedures fine, although
the
>>owner of the views and stored procedures is my user name
>>in the database, and not DBO.  My question is, how do I
>>configuration ACCESS 2000, or my connection to create
all
>>objects with DBO as the owner, and not my user name in
the
>>SQL Server database.  Note I am not allowed, and do not
>>have access to logon to SQL Server using the "SA"
account.

>.

 
 
 

Using ACCESS 2000 to create SQL Server view and Store Procedures

Post by Greg Larse » Thu, 20 Jun 2002 01:11:53


For those that don't want to bother the SA to rename the
objects here is a work around, I developed:
To create a view owned by DBO:
1) From the view menu "right click" on one of the views
owned by DBO, like vLynden(dbo) and select "Copy".
2) Move the mouse off of view into white space, and "right
click" and select "Paste".  
3) When the paste function prompts you for a name enter
the name of the new view you want to create.  This will
create a view that is owned by DBO.
4) Edit the new view you created in step 3 and change it
to what you would like for the new view and save.  This
will save your new view with the name you used in step 3.

To create a Stored Procedure owned by DBO.

You can either follow the view instructions above, but do
it for Stored Procedures, or you can do the following:

1) From the Stored Procedure menu click on "Create Stored
Procedure in Designer".
2) Create the stored procedure with the code you want, and
then save it with the name you want.  This will create the
SP under your user name and not DBO.
3) Own the SP you just created, and change the name from
to dbo.xxxxx, where xxxxx is the name you saved is as, in
step 2.  This will create a new SP owned by dbo.
4) Delete the SP that is not owned by DBO.

Quote:>-----Original Message-----
>I'm using ACCESS 2000 as a front end to a SQL Server 7.0
>database.  I'm connecting to SQL Server using SQL Server
>Authentication.  I'm in the "db_owner" role in the
>database I'm connecting too.  Here is the issue I am
>having.  When creating a view or stored procedure ACCESS
>creates the views and stored procedures fine, although
the
>owner of the views and stored procedures is my user name
>in the database, and not DBO.  My question is, how do I
>configuration ACCESS 2000, or my connection to create all
>objects with DBO as the owner, and not my user name in
the
>SQL Server database.  Note I am not allowed, and do not
>have access to logon to SQL Server using the "SA" account.
>.

 
 
 

Using ACCESS 2000 to create SQL Server view and Store Procedures

Post by Mary Chipma » Thu, 20 Jun 2002 05:24:02


On Tue, 18 Jun 2002 08:41:55 -0700, "Greg Larsen"


>Yes it is true you can do that, but that seems kind of a
>kludge.  Is access so STUPID, that it will not allow you
>to save an object as dbo.xxxxx!!!

It's not a question of Access being stupid--that's the way SQLS
security works. If you are a member of db_owner, then you can create
stored procedures owned by dbo by using the ownername.objectname
syntax:

Create proc dbo.procname
AS
--your code goes here

-- Mary
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/exec/obidos/ASIN/0672319446

 
 
 

1. Access 2000 calling SQL Server 2000 store procedures

Hi Folks,

Maybe you can help, Is there anyway I can call SQL Server 2000 stores procedures from Access 2000, if access 2000 is being used as my client.

If so ? how ?

Also would you recommend Access'2000 as a  good client to use, if not what else part from full programming languagues like VB, Visual C etc would you recommend ?

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

2. Invoice Number

3. Connecting to a Sql Server 2000 SP3 from a Sql Server 2000 SP2

4. compatibility

5. moving from Sql server 2000 evaluation to full version of Sql server 2000

6. Problem using ADO to connect to 8i

7. Update NT4.0 and SQL Server 7.0 to Windows 2000 and SQL Server 2000

8. How do I use SQL in Btrieve v6.15 with Titan Btrieve?

9. Access 2000 + Sql Server 7.0 - Stored Procedures???

10. sql server 200 and access 2000 (stored procedures)

11. Access 2000 + Sql Server 7.0 - Stored Procedures???

12. Access 2000/SQL Server subscriber stored procedure issue