Voice of the DBA

Author: way0utwest

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
Unwired for Weeks

I took a few weeks off recently for a vacation with my family. We traveled from Denver to Yellowstone National Park in Wyoming and then on to Glacier National Park in Montana. We drove a horse trailer, camping along the way in the vast outdoors of America. It was a wonderful time and a beautiful part of the country. If you get the chance to visit, I’d highly recommend the trip.

One of the interesting things on this trip was that I was very unwired. In fact, I didn’t even take a laptop, which is a rarity for me. Often I’ll take one to jot notes, sketch an editorial, or just have around to check on things. This time, I didn’t bother, and neither did my wife. One kid did, but only because he was enrolled in a couple classes (CS and Math) this summer and had assignments to do.

The only device I had was my phone, which didn’t have service for much of the trip. Both during long drives (I was glad Google Maps downloads directions), at many of our campsites, and in the national parks, there was little service. In fact, when I did have service, I was surprised. The camping locations did have wi-fi, but I only used it to upload pictures of the trip and didn’t even bother checking social media as I posted. I mostly uploaded pictures to ensure I had a backup.

I did check email once early on to be sure that I hadn’t forgotten anything from work early, and forwarded on a couple items to people, but when I arrived home last week, I had hundreds of messages from Redgate and probably close to a thousand from SQLServerCentral. It was quite a chore handling and deleting a number of items, and took half of my Monday back in the office.

What was interesting to me is that I didn’t really miss the social media much, certainly didn’t miss the news, and nothing that important came up. I didn’t worry about work, knowing that something would go wrong (such as a few buggy questions) and that someone would handle the situation (thanks to Grant for fixing things) or delay the item until I returned. I found numerous meetings canceled or rescheduled without me and quite a few hastily dashed emails or queries that were later retracted or amended. In fact, going through emails from newest to oldest allowed me to delete older, out of date messages without reading them.

I rarely take more than a few days off at a time, usually just a long weekend here and there, but this proved (again) something I’ve suspected for a long time. Much of the pressure we feel with deadlines at work is arbitrarily imposed. Whether we do it to ourselves or someone else picks a datetime, there isn’t any rationale for the choice. Usually the goal is to complete work as fast as possible, but if there are delays, if something comes up, with life, other tasks, unforeseen failures (hardware/software) get in the way, the business will survive.

I learned a long time ago that despite my best efforts, things will go wrong and there will be delays. I’ve learned to expect them, accept them and try to not add any extra stress on myself when things are delayed. Go home at a reasonable hour most of the time, live the rest of your life, while doing the best professional job you can. There will always be more work, and the business will survive if most of your work ends up being delayed slightly.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 6.2MB) podcast or subscribe to the feed at iTunes and Libsyn.

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