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')
GOLet’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.