CODEWITHSUNDEEP
sqlsql-server-2008t-sql
Francis Rodgers
Francis Rodgers

Reputation: 4675

Problems setting the Default Schema

In college I run the following command to alter my default schema:

ALTER USER [campus\s00103417]
WITH DEFAULT_SCHEMA = MyCmdSet01;

I recently installed SQL Server on my laptop under win7. Everything else works great. But when I create a database, then create a schema and try to set it as the default it gives me an error. Following is the code I run on my laptop.

ALTER USER [L01\Admin]
WITH DEFAULT_SCHEMA = dbo;

Below is the error I am getting

Cannot alter the user 'L01\Admin', because it does not exist or you do not have permission.

What I dont understand is, obviously I exist, I created the database. Also I taught I as the DBO would have permission to do pretty much anything I liked. Obviously I dont fully understand this stuff. Can anyone explain how I can the the command above to work so I can alter my default schema.

If it is relevant, I have only one windows login (no password). As soon as the computer boots, it pops me onto the desktop. Then I start up SQL Server 2008 Management Studio which asks me to connect and I type L01 as the server name and select windows authentication.

Any help would be greatly appreicated.

EDIT: I dont know if these will provide any help. I ran the commands:

SELECT SUSER_NAME()  --Output L01\Admin
SELECT USER_NAME()   --Output dbo

Upvotes: 4

Views: 1873

Answers (2)

Mithrandir
Mithrandir

Reputation: 25337

In SQL Server the user is a database level object. What is your current database when you execute the alter command? Do you actually have user called [L01\Admin]?

Upvotes: 0

user596075
user596075

Reputation:

You are talking about your login (I'm guess it is 'L01\Admin'). That is different than a database user. If your login is in the sysadmin fixed server role, you are automatically mapped to the dbo built-in database user. Therefore, that is why you aren't finding your L01\Admin user, because it actually doesn't exist.

Does that make sense? Just remember: server login <> database user.

Upvotes: 5

Related Questions