VoltInsider

The SQL Logins vs. SQL Users Interview Question

April 8th, 2010

The vast majority of technical positions at companies like Microsoft (developer, tester, program manager, technical writer, support engineer, etc.) require at least a basic familiarity with SQL. So, if you are interviewing for a job you are quite likely to get a few questions about SQL. After questions about JOINS, one of the most common SQL question topics runs along the lines of, “Tell me about SQL logins and users.” Like most interview questions, a good approach for you is to use the “newspaper style” by giving the main ideas first, and then going into details later as indicated by your interviewer. So, you could begin by telling the interviewer that in general, SQL logins allow access to a SQL server machine and that SQL users allow access to a particular database. Next, in an interview, it’s hard to go wrong by drawing examples or code on the inevitably-there whiteboard (or paper if you’re more comfortable). So, suppose you have a SQL server named VTEXYZ which contains some database named dbMyDatabase. With SQL Server 2005 you can create a new login to the server with the T-SQL statements:

use dbMyDatabase
create login myLogin with password=’secret’

And then you can create a user associated with the new login by:

create user myUser for login myLogin

At this point you’d also have to grant specific database permissions to user myUser, for example, by the statement “grant select,insert,update to user myUser”. (You could also use the less granular SQL Server fixed roles, for “exec sp_addrolemember ‘db_datawriter’, ‘myUser’). By the way, the mechanism to create logins and users in SQL Server 2005 is much cleaner than in SQL Server 200. Now if a client program connects to server VTEXYZ as login myLogin, the connection will be associated with user myUser, and have the ability to manipulate database dbMyDatabase. This question could well lead to a discussion of using SQL authentication vs. using Windows authentication, but that’s another blog topic.

Entry Filed under: Interviewing