Solved

Table Owners

Posted on 1998-12-02
7
255 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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

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.
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 combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

757 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now