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.

First off i would like to appologise if i sound stupid as i do not have much knowladge of SQL Server.

I have recently started a project working with SQL Server 2008 R2. I have access to the database using Windows Authentication. Once logged in i can access all of the databases etc... My problem is, for my user DOMAIN/Glen i would like to add a default schema for one of the databases. I have changed the Default schema for 'DOMAIN\Glen' within the users section under the database in question however whenever i run a script without the schema prefix on the table it will not run.

I have been looking around and trying some things out and to my knowladge i am for some reason using the SA account and not the DOMAIN\Glen login.

If anyone could help me change my Loged in user from SA to DOMAIN\Glen that would be a great help.

Thanks

Boldonglen


EDIT

When i am accessing the database directly on the server it is logging me in as DOMAIN\Glen but when i am accessing it from my computer it is logging me in as SA.


EDIT 2

Also when i connect to SQL Server on the server normally i am getting the default schema of the one i have added however if i run SQL server on the server as an administrator i am getting the default schema of dbo.


Any Ideas?

share|improve this question
    
Are you using Management Studio to connect? If so, check the properties on the connection. Or create a new connection in Registered Servers and specifiy Windows Authentication. –  SqlACID Jul 20 '12 at 11:07
    
@SqlACID Yes i am using Management Studio. I have cnahed the authentication to Windows Authentication only and i am still having the same problem. Why did i get a down vote for this post? –  Glen Robson Jul 20 '12 at 11:45
    
@GlenRobson: Now that you've switched to Windows authentication, are you connecting as Glen? What do you get using SELECT SCHEMA_NAME()? –  Jon of All Trades Jul 20 '12 at 15:27
    
@SqlACID I am connecting using my DOMAIN\Glen on both my local computer and on the server (When remote accessing it)... On my local computer i am getting dbo and on the server i am getting PIRMA (the correct Schema name). Why am i getting dbo on my local computer? –  Glen Robson Jul 20 '12 at 15:34
1  
sounds like the default schema is not set for the account; run "select default_schema_name , type_desc , name , create_date from sys.database_principals " on both and check your account. –  SqlACID Jul 21 '12 at 2:05
show 5 more comments

1 Answer 1

up vote 4 down vote accepted

DEFAULT_SCHEMA is ignored if the user is a member of the sysadmin fixed role. If DOMAIN\Glen is in the sysadmin role, the default schema is always dbo and you have to explicitly use the schema name.

share|improve this answer
add comment

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.