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.
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.
Have the user login that is used for the export be aliased as a dbo in the database.
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.
Please expand on your answer.
ASKER
I'm reopening this to everybody. kponder, please expand on your answer.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
ASKER
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.