Voice of the DBA

Category: Blog

  • Map a Login – Basic Skill #3

    This post is part of a series based on my presentation The Top Ten Skills You Need for SQL Server. This post is part of Skill #3 – Setup Security.

    I wrote about the basic security model for SQL Server, and got a question about mapping users to logins. It actually is done automatically for you in the dialog when you create a login, but I thought I’d cover the basic process here in more detail.

    Logins allow access to an instance of SQL Server. Users are the construct in a database that can be assigned permissions (explicitly or through a role). The mapping between a user and a login is what allows SQL Server to determine which logins get which rights.

    Let’s look at an example. On one of my instance, I have a test login called “JoeLogin”. If I connect to the instance, I enter the credentials of “JoeLogin” and the password to connect.

    loginmap

    Once I connect, however, the first thing the SQL Server database engine does is set my context to either the database I’ve specified, or my default database. This immediately maps me to a user in that database and allows me the connection to run commands, or it returns an error if I don’t have access.

    In this case, I have access to my default database, and I can check on my user credentials with this code:

    SELECT USER_NAME()

    This returns “JoeUser”, which is my user name. You can read about user_name() here, but it is a system function that returns your current database user name.

    My login has essentially been bypassed, and would only be used if I needed to check permissions to execute an instance level function, like setting a configuration value or viewing the error log. However I can check my login by using the SYSTEM_USER function. 

    SELECT SYSTEM_USER

    This will return my login name, and it returns “JoeLogin” on my instance. I can easily see this in SSMS, in the right corner of the status bar at the bottom of the window.

    loginmap2

    The Mapping

    We can view the mapping between users and logins in two ways. If you want to see where a user is mapped, you can right click the user and select properties.

    loginmap3

    This will bring up a dialog for the user, and at the top you can see the login mapped to this user:

    loginmap4

    If you want to see where a login is mapped, you can right click the login in the server level Security folder and select properties. This brings up the login dialog, and if you select the “User Mapping” item from the left pane, you will see the list of databases and the user mappings.

    loginmap5

    In this example, my JoeLogin has been mapped to two database. In db1, the default behavior is applied and the login is mapped to a user with the same name. In db4, I have changed the default and mapped to a user called “JoeUser”.

    I haven’t run across a good reason to change the user name from the login name, and I don’t recommend it, but if you think you might have some issues, this is how you check things.

  • A Mini Break

    One of the things I am trying to be better about is ensuring I take all my time off each year and get away from work. This past year I was close, almost using it all and I thought I’d be better this year, but the travel has messed up my schedule a bit and I’ve been struggling to find some good time off. I’m hoping to rectify things and catch up on my skiing once I get past SQLInspire in November.

    The next 60 days are rough, and I’ll be out of town or traveling to/from something for 21 of them. Not the schedule I want to keep.

    However 3 of those days are this weekend, where my wife and I will get a break from life at the Austin City Limits music festival. We have always wanted to see Stevie Wonder and this is our chance to see him. Perhaps the last one we will have.

    So the kids leave tomorrow for Grandma’s in VA while we head to TX and 3 days of time away from our lives. Fortunately I’ve scheduled Monday off to recover.

    If I don’t answer emails, fix QODs, or anything else, you’ll know it’s because I’m away. I’ll get things squared away when I return.

  • Data Presentation – T-SQL Tuesday #22

    TSQL2sDay150x150It’s T-SQL Tuesday again, with our host this month being Robert Pearl, of Pearl Knowledge Solutions. He chose the topic of Data Presentation for September.

    If you haven’t participated in a T-SQL Tuesday, it’s easy. Read the rules in Robert’s post and read the basic idea in Adam Machanic’s original invittation. Then write a blog post with the logo to the right in it and link it back to Robert’s post.

    That’s it.

    Formatting Data

    One of the things that DBAs and data professionals should learn is that formatting typically doesn’t belong in the database. The client, front end, or the tools used to extract the data should handle formatting, presenting the data as the client wants to see it.

    When you store numbers, you don’t want to store things like currency symbols. So in SQL Server I should see this:

    datapresent1

    and not this:

    datapresent2

    The former gives more flexibility, and the latter limits what you display (and requires character storage).

    The same thing goes for other data, for example, phone numbers. We don’t want to store our phone numbers like this:

    datapresent3

    If someone wants to see just the number, or they add in another format of number (say European), then you have inconsistencies. Also, it becomes harder to separate out issues. When you do separate out the number from the area code, how do you handle things if you have this:

    datapresent4

    That’s hard to handle. If all the phone numbers were numbers, it’s easier to handle. This allows one easy CASE statement based on length.

    datapresent5

    Or better yet, use better design and let the front end handle data presentation.

    datapresent6

    Doing It Right

    I guess I haven’t talked data presentation so much as how not to handle data presentation. I know that SQL Server can do things like ordering, formatting, combining or splitting strings, or more, but ultimately I think that’s not the right way to handle things.

    I’ve always viewed the database as the single bottleneck. It’s incredibly hard, and expensive, to scale a database server, while it’s easier to scale web servers, app servers, and client tools, and much less expensive. It’s even easier to scale developers and have them write more code to handle presentation on the front end instead of using SQL Server to do the work. I’d try to always push any presentation work to the client instead of the database server, just because of the workload and bottleneck on the server.

    It might seem like more work up front, and it will be, but it will be infinitely better than trying to re-write code or upgrade your database server later when the load becomes larger.

  • Fun Networking at the PASS Summit

    Once again I’m a part of two fun events at the 2011 PASS Summit that I hope you’ll join me in attending.

    The Mentoring Experiment – Monday Night

    Andy Warren (LinkedIn | Blog | @sqlandy) and I started The Mentoring Experiment earlier this year as a way to try and help people grow their careers. It has been going well and we’ve rented out Lowell’s in the Pikes Place Market on Monday night from 6pm-9pm. Please feel free to come by and join us.

    Register at EventBrite

    We rented the space, but each person is responsible for their own food and drink. That should not, however, deter you from offering to buy a new friend a drink Winking smile

    The registration is for planning, but even if it shows full, come by if you have time and would like to meet a few fellow Summit attendees.

    The Exceptional DBA Awards Party – Tuesday Night

    For the last eight or nine years, SQLServerCentral has hosted an opening night party, just after the official PASS opening reception. We’ve tried a few things, but settled on a casino themed party for the last 5 or 6 years and it’s been great fun.

    This year we have arranged for it to happen again, and you can join us for some networking fun and also meet the 2011 Exceptional DBA award winner. We will once again have a casino caterer, allowing you to get some virtual “fun” money and enjoy various games. Come join us and have fun with fellow attendees on Tuesday night.

    We will have a cash bar available and there will be lots of random prizes, so try your luck at a few games and you might come home with a fun prize.

    We are selling tickets in advance for $20, or you can bring $30 to the event and pay at the door. Details are below:

    When: Tuesday 11 October 2011, 8-11pm
    Where: PASS Summit, Seattle – Room 2AB, Washington State Convention Center
    Tickets $20 – book yours now via PayPal to sjones@sqlservercentral.com