Solved

Table Owners

Posted on 1998-12-02
7
259 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
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

Is Your DevOps Pipeline Leaking?

Is your CI/CD pipeline a hodge-podge of randomly connected tools? You’ve likely got a tool to fix one problem & then a different tool to fix another, resulting in a cluster of tools with overlapping functionality. Learn how to optimize your pipeline with Gartner's recommendations

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

752 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