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 Security\Logins. 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:
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:
- Login name - just as it sounds this is the name of the login and what will be used for authentication. If this is a Windows\Trusted Authentication-based login you'll need to name it in terms of DOMAIN\LOGIN where LOGIN is the login within the domain you'll be authenticating from which, if it is not within the same domain as the SQL Server instance needs to have trust between its domain and the SQL Server's domain.
- Password - If using SQL Server authentication the password text box is enabled and you'll supply both a login name and an associated password.
- Password Policy and Expiration Settings - The checkboxes for password policy enforcement and expiration will also be enabled under SQL Server authentication and those policies are enforced based upon whatever is in place within Active Directory in the domain which the SQL Server resides. If you're setting up a SQL Server login you can also decide whether to allow the user to change their password on their initial login. This prevents the DBA from having access to the end user's login.
- Certificates, Keys, Credentials - Since this is an introduction-level article we'll not expand into discussing certificates, keys, and credentials at this time.
- Default Database - When a connection to a SQL Server is made there are a couple steps that happen: authentication is verified (a login must exist for the user's domain credentials if Windows/Trusted authentication is being used or a valid login and password combination must have been passed to the instance of SQL Server. That's the first hurdle. The second is that the validated login also has an associated user object within the default database - the database that is initially set as the login's context after validating their identity. Even if the first hurdle is cleared, unless there is an associated database user for the login's default database a connection will not be established and an entry will be made in the SQL error log denoting this. There are exceptions though: if the login's server role is significant enough to state that by default the login has implicit rights into each database then the corresponding user in the default database is not necessary. Note however I got ahead of myself because we've yet to discuss database users or server roles. Those come later. Suffice to say that when you choose a default database through the GUI an associated database user is not created. You're just stating what the default database is supposed to be. You will use the User Mapping tab for the Create Login dialog to create an associated database user.
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.
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:
- Database - click the checkbox next to the database you want to create an associated user in for the login.
- User Name - a user object's name does not have to match the login name and you'll see below how you can change that.
- Default Schema - each database user should also be assigned to a Default Schema. A schema is a collection of database objects, segregated logically (but not necessarily physically) from other objects in the database. It's possible to grant rights for a user or group of users to all objects in a given schema, for instance granting all users in the accounting department (or the accounting application's service account) certain rights to all the objects in the Billing schema, yet not granting access to those objects to other users. By setting a default schema for a database user it means that there is no need to include the schema name in any T-SQL calls to the database when addressing objects in that schema. It also mean if the user has been granted rights to create objects they'll be created within that schema by default unless a schema name is specified when creating objects. We will touch upon the concept of schemas a bit more later in this article.
- Database Role Membership - just as is the case at the instance/server level, each database has pre-defined collections of rights rolled up into roles. We will discuss what these database roles are that are shipped with Microsoft SQL Server later in this article.
Let's look at the example dialog I've completed for the SQLCRUISE\skipper login:
In this example the SQLCRUISE\skipper 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:
- A pre-defined role is either too broad (affords too many other rights) to a login
- A pre-defined role or set of roles is not encompassing completely in all of the rights required for a login
In this example I granted SQLCRUISE\skipper public server role membership and on this screen also allowed it to view any definition of objects that exist within the server level:
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?)
Behind the scenes you can examine the T-SQL that gets generated and executed for these settings in the GUI:
USE [master]
GO
CREATE LOGIN [SQLCRUISE\skipper] FROM WINDOWS WITH DEFAULT_DATABASE=[lifeboat]
GO
USE [lifeboat]
GO
CREATE USER [skipper] FOR LOGIN [SQLCRUISE\skipper]
ALTER USER [skipper] WITH DEFAULT_SCHEMA=[skipper_only]
GO
CREATE SCHEMA [skipper_only] AUTHORIZATION [skipper]
GO
USE [lifeboat_blank]
GO
CREATE USER [SQLCRUISE\skipper] FOR LOGIN [SQLCRUISE\skipper]
ALTER USER [SQLCRUISE\skipper] WITH DEFAULT_SCHEMA=[dbo]
GO
USE [Lifeboat_Messy]
GO
CREATE USER [SQLCRUISE\skipper] FOR LOGIN [SQLCRUISE\skipper]
ALTER USER [SQLCRUISE\skipper] WITH DEFAULT_SCHEMA=[dbo]
GO
use [master]
GO
GRANT VIEW ANY DEFINITION TO [SQLCRUISE\skipper]
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_name
FROM lifeboat.sys.database_principals
WHERE name = 'professor';
These views can be joined on sid:
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]
GO
CREATE SCHEMA [user] AUTHORIZATION [dbo]
GO
CREATE TABLE dbo.tblFoo (id INT);
CREATE TABLE [user].tblFoo (id INT);
SELECT *
FROM tblFoo;
Msg 208, Level 16, State 1, Line 9
Invalid 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.
