1

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.

Improve this question
1
  • I connect with a SQL Login to my instance and run your query, it shows me as WINDOWS_USER...
    – user507
    Commented Aug 23, 2012 at 17:06

1 Answer 1

Reset to default
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
Improve this answer
7
  • 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
    Commented Jul 24, 2012 at 13:12
  • @GlenRobson: added a workaround...
    – gbn
    Commented Jul 24, 2012 at 13:19
  • Im sorry but that makes no sense to me
    – Glen Robson
    Commented Jul 24, 2012 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
    Commented Jul 24, 2012 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
    Commented Jul 24, 2012 at 13:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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