Voice of the DBA

Category: Blog

What is Projectsv13 in SQL Change Automation?

I got this question recently from someone that was evaluating the SQL Change Automation client in Visual Studio. After setting up a new PoC (Proof of Concept), they were confused about why there is a new entry in their list of SQL Servers.

If you’re wondering, this is what you see in Visual Studio 2017, for the SQL Server Object Explorer.

In my case, I’ve actually got a few different instances since I’ve been working with the SQL Change Automation (SCA) for years, across a few versions.

As the instance names list, these are LocalDB instances. LocalDB is a version of SQL Server that is spun up in process, rather than a service. Other than that, it’s a real SQL Server instance that is running. In fact, if you want, you can connect to this with SSMS.

If I connect with those credentials, this will appear in the SSMS Object Explorer as well, the same as any other instance.

Why is this in SCA?

As an early part of the SCA product, when it was in ReadyRoll, there was a desire to not impose burdens on application developers. If you were working with a project for C# or ASP.NET and wanted to include the database, the idea was to keep things simple.

The simple way to get up and running is to use LocalDB, so no install, no worries with Express and a service running, just use LocalDB. This used to be more of a default, but now it’s just one of the options.

In my case, once I’ve setup my project to connect, the Shadow database, by default, is also right here, and I’ll see both databases under the (localdb)\Projectsv13 instance.

All of this is configurable, so you can set things to work in whatever manner works best for you. If you typically have a development instance, either local or remote, you can easily use those instead, and you’ll never need to worry about LocalDB. In fact, if this bothers you, you can disconnect or even delete the instance.

LocalDB is a lightweight way to work with SQL Server, but you don’t need to use it.

September 19, 2018
A Summit Contest for registering by Sept 22

If you haven’t registered for the 2018 PASS Summit and plan to go, you might press to get registration done this week. Next week prices increase, but this week you can save.

There is a promotion from PASS that will let you save $200 this week. In addition, you can beat the price increase next week, which will go up $200, so if you can register by Sept 22, you save $400 over the cost next week. Use the discount code in the graphic above.

There’s also the chance to win a daily prize from PASSStuff.com. Plus, everyone registering this week will be entered for the chance to win a complementary admission to the 2019 Summit.

If you’re going to the Summit, or have asked, press to get registration done this week. You’ll save some money and get the chance to win some fun SAWG and maybe even get next year’s summit registration for free.

Register today and good luck.

September 17, 2018
Deleting a User that Owns a Schema
This was an interesting question I saw posted recently. Through SQL Server 2000, trying to delete a user that owned objects was a pain. We had to actually rebuild all the objects, which was problematic. With SQL Server 2005 and later, we got schemas actually added as separate entities, so that solves some of the issue.

To allow a user to be removed from a database when they own a schema, we need to move the ownership, or authorization of the schema to a new user.

Here’s a short repro of what to do.

Let’s say that I have a user in my database called SallyDev. This user is a DDL_admin and can create objects. I’ll add Sally to my database with this script:
```
CREATE LOGIN SallyDev WITH PASSWORD ='SomethingStrong'

GO

CREATE USER SallyDev FOR LOGIN SallyDev

GO

ALTER ROLE db_ddladmin ADD MEMBER SallyDev

GO
```
As such. SallyDev has run this script:
```
CREATE SCHEMA SallyDev

GO

CREATE TABLE SallyDev.Class

( ClassKey INT IDENTITY(1,1)

, ClassName VARCHAR(100)

, Active BIT

)
```
Now, SallyDev has left the team and we need to remove her user and login. When we try to remove the user, we get this error:

In SQL 2000, Sally would have owned the table and we would have gotten a different message. Now, Sally owns the schema, but the schema now owns the table.

To fix this, we need a new owner for the schema. We can use any user with the ALTER AUTHRORIZATION command like this:
```
ALTER AUTHORIZATION ON SCHEMA::SallyDev TO JoeDev
```
I don’t recommend another user as we are just deferring the same problem. Instead, I’d move the schema to dbo.
```
ALTER AUTHORIZATION ON SCHEMA::SallyDev TO dbo
```
Once this is complete, we can drop the SallyDev user.

We can also verify the schema has moved to a new user.
September 17, 2018
Learn about SQL Source Control in Redgate University

I love the SQL Source Control product from Redgate. It’s not perfect, and it can be slow to run at times, but the simplicity of what it does, of getting my code quickly and easily to a VCS is fantastic. I really appreciate it.

This is one of the tools I enjoy demoing and showing off how to ensure you get all the code from you system stored away. I wish I had been able to purchase this product years ago when I was building database software as my day job.

We now have a course to help you learn how to use SQL Source Control at Redgate University. This is a series of 10 sections (as of now) that cover a variety of ways in which you can capture development code with SQL Source Control and even deploy those changes to another database.

Give the course a try, and see what you might learn about this product. We’ve got other courses as at Redgate University, with more coming all the time.

If you’ve got ideas or suggestions for the courses, send us a note at https://www.red-gate.com/hub/university.

September 17, 2018