Voice of the DBA

Category: Blog

Creating a User Without a Login

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

This is one of those simple things that many people should be able to do in order to build in better security for their database code. However I’m sure many people haven’t ever built one. I ran into this recently, and thought it would be a great SQLNewBlogger post.

Creating the User

It’s a bit anticlimactic, but creating a user in a database without a login is simple:

CREATE USER UpdateStatsUser
WITHOUT LOGIN WITH DEFAULT_SCHEMA = [dbo];
GO

This creates a new user in my database,

that is not associated wtih a login.

I can assign this user permissions, like any other user.

GRANT CONTROL ON dbo.Authors TO UpdateStatsUser;

I could then use these permissions any other way.

Why would you do this?

A short summary from Pinal Dave, which is pretty good. Essentially you want to give different permissions to a user, without using something like an Application Role, which has a name and password that must be managed. Here, you can allow a user to execute a routine as another, more privileged user, without giving the original user additional permissions.

SQLNewBlogger

This is a really simple post that took my longer to write than create the user. About 10 minutes. I wouldn’t expect most of you to stop here. I’d want a post that shows you understand something about how this user can be used, show me an example of reading or writing a table as a user with this impersonation in action.

References

CREATE USER – https://msdn.microsoft.com/en-us/library/ms173463.aspx

February 8, 2016
Loading a Text File with a Line Feed

Loading text files is a skill that probably every DBA needs. I know that the import wizard is available, but there are times that you might want to automate this without using SSIS. In those cases, it’s nice to know how to load data from a programmatic standpoint.

I had the need to do this recently with a text file that looked normal. When I opened it in my editor, it looked like a normal text file, one column of data.

I thought this would be easy to load, so I created a simple table:

CREATE TABLE MyTable ( teststring VARCHAR(100))

I then ran a simple BULK INSERT command.

BULK insert MyTable
     from ‘C:\SampleFiles\input.txt’

And I received this:

Msg 4863, Level 16, State 1, Line 3
Bulk load data conversion error (truncation) for row 1, column 1 (mychar).

That’s not good. I suspected this was because of the format of the file, so I added a row terminator.

BULK insert MyTable
     from ‘C:\SampleFiles\input.txt’
with ( ROWTERMINATOR = ‘\r’)

That didn’t help. I suspected this was because of the terminators for some reason. I also tried the newline (\n) terminator, and both, but nothing worked.

Since I was worried about formatting, I decided to look at the file. My first choice here is XVI32, and when I opened the file, I could see that only a line feed (0x0A) was used.

However, I wasn’t sure how to get this in my code.

I tried CHAR(), and that didn’t work.

I could look to edit the code with XVI32, but that seems odd. However, let’s try that.

I replaced the \r with 0x0A and then deleted the r. Once I saved this, and reloaded into SSMS (do not normalize to CRLF), I could run this.

I suppose I could also do this with the ALT key, and a number pad, though I couldn’t get that to work on my laptop. I need to try that on my desktop, but it’s not a great way to code. Easy to forget that characters are in the code.

I tried searching a bit and found that SQLDenis had a solution. He used dynamic SQL, but with a little formatting, the code is still easy to read, and this works fine.

DECLARE @cmd VARCHAR(8000)
SELECT @cmd = ‘BULK insert Mytable
                from ”C:\SampleFiles\input.txt”
                with ( ROWTERMINATOR = ”’ + Char(10) + ”’)’
EXEC(@cmd)

I executed this and loaded my file just fine.

It’s not often you might need to do this, but it’s a handy little trick for those files that might be formatted from other OSes.

February 3, 2016
Loading a Text File from T-SQL

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

One of the interesting things I’ve had to work on with the Advent of Code puzzles is loading files into SQL Server. Some of the inputs are large strings, but many are files with lines of code that need to be loaded into SQL Server.

I thought this might be a nice, simple SQLNewBlogger post. Do you know how to load a text file? Certainly the Import/Export wizard can work, but can you quickly load a file from T-SQL itself?

If you can’t, go work that out. If you get stuck, come back or search for help.

Loading a Text File

Obviously you need a place to load the file. I created a table for each puzzle, and here is the table for Day 2.

create table Day2_WrappingPresents
( dimensions varchar(12)
)
go

Now ordering doesn’t matter for this puzzle, so I have a very simple table. If ordering mattered, I’d have to do this differently.

To load this file, I’ll use the BULK INSERT command. This takes a table as a target, and optionally has a number of parameters. Since this is a simple load of a simple file with one column of data to a table with one column of data, I can use the defaults.

bulk insert Day2_WrappingPresents
from ‘C:\Users\Steve\Documents\GitHub\AdventofCode\Day 2 – Wrapping\input.txt’

In this case, the insert will load all 1000 rows into the table. A simple query shows this works:

Now I can get on with the rest of my puzzle solution.

SQLNewBlogger

This is a great example of a simple thing that we might not need to do often, but we may need to do at times. Knowing how to do this, a simple operation, showcases that you are improving your SQL Server skills. This post took me about 5 minutes to write.

February 1, 2016
Laptop Build Quality

I’ve been looking around at various laptops, in preparation for getting a new one. I wrote about considering the Surface Book, which is still on the list, but has dropped a bit. The hardware quality is great, but when I was in the UK last week, a few people had them and complained about some driver bugs. In particular, I was messing with one person’s touch keyboard, and they warned me not to pull if off. If I did, the machine might crash.

Ugh. At $2k and lots of hype, I wouldn’t expect any issues like that.

In any case, this post is about build quality, not software.

I was laying in bed this week, working on some editorials when my daughter came in. She wanted me to look over a piece she was writing for school and handed me her laptop. She has a Macbook Air, and as soon as I put my Toshiba z30 down, I was impressed with the Air’s build. It’s solid, it’s light, but it feels strong. I remember loving my Macbook Air, and holding it as I reviewed her work, I was reminded of that.

My z30 flexes, to the point that across a year, my touch point is unusable with the twisting of the frame. The trackpad was also far, far superior on the Air. I thought the Macbook Pro was like that, so I swung by a Best Buy to check. I walked in and went to the Apple section, picking up a Macbook and it feel solid. It’s just a better device than my Toshiba.

However I was curious about others. I did walk over and look at a Surface Book. It’s a solid machine, about the size and weight of the MBP. However it has the touch screen, which is interesting. The trackpad works differently, but it’s a nice machine. Detaching the screen, it’s a tablet, which is nice. I still don’t know how much I’d use the tablet factor, but it’s tempting. However the weight distribution is strange. The screen is heavier than the keyboard, the opposite of most laptops.

I also walked over to look at a Yoga 900, which I was curious about after reading Tim Mitchell’s review. I’m actually anxious to see how Tim’s machine looks next month in NM, but for now I contened myself with the display model. The hinge is neat, but this is a light laptop. At first glance, it also was solid. The flex I have on my Toshiba was not there. Despite a few reviewers noting this felt plastic and cheap, I didn’t get that feeling. It’s no Macbook, but it’s better than my Toshiba.

This will be an interesting decision for me, but since I’m going to wait for Apple’s announcement in March and see what they might do. I doubt they’ll go touch screen, but you never know. I have gotten used to touching my screen for some reading, and I think I might miss that with a MBP.

January 29, 2016