Voice of the DBA

Tag: SQLNewBlogger

Reading Data from the Command Line in PowerShell–#SQLNewBlogger
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

In every language I’ve coded in, there is a way to read input from the command line. From INPUT in BASIC, readln in PASCAL, scanf in C, read in LISP, input() in Python, and more, every language can do this.

I knew PowerShell could, and I wanted to find out how to do it. I’m building a CLI tool for SQL Saturday, and I am looking to get user input.

A quick search led me to Read-Host. I probably should have guessed this, but it does what I need. A quick example of where I started. This code gets an event number from a user and then prints if back out, along with a string created from the number:
```
$EventNumber = Read-Host -Prompt "Event Number:"
$DataFilename = "SQLSat$($EventNumber).yml"
# write the data
write-host("Event: $EventNumber")
write-host("File: $DataFilename")
```
I can run this, as shown here, and put in a number. I typed in the first 1022 below:

I’ll use this to get information from the user and then produce a YAML file that will contain what’s needed to publish, or update, an event.

SQLNewBlogger

This post took me about 10 minutes to assemble. I was already working through this process and stopped to jot down an idea, save some code, and make a screenshot. That was about 2 minutes out of my day, and then I went on with coding. Later, I wrote this.

You could do the same thing. Take a minute out of your daily work, sketch a quick post, and then finish it later.
November 22, 2021
Row-Level Security Basics–#SQLNewBlogger
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I realized recently that I hadn’t really blogged about Row-Level Security, so this post covers some of the things I know at a high level.

What is Row-Level Security?

This was a feature added to SQL Server in SQL Server 2016 that makes it easy to grant access to rows of data based on some characteristic of a user. At a high level, this means:
- I have something that segregates rows of data, like a CustomerID as a column in an Orders table.
- I want a customer to only be able to view their orders, those associated with their customer ID.
- This has to work, even if they didn’t use a WHERE clause and did a SELECT *.
- In this case, a user for CustomerID 4 would only see Orders that had CustomerID=4 in those rows.
We used to be able to do this with views, but this was cumbersome, and it was obfuscation. There was no security mechanism that actually ensured a user logged in wouldn’t see other rows.

Row-Level Security

This was a first class security mechanism that uses security policies and functions to control access. The way this works is as follows.

We create a function that is a table-valued function which takes a parameter(s) from a column(s) and returns a 1 if the user should view a row. In this case, we would use a WHERE clause in the query in the function that looks for Orders.CustomerID = @CustomerID.

We bind this function in a security policy that binds the function to the table, and specifies the column (or columns) used as parameters to the function. We also specify the predicate involved. There are two types:
- Filter predicates – limit read access
- Blog predicates – limit write (insert/update/delete) access
We give permissions to the function to users.

Does it Work?

Yes. It works very well from a security standpoint. Since we are tying this to users or logins, the performance of determining if the user or login has access can be slow. The IS_ROLEMEMBER() and similar functions are not super efficient and you can have performance issues across millions of rows.

However, it works.

I’ll write more in the future on the details.

SQL New Blogger

I was watching a presentation recently on this topic. I’ve written about this for SQL Server Central, but when I checked, I hadn’t really done much blogging on it.

Here I’m re-using knowledge, but in a basic way. I took 15 minutes to write a high level description. I’ll do a few more posts that demo setting this up for reads, one for writes, maybe one to get around how this might have a hole for security purposes. At least 3 more posts.

You could learn this and blog 3-4 times about what you learn and how to set up it up situations.
November 8, 2021
Quick CSV Import with dbaTools – #SQLNewBlogger
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

Recently I was searching around for some sample data for a project. I stumbled on a CSV of data from the Internet Movie Database (IMDB). I wanted to quickly import this into a database to play with and query. I decided to use dbaTools since my csv python skills were a little rusty and I thought this would be quick.

This post looks at how easy this was.

Disconnected from the Internet

I had downloaded the CSV before getting on a plane, but once I was ready to import this, I had no access to the Internet. This meant I had no access to looking up commands or documentation. As a result, I decided PowerShell and dbatools would be the easiest way to do this.

I ran a quick Get-Help command and got the name of the command. Using wildcards, I could see the dbatools result below.

I then ran “Get-Help Import-DbaCsv” to get the syntax. Using the results below, I wanted to see how quickly this would run.

A Quick Experiment

With the docs above, I created a database and then entered this command.
```
$movie = Import-DbaCsv -SqlInstance localhost -Database IMDB -AutoCreateTable -Path "IMDB-Movie-Data.csv"
```
This was my experiment. Just try some values and see what happens here. This ran in less than a second, and from ADS, I could see the data had been imported.

When I checked my variable, I saw that things had just worked with the 1000 rows imported in less than half a second.

Success!

This wasn’t my main task, but using some tools and some past knowledge, I figured out how to accomplish this task quickly and get back to the process of writing SQL code to query the data.

SQLNewBlogger

My entire purpose here was to write some queries against this data, but I needed to import the data. Either ADS or SSMS have import wizards, but I’ve had various levels of success at times with them. I didn’t want to work through an ETL process. Once I saw how quick dbatools made this, I decided to write this post, based on the ease of getting something done.

You could easily duplicate this post, noting why you needed to do an import and how easy this was. You could compare this to SSMS or ADS, or even write about starting to use dbatools for this purpose.

This took me less than 10 minutes.
November 1, 2021
What’s my MAC Address in a Command Prompt?–#SQLNewBlogger
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

The other day I was working with a licensing issue and someone requested my MAC address to track something down. I know I could dig into the adapter properties, but that’s an annoying set of clicks in Windows. I decided to spend that time checking out how to get the MAC address from a command line.

Normal CMD

I tried “ipconfig” at the command line, which I always have open. I got my IP and DNS info, but no MAC. A quick search on Google showed me I needed more detail.
```
ipconfig /all
```
This gives me all the info, as you can see below:

PowerShell

I’d prefer PowerShell for many things like this, mostly because I could programmatically extract the values and use them elsewhere. I don’t need that here, but I might for something.

In this case, this is in the WMIObject, which is an ugly command. I’ll never remember this.
```
Get-WmiObject win32_networkadapterconfiguration | select description, macaddress
```
However, there’s an easier cmdlet.
```
Get-NetAdapter |select MacAddress, Name
```
This will return what I want. I get see which MAC is associated with which adapter.

Both quick ways to get some information.

SQLNewBlogger

I had to get some info, and I took a minute to learn something. This took me just 2-3 minutes to learn how to do this and practice. Then it was less than 10 minutes to do this post.

For reference, I used these links:
October 27, 2021