Voice of the DBA

Tag: SQLNewBlogger

Altering the Default Schema for a User
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.
July 19, 2023
Assigning a Default Schema to a New User–#SQLNewBlogger
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.
July 12, 2023
Typing Strange Characters–#SQLNewBlogger
I’ve had to type a few non-English characters lately, and this blog talks 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 Need for Non-English Characters

Recently I wrote a blog on using Chinese characters. While you can copy/paste from Google, perhaps you want to actually type something regularly. For example, I have been learning French, and I sometimes need to

Another one I’ve used fairly often is the British pound symbol.

Typing Unicode characters

There are Unicode characters for these symbols. While some keyboards may include these, the US ones do not. I can switch keyboards, and I have a Japanese keyboard, but

A few commons ones for me are:
- é (French e with acute (forward) accent) – 0233
- è (French e with the grave accent) – 0232
- ô (French o with the circumflex) – 0244
- ç (French c with the cedilla,hanging thing) – 0231
- £ (British pound symbol) – 0163
- はい (Japanese yes, hai) – a little harder
To type these, I press the ALT key and hold it. Then I enter the number. So, holding ALT and entering 0163 gives me this: £

The Japanese is a little harder. There I needed to enable the Microsoft IME keyboard that lets me type the phonetics for Japanese characters. There are other ways to do this, but that’s what I did.

Learning to work with other languages and characters has been interesting to me, and it’s nice to be able to type São Paulo instead of Sao Paulo. Especially when I communicate with people whose names contain non English letters.
June 28, 2023
Restore One Backup From Many in a Device–#SQLNewBlogger
I wrote recently about finding multiple backups in a file. This post looks at how to restore one of those. The one you choose.

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

Setup

In the previous post, I did these things:
- took a backup
- added a table and data
- took a second backup
- truncated the table
- took a third backup
If I restore the default last backup, I get my table without data. You can read that post to see how I got here.

Let’s restore things.

Restoring a Backup

I cheat with restores. I remember some syntax, but typing it in and trying to remember the order is a pain, even with SQL Prompt. So I click restore database in SSMS and fill out the dialog. I pick the device and when I change the name in the Destination database, the file names change. Once I have the dialog below, I click “Script” at the top.

This gives me code in a new window. In my case, I get this code:
```
USE [master]

RESTORE DATABASE [sandbox2] 

FROM  DISK = N'D:\SQLBackup\sandbox.bak' 

WITH  FILE = 3,  

MOVE N'sandbox' TO N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\sandbox2.mdf',  

MOVE N'sandbox_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\sandbox2_log.ldf',  

NOUNLOAD,  STATS = 5
```
By default, this gives me file=3, which is the third backup. If I run this and then query the new database, I see this:

That’s what I expect. The third backup had the table with no data. Let’s restore the second one. First delete the database and then change File=3 to File=2. Once I run the restore and the same query, now I see data:

If I restore file=1, then there is no table.

Alter the FILE parameter to pick the backup in the file.

SQL New Blogger

This post took less than the 10 minutes of the previous post. I basically restored my database a few times with a query. The code was a couple minutes to generate and modify in SSMS, and this writeup was short.

The key was doing this immediately after the previous post and reusing the setup and code. Plus, the concept was in my mind.

As with the previous post, this is a good way to show knowledge and learning, and in this case, 20 minutes got me two posts.
May 17, 2023