"Federica T" <fedina_chicca@ N_O_Spam_libero .it> wrote in message
news:ck******** **@atlantis.cu. mi.it...
Another question about SQL Server tables and access grants.
I've created an user 'user1' as Access account, ad also as db_owner of a
selected database.
Then, I've created a table as user1.table1.
When I try to access the data of the table, with the account name of
user1,
I must specify the owner ('select * from user1.table1') even if I've
logged
as user1 (I need to access the data just with 'select * from table1').
What's the problem?
Thank you
Federica
It sounds as if it should work as you expect - for users not in the sysadmin
role, MSSQL always looks for an object owned by the current user first, then
dbo (see "Database Object Owner" in Books Online), but for sysadmins it will
always look for dbo first.
Do you get error 208 (invalid object name), or does it select from
dbo.table1 instead? If you get error 208, then you can use USER_NAME() to
check that you are user1, DB_NAME() to check you're in the correct database
(you might be in master by default, for example), and OBJECT_ID() to make
sure the object exists. If it selects from dbo.table1 instead, then user1's
login is probably also in the sysadmin role on the server.
If the problem is still unclear, what do you get when you run the following
as user1?
create table user1.t1 (col varchar(100))
insert into user1.t1 select 'Owned by user1'
create table dbo.t1 (col varchar(100))
insert into dbo.t1 select 'Owned by dbo'
select
'Database: '+ db_name() + ' ' +
'Login: ' + suser_sname() + ' ' +
'User: '+ user_name() + ' ' +
'Object owner: ' + col + ' ' +
'db_owner: ' + case is_member('db_o wner') when 1 then 'Y' else 'N' end + '
' +
'sysadmin: ' + case is_srvrolemembe r('sysadmin') when 1 then 'Y' else 'N'
end
from t1
Simon
