Solved

Table Owners

Posted on 1998-12-02
7
257 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
  • 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
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.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

772 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