SQL Default Schema for Users in the Local SQL Groups

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 Administration » SQL Default Schema for Users in the Local SQL...

SQL Default Schema for Users in the Local SQL Groups

Rate Topic

Display Mode

Topic Options

Author

Message

Pete-L

Posted Wednesday, August 15, 2012 9:43 AM

SSC 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 »

Permissions