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.