Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL Default Schema for Users in the Local SQL Groups Expand / Collapse
Author
Message
Posted Wednesday, August 15, 2012 9:43 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, January 23, 2013 5:40 AM
Points: 26, Visits: 400
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
Post #1345359
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse