Voice of the DBA

Category: Blog

Granting CONTROL on a database–#SQLNewBlogger
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I wanted to grant a login the CONTROL permission on a database. This wasn’t a simple as I expected. I had a login (JoeDev) with no user mappings or server roles, and a database (EmptyFileTest) that I wanted to grant them permissions on.

My first attempt was this:
```
GRANT CONTROL on EmptyFileTest to JoeDev
```
This didn’t work.

I’m a sysadmin, so it’s not permissions. Maybe it’s qualification. The database isn’t found as an object, so let’s fix that.
```
GRANT CONTROL ON DATABASE::EmptyFileTest TO JoeDev
```
Aha, I’m making progress.

The database is found, but the user isn’t. That implies this is an internal permission in the database that can’t be granted to the login. One more try:
```
GRANT CONTROL ON DATABASE::EmptyFileTest TO LOGIN::JoeDev
```
Nope.

Let’s add the user.
```
CREATE USER JoeDev FOR LOGIN JoeDev
```
Now we’ll try this again.
```
GRANT CONTROL ON DATABASE::EmptyFileTest TO JoeDev
```
As you can see, the context is the master database. This won’t work.

Let’s change the context and execute this again. Once I do that, things work.

We can see this worked in the database properties for our database, under the Permissions tab.

SQLNewBlogger

Security and permissions are important. A series of short pieces on different aspects of managing, assigning, or using security will teach you a lot and show that you’re cognizant of the need for security for your databases.

This took about 10 minutes to write. Note that I’m showing my process of learning and progress, not just writing about what is required. Think about writing your story, not just the information.
August 9, 2018
Flipping Sides in SQL Operations Studio

I’ve been starting to use SQL Operations Studio (SOS) a bit more. This is a small, lightweight editor that is based on Visual Studio Code. It’s fairly limited, though it’s still in preview. You can download it and give it a try.

One of the things that I like is a consistent UI when writing code. I often hide the OE in SSMS, but I don’t want code moving left and right when I’m working with it. That can be a little jarring.

When working in SOS, I usually start with a query, and no blades (panes?) open. My workspace looks like this.

If I need to commit to version control, I’ll click that button, but then my code moves. (I’m really not committing this query. It’s just an example.

The process is just a little distracting to me, especially if I make the pane wider to more easily see the Object Explorer or file list. I didn’t like this in Visual Studio Code when writing PoSh or Python, and I don’t like it here.

Switching Sides

Someone on Twitter posted a couple neat tricks, one of which was moving the panes to the right side. I know some panes in SOS, like the connection one, are always over there, but you can move the main panes.

I’m surprised I didn’t see it, but I really never even thought about this being an option. I was just annoyed. If I’d looked in the View menu, I’d see “Move side bar right”. If I do that, I get this.

I could also fine this in the command palette

The other cool trick doesn’t do much in SOS yet, but it works in Visual Studio Code. I’ll show it there.

If I create a new file, VSCode doesn’t know what I’m doing. About half the time I’m writing Python, but I don’t get any of the intellisense or other features unless I save the file and give it a type.

The other option, is to click the language name in the lower right corner. In the image, I’ve clicked the “PowerShell” and this opens a list of languages. Type “py” and get Python.

Cool tricks. I expect that over time SOS will have more than just the “mssql” is has now. I’d expect MySQL, PL/SQL, and more.

.

August 8, 2018
A few fun pics from vacation

A few nice pics from vacation. Posting here as I’ll link from an editorial.

August 7, 2018
Refreshed and Back from Vacation

It was a nice break to get away from work for a couple weeks. My family and I traveled 2,000+ miles to Glacier National Park. We camped out each night, and managed to get to both Yellowstone (briefly) and Glacier. Obligatory pic below.

For the most part, I was unwired. There were plenty of places were traveled each day where no connectivity was available. My cell phone was mostly used for pictures, with the “No service” text at the top while in most of the national parks.

Lots of hiking, and some horseback riding. My wife even managed to get me on a horse one day.

It was nice to get away, and a little strange to arrive back in a place with more concrete and people than trails and wildlife.

Going through email and quite a few requests that came in over the last two weeks, as well as starting to prep for SQL Saturday Baton Rouge this weekend. Back in the saddle, as they say, very quickly this week.

August 6, 2018