I had to test something for a customer, and as a part of this there as a need to have a different default schema for a user. I wrote about that, but since this isn’t something that I (or many people) do often, I wanted to make a second post about changing the schema.
The Scenario
A user in a database needed to access certain objects, which were going to be located in a separate schema. The previous post looked at the issue for a new user, but for this one, I wanted to show how to change a schema for an existing user.
This post shows how to alter a user’s default schema.
The Solution
When you add a user, this is a simple parameter as part of the CREATE USER DDL. In this case, you use the DEFAULT_SCHEMA parameter. The ALTER is the same, which isn’t the case with all parts of the T-SQL language. Sometimes there are procedures instead of true DDL.
In my case, we wanted to change the default schema for a user. In the first post, the APIUser had the default of the WebAPI schema. Let’s move them to the Sales schema with this code:
ALTER USER APIUser WITH DEFAULT_SCHEMA=Sales
GO
That’s it and now if objects aren’t schema qualified, the APIUser will query the Sales schema first, then the dbo schema. If this user wants to query the WebAPI schema, they must schema qualify things.
SQL New Blogger
This was a minor part of something else I was doing. I noted this in the previous post, but then realized the ALTER was a good second post. I could have added this to the first one, but I like separating and focusing posts. Better for SEO if you care, but better for your workload and producing most posts.
Outside of the work I was doing, the sketch of these notes took about 2 minutes, and then the entire post was < 10 minutes.
You can do this.
Voice of the DBA 