Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
I connect with a SQL Login to my instance and run your query, it shows me as WINDOWS_USER... –  Shawn Melton Aug 23 '12 at 17:06
add comment

1 Answer 1

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?

CREATE SYNONYM OtherSchema.mycode FOR [DOMAIN\Glen].MyCode
share|improve this answer
    
I get that it is best practice but unfortunatly i did not write the views or stored procedures. There must be a way around this? –  Glen Robson Jul 24 '12 at 13:12
    
@GlenRobson: added a workaround... –  gbn Jul 24 '12 at 13:19
    
Im sorry but that makes no sense to me –  Glen Robson Jul 24 '12 at 13:27
    
@GlenRobson: a synonym is an alias for an object, as per my link. So your SQL_USER schema will resolve to your [DOMAIN\Glen] schema. Otherwise, fix the code –  gbn Jul 24 '12 at 13:28
    
The only schemas i have in my database are my schema (GLENSSCH) and dbo. And it would take months to change every single procedure, function and view within my database. That would not be possible. –  Glen Robson Jul 24 '12 at 13:31
show 2 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.