Hello,
I've just seen some strange behaviour and was hoping someone here could help explain why.
I have a DB1 and SCHEMA1, in DB1 is TBL1
I create a user DOMAIN\User1 and DOMAIN\User2 set their default DB to DB1 an SCHEMA1 (select, update, delete, insert)
When DOMAIN\User1 runs
SELECT * FROM TBL1
It returns SQL error 208 Invalid object name
When DOMAIN\User2 runs
SELECT * FROM TBL1
It executes correctly, I therefore compared the SQL permissions and found that they are idential.
To resolve the problem I had to remove DOMAIN\User1 from the local SQL groups (SQLServerMSSQLUser$SERVER$MSSQLSERVER and SQLServerSQLAgentUser$SERVER$MSSQLSERVER). Once I had done this and restarted SQL the problem went away.
Does anyone know if a user is a member of these groups does it default the schema to dbo the same way with the sysadmin role?
Thanks,
Peter