What Every Accidental DBA Needs to Know Now: Basics of SQL Security

Author's Note

This is a continuation of a series I'm writing on those foundational topics every new DBA (accidental or otherwise) needs to have a grasp of the first day on the job. This series grows out of a session I present on the topic of "What Every Accidental DBA Needs to Know Now" that I'm delivering at IT/Dev Connections this year. If you're new to your DBA role, know someone in that situation, or just want a refresher on the basics of SQL Server Administration then please read this and the other articles in this series.

SQL Server Security v. Trusted Authentication

There are two branches of security schemes in Microsoft SQL Server: SQL Server Security and Trusted Authentication (also known as Windows Authentication.) SQL Server Security is a standard login & password combination whereas Trusted Authentication assumes the device attempting to connect to the SQL instance has been vetted and proven through domain authentication and that authentication is then passed on to the SQL Server instance: in the truest sense SQL assumes the login's identity is trusted by the domain hosting the SQL Server instance - that authentication has passed previously.

Users and applications attempting to connect to SQL Server under SQL Server Security will need to supply a login and password that has be created on the target SQL Server. Connections utilizing Trusted Authentication will only need a flag set in the connection string that states that trusted authentication is being used and in that case the identity of the end user or application service account is what is passed through as the security token. If there is a valid login that matches on the SQL Server then access will be granted.

Logins v. Users

Speaking of access there are two levels of access into a SQL Server instance: server (aka instance) logins and database users. Server logins allow an outside user (and for the sake of the rest of this article we'll use "user" as the word to identify any application, service, api, etc. that is attempting to make a connection to SQL Server) to make an initial connection to the instance of SQL Server. For SQL-based security this involves a login and password. In the case of Trusted Authentication this is a domain login.

There are a couple ways to create these logins: either through Transact-SQL or through the GUI. Since the documentation around using T-SQL to create logins is substantial its best served through a link to Microsoft SQL Server's official documentation. That said let's look at the GUI path for login creation. To launch the dialog for new logins connect to a SQL instance in SQL Server Management Studio (SSMS) within the Object Explorer then expand SecurityLogins. Right click Logins and select New Login...:

You'll then see the following dialog for creating a new login. Editing a login will display the same dialog:

The Create Login Dialog in Microsoft SQL Server

This is the General tab for the create (and edit) login process. It's also the only one that will differ between the two security schemes previously outlined. The General tab allows you to set:

Let's move onto the next tab: Server Roles. This page will allow you to select any roles at the SQL Server (aka Instance) level for the new login. Server Roles are a collection of rights, also known as securables, that are packaged into a collection so you don't have to grant individual rights to each securable. By default every login is a member of the public role which affords basic connection to the SQL instance. We will discuss each of the Server Roles that are shipped with Microsoft SQL Server later in this article.

Example of the Server Roles page

The next page in the Create Login dialog in SQL Server Management Studio is for user mappings. Each login can have a user in one or more databases. On this page you're able to create database users associated with the new login. That requires providing the following information:

Let's look at the example dialog I've completed for the SQLCRUISEskipper login:

Example of the User Mapping page

In this example the SQLCRUISEskipper login is granted rights to its default database (lifeboat) where the associated login is simply "skipper". It's default schema is skipper_only. The other two databases users for this login will be created in use the same user name as the login (which is usually going to be the case for ease of identification) and the default schema is dbo, which is the Microsoft SQL Server default for all user-defined objects - more on that in the next section though! In the case of the lifeboat database we are only granting public database role membership which simply allows connection to the database without any real additional permissions.

Next we have the Securables page which are securables at the server/instance level. As mentioned previously securables are grants given to objects. Securables exist at both the server and database levels. Securables are granted typically when:

In this example I granted SQLCRUISEskipper public server role membership and on this screen also allowed it to view any definition of objects that exist within the server level:

The Create Login Securables page

Finally we have the Status page. This page allows you to grant or deny access for the login (by default "Grant" is selected). So yes, you can create a login, provide rights, create associated users, and then deny access preventing its use. You can also go back into this dialog for an existing login and deny access to the SQL instance as well. Working similar to that is the ability to enable or disable a login. Finally you can view the status of a login (ie: has the login  been locked out due to too many incorrect login attempts with a bad password?)

Status Page in Create Login dialog

Behind the scenes you can examine the T-SQL that gets generated and executed for these settings in the GUI:

USE [master]GOCREATE LOGIN [SQLCRUISEskipper] FROM WINDOWS WITH DEFAULT_DATABASE=[lifeboat]GOUSE [lifeboat]GOCREATE USER [skipper] FOR LOGIN [SQLCRUISEskipper]ALTER USER [skipper] WITH DEFAULT_SCHEMA=[skipper_only]GOCREATE SCHEMA [skipper_only] AUTHORIZATION [skipper]GOUSE [lifeboat_blank]GOCREATE USER [SQLCRUISEskipper] FOR LOGIN [SQLCRUISEskipper]ALTER USER [SQLCRUISEskipper] WITH DEFAULT_SCHEMA=[dbo]GOUSE [Lifeboat_Messy]GOCREATE USER [SQLCRUISEskipper] FOR LOGIN [SQLCRUISEskipper]ALTER USER [SQLCRUISEskipper] WITH DEFAULT_SCHEMA=[dbo]GOuse [master]GOGRANT VIEW ANY DEFINITION TO [SQLCRUISEskipper]GO

At this point its important to note how the database users are related back to server logins. Remember that I stated names do not need to match between the two objects. This is because these objects are not joined in the system tables based upon name but rather by something called a sid (or security id). This allows for the ability to not be tied to a login name having to match a user name or encountering a situation where you possibly restore a database with, say, a user named "trevor" to a SQL server instance where there is already a server login named "trevor" but it's a completely different individual who should not have rights to that database. By using a sid, you remove that possibility. If we look at the two system views that present the data that represents logins and users we can see how these objects look inside SQL Server. I created a login and a user called "professor" and set the default database to lifeboat while being sure to create a user in lifeboat to correspond. The system view that provides information on logins is the sys.server_principals view (sys is the schema). Meanwhile the database user information is presented through the sys.database_principals view in each database. These views can be joined on sid:

SELECT name, sid, principal_id, type_desc, default_database_name FROM sys.server_principals WHERE name = 'professor';SELECT name, sid, principal_id, type_desc, default_schema_nameFROM lifeboat.sys.database_principals WHERE name = 'professor';

These views can be joined on sid:

sys.server_principals v. sys.database_principals

There are issues that arise though should the sids not match. As that is a more advanced topic I'll dedicate an entire follow-up article just to determine when that happens, how to mitigate it, and how to resolve it should you encounter this concept known as orphaned users.

Schemas v. Roles

There were quite a few "let's talk about that later in the article" comments above. It's later in the article now so let's discuss those additional details around schemas and roles. Schemas and Roles in SQL Server terminology are both collections. Schemas are a collection of objects (tables, views, stored procedures and so forth.) Roles are a collection of rights: server roles for rights at the server/instance level and database roles, which are collections of rights within a given database. That is where any similarity ends however.

Regarding schemas there are two default schemas to highlight: sys and dbo. The sys schema is the de-facto owner of all system objects in Microsoft SQL Server. These are also referred to in many system views and Dynamic Management Objects as ms_shipped objects which you'll see denoted in bit-typed columns within applicable views as is_ms_shipped with a value of 1 for system objects and 0 for user objects. You can also create schemas to fit your needs. Earlier I mentioned a billing schema for accounting objects as an example. If a user creates an object without specifying a schema the object will be created in the default schema for that user. If no default schema is defined for a user then their user when created dbo is assigned by default as their default schema.

When querying objects it's best practice to use fully-qualified names. This means specifying the database name, schema name and object name rather than just the object name. What does this look like in practice? If I have a table named tblFoo in the dbo schema of the SQLCruise database I could create a query that would select all columns and rows from that table in a few different ways:

--==========================--OPTION 1: fully qualified--==========================SELECT * FROM SQL_Cruise.dbo.tblFoo;--==========================--OPTION 2: schema qualified--==========================SELECT * FROM dbo.tblFoo;--==========================--OPTION 3: non-qualified--==========================SELECT * FROM tblFoo;

Each one of these would execute properly if there was only one table named tblFoo in the SQL_Cruise database and the current database context was the SQL_Cruise database. However only option one if going to execute properly no matter what database you're currently connected to on the SQL Server instance hosting the SQL_Cruise database. Option 2 will execute if you're connected to the SQL_Cruise database no matter how many schemas have a tblFoo since you're specifying the dbo schema. Option 3 will throw an error if there are multiple schemas in the SQL_Cruise database with a tblFoo as shown below where I've created both a dbo.tblFoo table as well as a user.tblFoo table:

USE [SQL_Cruise]GOCREATE SCHEMA [user] AUTHORIZATION [dbo]GOCREATE TABLE dbo.tblFoo (id INT);CREATE TABLE [user].tblFoo (id INT);SELECT * FROM tblFoo;

​Msg 208, Level 16, State 1, Line 9Invalid object name 'tblFoo'.

Yes, that's correct - the object does exist but you get the Invalid object name error. Don't take that ever for the assumption there is no object by that name. It could also point to a syntax issue.

Regarding roles, pre-defined roles are included with Microsoft SQL Server at both the server and database layers. However you can create your own should you find situations where you're going to be rolling out the same permissions to many users. Creating custom roles allows you to only have to provision those rights once: when you create the role - rather than for each user or login (depending on database or server roles.) In addition to saving time this also prevents the chance of not being consistent when you push the rights to multiple users or logins.

For a complete list of Microsoft shipped Server Roles and Database Roles navigate using the respective hyperlinks provided. In a follow up article to this series - as I begin to expound on these basic understandings towards more advanced topics I'll be covering how to create roles, add users or logins to these roles, and associate rights with these roles at the database and server levels.

Conclusion

The topic of security in Microsoft SQL Server is a critical one. It's also broad and deep and has a great deal of terminology involved. I hope I've hit my goal of explaining the various terms and giving a high level view of these objects in practice. I have quite a few more topics to cover in this introductory level series but I will be cycling back to more advanced topics that rise from this article quite soon. Explaining all these various items in great detail would make for a very cumbersome article to digest (as much as it would be to write.) As always, thank you for reading to the end and I look forward to your comments and hopefully helping you navigate the mysteries of SQL as a new DBA.