Link to home
Start Free TrialLog in
Avatar of gbentley
gbentley

asked on

Table Owners

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.
Avatar of kponder
kponder

Have the user login that is used for the export be aliased as a dbo in the database.


Avatar of gbentley

ASKER

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.
I'm reopening this to everybody. kponder, please expand on your answer.

ASKER CERTIFIED SOLUTION
Avatar of tchalkov
tchalkov

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
How does one write a stored procedure that doesn't care who owns the table.
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.

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.