What Are SQL Server Logins and Users

You’re a data professional learning about managing SQL Server and you’ve been asked to grant permissions for SQL Server to an individual or a group of individuals. What do you need to understand in order to accomplish this? I’ll be your guide to getting started with handling access to SQL Server.

Let’s start with an analogy.

Keys and fobs are common place these days for vehicles. Sometimes if you have a fob it ties into the ignition allowing for push button start so no key is needed for that. That’s not always the case though. Many people still have key and fob combos on their key ring. For our purposes, we’re going to assume that is the case.

Your fob let’s you access your vehicle’s doors. The fob gives you what I’ll call an initial, top layer of access to your vehicle. With it you can unlock the doors so you can get inside. You can now sit in the seats, open the glove compartment and access other surface level features of the car, but you can’t do much else.

Your key to the ignition lets you start the car, giving you access to the engine, radio, power seats and other features in the vehicle.  With the key in the ignition, you can now take your car for a spin.

What is a SQL Server login?

A SQL Server login is much like the fob to your car. The login, by itself with no other permissions having been applied, gives the holder of the login access to the initial, surface layer parts of SQL Server. The login allows for connecting to an installation of SQL Server on a computer. A login can be a Windows authenticated account or a SQL Login. One uses the Windows operating system authentication method and the other uses just data within SQL Server itself to authenticate or verify the ability to connect to a SQL server instance.

SQL Server Fixed Roles

Roles are sets of permissions that can be assigned to a security object, like a SQL Server login. These permission sets grant certain abilities to the logins that have been assigned those roles.

• bulkadmin: Allows for running the BULK INSERT statement
• dbcreator: Allows for creating, altering, dropping or restoring a database. Databases created by logins in this role are owned by that login and essentially have db_owner rights in the database.
• diskadmin: Allows for the management of backup devices
• processadmin: Allows for use of the KILL command to end session connections
• public: Allows for access to certain metadata system catalog views and is granted to all logins by default.As a best practice, you should never change the permissions applied to the public server role because then every login connecting will have those permissions.
• securityadmin: Allows for the management of access to the SQL Server. It manages logins and associated permissions such as grant, deny and revoke.
• serveradmin: Allows for changing server-wide options, can shut down the server, clear the SQL Server procedure cache, as well as a few other things.
• setupadmin: Allows for adding and removing Linked Servers using T-SQL
• sysadmin: Allows for full control of the SQL Server. Logins in this role can do anything.

What is a database user?

A database user object is much like the key to your car. The database user (the key) is a database level authentication object that is typically associated with a Server level login (the fob). This user object is what provides for connecting to one or more databases.

SQL Server Database Fixed Roles

• db_accessadmin: Allows for adding or removing access to a database
• db_backupoperator: Allows for backing up the database
• db_datareader: Allows for reading the data in a database
• db_datawriter: Allows for using insert, update or delete in a database
• db_ddladmin: Allows for creating, altering or dropping (deleting) database objects
• db_denydatareader: Denies the ability to read (SELECT) data.
• db_denydatawrtier: Denies the ability to write (add, update or delete) data.
• db_owner: Allows for full control of all database objects and data.
• db_securityadmin: Allows for managing access to a database. Has control over database users and their permissions.
• public: A role granted to al users in a database by default. It is best practice not to alter the permissions of this role.

The login (fob) allows for connecting to SQL Server and in turn is almost always associated with a database user (the key). That user allows connection to one or more databases. The server roles grant permissions for group members to do things with the SQL Server installation. The database roles grant permissions to members to do things with one or more databases.

Using Windows Active Directory to Manage SQL Server Access

To make managing SQL Server easier over the long run, whenever possible, it is best to use Windows Active Directory groups and appropriate server and database fixed roles. For example, if you need to grant read only access to the accounting database for the accounting department, or even just a couple of people in Accounting, then either ask the SysAdmins about Windows groups for the Accounting department and who is in those roles, or get that information from Active Directory Users and Groups yourself. Now, add windows groups to SQL Server and assign permissions. For more on that see this post about the topic.

Next Steps to Take

1. See this for MS Docs on how to create a login.
2. See this for MS Docs on how to create a database user.
3. If you have questions or comments, leave me a comment on this post, or message me on Twitter