When i connect to SSMS on my server through remote desktop and query my database using:
SELECT * FROM [sys].[database_principals] WHERE [principal_id] = USER_ID()
I am getting the correct results (my name is DOMAIN\Glen and the type is WINDOWS_USER)
However when i connect to SSMS as a Administrator (right clicking and selecting run as administrator) i get the following results (name is dbo and type is SQL_USER)
This is a problem for me as in the users section under DOMAIN\Glen i have the default schema set. So when i run as administrator it is not picking up the correct tables.
Plus i am a little confused on why i am connecting as a SQL_USER when my authentication is set to Windows Authentication only.
The main reason i am trying to solve this is because when i run SSMS from my local machine it is connnecting in the same way as if i run as an administrator on the server and therefore i am unable to access my tables within the defined schema. I can not add this onto my procedures as they are all hard coded and there are over 1000.
Any help would be great.
Best Answer
Sorry to say it, but best practice is to always qualify object names with the schema anyway.
This is mandatory for things like schemabound views.
Why is it best practice? See this by Tibor Karaszi or this by Midnight DBA or just trust me or the MS SQL Server Best Practice Analyzer
After comment...
Have you considered synonyms to make OtherSchema.mycode point to [DOMAIN\Glen].MyCode?