Solved

Table Owners

Posted on 1998-12-02
7
260 Views
Last Modified: 2010-03-19
I have an Access database that exports data to a table in SQL Server and then executes a stored procedure on the server. My problem is that the owner of the SQL table is dependent on the login from the user of the Access database. While the stored procedure is looking for a table owned by dbo.

So, can I set the ownership from Access OR can I write a stored procedure that doesn't care who owns the table?

Help Please.
0
Comment
Question by:gbentley
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
7 Comments
 
LVL 1

Expert Comment

by:kponder
ID: 1092050
Have the user login that is used for the export be aliased as a dbo in the database.


0
 
LVL 5

Author Comment

by:gbentley
ID: 1092051
I'm not sure what the effect of that is for security on the database. For example, does that mean that the user then has the same rights as "sa" or what. The reason I ask is that I am trying to get to the point where the users have no need at all to know the "sa" login.

Please expand on your answer.
0
 
LVL 5

Author Comment

by:gbentley
ID: 1092052
I'm reopening this to everybody. kponder, please expand on your answer.

0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 7

Accepted Solution

by:
tchalkov earned 100 total points
ID: 1092053
If you want the owner to be DBO then you must log on as an user who is dbo or is aliased to dbo. You can create alias by sp_addalias. This means that this user will have the rights of the SA but only in this database. If this is not acceptable you must change the stored procedure so it can accept table which are not owned by dbo.
0
 
LVL 5

Author Comment

by:gbentley
ID: 1092054
How does one write a stored procedure that doesn't care who owns the table.
0
 
LVL 7

Expert Comment

by:tchalkov
ID: 1092055
it cannot be done directly but there is a way to make it. The idea is to build dynamically your TSQL statement like this:

declare @a int
declare @b varchar(200)
select @a=(select uid from sysobjects where name='table1')
select @b=user_name(@a)
execute ('select * from '+ @b + '.tale1')

@b contains the owner name of table1. After that you use it to build the apropriate TSQL statement.

0
 
LVL 5

Author Comment

by:gbentley
ID: 1092056
Thanks for that. I did a test using aliases this morning and it seems to work. If not however, I will do it this way.
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question