Tag: security

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 one of the things that occurred to me is the need to know how security works with schemas and the priority of queries.

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 setup of the user, but what happens with queries. Let’s set up a few tables that will help us learn.

I’ll create two tables, one in each schema.

CREATE TABLE dbo.Location (locationname VARCHAR(20))
GO
INSERT dbo.Location (locationname) VALUES ('dbo schema')
GO
CREATE TABLE webapi.Location (locationname VARCHAR(20))
GO
INSERT webapi.Location (locationname) VALUES ('webapi schema')
GO

Let’s see what happens when we query the tables. If I write a query that qualified objects, I get what expected. I’ll login as APIUser and then run this code.

SELECT * FROM dbo.location
SELECT * FROM WebAPI.location

When I do this, I get the results expected.

If I don’t qualify the table, do you know what happens? You should, and this post shows the results that the default schema is queried first.

If I didn’t have a WebAPI.Location table, the dbo.location result would be shown. It is important that you understand SQL Server security if you manage instances or write queries, as you could otherwise feel you need to grant far too many permissions.

SQL New Blogger

This was a minor part of something else I was doing. I noted this in the previous post, but also realized the ALTER was a good second post and verifying the permission hierarchy was a good third 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 a little longer, with more code to write. This was about a 15 minute post.

You can do this.

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.

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. Since this isn’t something that I (or many people) do often, I wanted to make a note about how to do this.

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

The Scenario

A user in a database needed to access certain objects, which were going to be located in a separate schema. There was a possibility that there would be objects in the new schema and in the dbo schema with the same name, so the concern was with developers writing code that might access the wrong object.

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. I didn’t look this up at SQL Prompt hinted me to the WITH and the parameter.

In my case, we wanted to add a new user, which we will call APIUser and assign them to the WebAPI schema. We use this code:

CREATE USER APIUser FOR LOGIN APIUser WITH DEFAULT_SCHEMA=WebAPI
GO

Note a couple things. First, the schema name isn’t quoted. It’s treated as an identifier. Second, the WITH is used to add this parameter to the statement. Once we do this, if they user does not include a schema in an object reference, like the one below, they will still get data from the object in the WebAPI schema.

SELECT * FROM location

SQL New Blogger

This was a minor part of something else I was doing. In this case, setting up a different scenario, but I captured this slice of code, edited the names slightly, and then pasted them in here.

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.