Solved

Table Owners

Posted on 1998-12-02
7
258 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Suggested Solutions

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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.
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.

756 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