Chapter 6
Administering security and permissions

In this chapter, we look at the Microsoft SQL Server permissions, from authentication through to data access, starting from the ground floor on up. We discuss practical administrative tasks that DBAs need to do and why, including handling orphaned security identifiers (SIDs), security migration, SQL Server Agent runtime permissions, and more. Many of these principles apply equally to SQL Server and Microsoft Azure SQL Database; when they differ, we will point this out.

• A principal is an entity that is given access.

• The scope of a principal depends on that to which it can be given access.

• Server Authentication is made via principals named logins.

• Database access is made via principals named users.

• Other types of principals include roles (server, database, and application)

In each database, a user can be associated with one server login.

Logins might not be associated with users in all databases, and it is possible for users to exist without any association to a login, though oftentimes this occurs accidentally. (We talk about this scenario more later on.) Table 6-1 presents a quick comparison.

Table 6-1 Comparison of users and logins

Database user	Server login
• Set a database context • Linked to a server login • Does not have a password • Assigned to database roles • Stored in the user database • Brought along with a User DB Restore • Given access to SELECT, UPDATE, EXECUTE, CREATE TABLE, and so on.	• Authenticates sessions to a SQL Server • Can be linked to Active Directory (Windows Authentication) • Or can have a password (SQL Authentication) • Assigned to server roles • Stored in the master database • Not affected by User DB Restore • Given access to RESTORE, CONNECT, CREATE DATABASE, and so on.

It is important to understand this terminology and the differences between these objects, not just for interacting with SQL Server, but for communicating with fellow SQL administrators and developers.

Logins and users are not associated by their names that you see in SQL Server Management Studio; instead, they are associated by a SID binary value. How the SID is generated for each login is based on the type of authentication used by the login. You can view the SID for a login in the sys.server_principals view.

For Windows-authenticated logins, the Windows SID for the account or group is used. For domain accounts, this SID will be the same from Windows server to Windows server.

In a typical business environment, using Windows Authentication means that account creation/termination, Active Directory security group membership, and password policy are handled by an existing corporate security administration infrastructure. In fact, using Active Directory security groups is a best practice for centrally managed role-based authentication (RBA).

The SID assigned to a newly created SQL Server–authenticated login is generated by the SQL Server. This is why two logins with the same names on two SQL Server instances will have different SIDs (more on this later).

You can use SQL Server Authentication to connect to on-premises SQL Server instances, Azure virtual machine–based SQL Server instances, and databases in Azure SQL Database, but other methods of authentication are preferred in all cases.

SQL Server Authentication also covers database-level authentication, which is used by contained database users.

The last three authentication types are exclusive to Azure-based resources, specifically Azure SQL Database or Azure SQL Data Warehouse, using Azure Active Directory (Azure AD) credentials.

Currently, this feature is limited to authentication with Azure AD accounts for connecting to a database in Azure SQL Database or Data Warehouse, though further Microsoft development around two-factor authentication for server access is likely—and welcomed.

This method, like the next two Azure AD–based authentication methods, was first supported by SQL Server Management Studio as of SQL Server 2016.

This authentication method makes it possible for you to use your Azure account to sign in to SQL Server from outside Azure AD via a user name and password. This is more secure than SQL Server–based authentication because it is linked to an Azure AD account that is, in theory, managed by an existing Enterprise Security group.

You can use this method, for example, to grant Microsoft Office 365 accounts direct access to a database in Azure SQL Database over the web.

As of SQL Server 2016 and SQL Server Data Tools for Visual Studio 2015, SQL Server Management Studio supports this authentication method.

For example, you can use this authentication method when connected via Remote Desktop to an Azure AD–authenticated session on a virtual machine (VM) in Azure.

You can create a login that is mapped directly to a certificate or to an asymmetric key. Secure access to the SQL Server instance is then possible by any client with the public key of the certificate, using a nondefault endpoint that you create specifically for this type of access.

The Service Broker feature of SQL Server, used for asynchronous messaging and queueing, supports Certificate-Based Authentication, for example.

You also can use these logins to sign database objects, such as stored procedures. This encrypts the database objects and is common for when the code of a third-party application is proprietary and not intended to be accessible by customers.

For example, the ##MS_PolicyEventProcessingLogin## and ##MS_PolicyTsqlExecutionLogin## logins, created automatically with SQL Server, are certificate-based logins.

In the case of SQL Authentication, there are no differences when connecting to a SQL Server instance running on Linux with SQL Server Management Studio.

It is also possible to join the Linux server to the domain (by using the realm join command), using Kerberos, and then connect to the SQL Server instance on Linux just as you would connect to a SQL Server instance on Windows Server. The steps necessary are detailed in the SQL Server on Linux documentation at https://docs.microsoft.com/sql/linux/sql-server-linux-active-directory-authentication.

Any time you restore a (noncontained) database from one SQL Server to another, the database users transfer, but the server logins in the master database do not. This will cause SQL Server–authenticated logins to break, but Windows-authenticated logins will still work.

Why?

The key to this common problem that many SQL DBAs face early in their careers is the SID and, particularly, how the SID is generated.

For Windows-authenticated logins based on local Windows accounts and SQL Server–authenticated logins, this SID will differ from server to server.

The SID for the login is associated with the user in each database based on the matching SID. The names of the login and user do not need to match, but they almost certainly will, unless you are a SQL DBA intent on confusing your successors. But know that it is the SID, assigned to the login and then applied to the user, that creates the association.

The most common problem scenario is as follows:

1. A database is restored from one SQL Server instance to another.

2. The SIDs for the Windows-authenticated logins and their associated users in the restored database will not be different, so Windows-authenticated logins will continue to authenticate successfully and grant data access to end users via the database users in each database.

   The SIDs for any SQL Server–authenticated logins will be different on each server. So, therefore, will the associated database users’ SIDs in each database. When restoring the database from one server to another, the SIDs for the server logins and database users no longer match. Their names will still match, but data access cannot be granted to end users.

3. The SID must now be rematched before SQL Server–authenticated logins will be allowed access to the restore database.

Original state

Server1

SQL Login = Katherine

SID = 0x5931F5B9C157464EA244B9D381DC5CCC

Database User = Katherine

SID = 0x5931F5B9C157464EA244B9D381DC5CCC

Server2

SQL Login = Katherine

SID = 0x08BE0F16AFA7A24DA6473C99E1DAADDC

Then, the database is restored from Server1 to Server2. Now, we find ourselves in this problem scenario:

Orphaned SID

Server1

SQL Login = Katherine

SID = 0x5931F5B9C157464EA244B9D381DC5CCC

Database User = Katherine

SID = 0x5931F5B9C157464EA244B9D381DC5CCC

Server2

SQL Login = Katherine

SID = 0x08BE0F16AFA7A24DA6473C99E1DAADDC

Database User = Katherine

SID = 0x5931F5B9C157464EA244B9D381DC5CCC ← Orphaned SID

Click here to view code image

ALTER USER Katherine WITH LOGIN = Katherine;

The SID of the user is now changed to match the SID of the login on Server2; in this case, 0x08BE0F16AFA7A24DA6473C99E1DAADDC. Again, the relationship between the Server Login and the Database User has nothing to do with the name.

You can use the script that follows to look for orphaned SIDs. This should be a staple of your DBA toolbox. It’s important to understand that this script only guesses that logins and user names should have the same name. If you are aware of logins and users that do not have the same name and should be matched on SID, you will need to check for them manually.

Click here to view code image

Select
     DBUser_Name        =         dp.name
,    DBUser_SID         =         dp.sid
,    Login_Name         =         sp.name
,    Login_SID          =         sp.sid
,    SQLtext            =         'ALTER USER [' + dp.name + ']
                                  WITH LOGIN = [' + ISNULL(sp.name, '???') + ']'
      from sys.database_principals dp
      left outer join sys.server_principals sp
      on dp.name = sp.name
      where
            dp.is_fixed_role = 0
      and sp.sid <> dp.sid
      and dp.principal_id > 1
      and dp.sid <> 0x0
      order by dp.name;

You should check for orphaned SIDs every time you finish a restore that brings a database from one server to another; for example, when refreshing a preproduction environment.

Click here to view code image

CREATE LOGIN [Katherine] WITH PASSWORD=N'strongpassword', SID =
0x5931F5B9C157464EA244B9D381DC5CCC;

Using the SID option, you can manually create a SQL login with a known SID so that your SQL Server–authenticated logins on multiple servers will share the same SID. Obviously, the SID must be unique on each instance.

In the previous code example we used sys.server_principals to identify orphaned SIDs. You can also use sys.server_principals to identify the SID for any SQL Server–authenticated login.

Creating SQL Server–authenticated logins with a known SID is not only helpful to prevent orphaned SIDs, but it could be crucially timesaving for migrations involving large numbers of databases, each with many users linked to SQL Server–authenticated logins, without unnecessary outage or administrative effort.

We’ll look more closely at this topic later in this chapter when we examine SQL Server security migrations.

If the content of this chapter refers to SQL Server Authentication as redundant, often unnecessary, and problematic for administrators, that’s intentional: using SQL Server Authentication (aka configuring the SQL Server instance in mixed mode) creates additional administrative overhead of which DBAs need to be aware.

As the DBA of a SQL instance, be sure to emphasize to developers and application administrators that Windows Authentication, via named domain accounts or service accounts, is always preferred to SQL Server Authentication.

Use SQL Server Authentication to connect to SQL Server instances only in special cases; for example, when Windows-authenticated accounts are impossible to use or for network scenarios involving double-hop authentication when Kerberos is not available.

Since SQL Server 2005, user names and passwords of SQL Server–authenticated logins are no longer transmitted as plain text during the login process. And, unlike early versions of SQL Server, passwords are not stored in plain text in the database. However, any SQL Server–authenticated login could potentially have its password reverse-engineered by a malicious actor who has access to or a copy of the master database .mdf file. For these reasons and more, Windows-authenticated accounts are far more secure.

This is not an option for Azure SQL databases.

A login will fail if its default database is not accessible, including if the database is restoring, offline, or dropped from the instance. The login will fail even if it is a member of the sysadmin server role, so you should never change the default database of a known administrator login. (The sysadmin role has all permissions to the SQL Server instance. We will talk more about the sysadmin role and other server-level roles later in this chapter.)

Similarly, if you specify an inaccessible database as the Initial Catalog or Initial Database in various connections strings, your login attempt might fail.

Administrator logins should keep the master (the default setting) or TempDB as their default database because the SQL Server cannot start up without the presence of those databases.

This guidance follows even for logins that are not a member of the sysadmin server role. There might be some cases for which a default database set to a user database is appropriate; for example, for a login that will be used only for a single database. In this way, the default database setting might be helpful because login will be denied new connections if that single database is inaccessible, moved to another instance, or dropped from the instance.

The policies applied from the machine’s local security policy, inherited from the domain if applicable, include minimum length and complexity requirements.

The Enforce Password Policy check box is selected by default when you open the Login – New dialog box (Figure 6-2) in SQL Server Management Studio, but you can clear the check box to turn off this option. So, with SQL Server Management Studio it is possible to create a login with a noncomplex (or even blank) password. When you create it in code, the CHECK_POLICY option is not required but defaults to ON.

You should keep in mind that if you turn on the CHECK_POLICY option on an existing login that did not already have it on, the existing password is not affected. The policy will be enforced the next time the password is changed. Applications and end users can still sign in to the SQL instance by using the existing, potentially noncomplex password. Subsequent DBAs might assume that the password policy is enforced on the existing password. Therefore, do not turn on CHECK_POLICY on a login without then immediately changing the password, or at least setting the MUST_CHANGE option at the same time so that the user must change her password on the next login.

In addition to enforcing password policy, you can optionally enforce a maximum password age by selecting the Enforce Password Expiration check box. You also can force a user to change her password on her next login, but keep in mind that although SQL Server Management Studio has built-in behavior to allow for this password to be changed with a simple dialog box, other applications might not have the ability for users to change their passwords interactively, and they will instead see only a failed login.

Windows Authentication means that SQL DBAs will need to sign in to a Windows instance using their domain credentials. Then, they connect to the SQL Server instance with Windows Authentication and begin their work. DBAs use this same method whether they are connecting to a production environment SQL Server or a development environment SQL Server, though next we’ll talk about using different credentials for each.

To illustrate, let’s consider the following scenario: tasked with backing up and restoring a database from the production environment to the development environment, the DBA uses the connections already open in SQL Server Management Studio, copies the backup to a network share, and then begins the restore. The restore is 50 percent complete when a user calls to ask, “Is the SQL Server down?”

What do you think has happened?

If your DBA team isn’t already using two or more Windows-authenticated accounts, you should consider segmenting each DBA’s production database access from the account they use for the rest of their day-to-day activities, including preproduction systems, but also email, office applications, Office 365, and more.

Consider creating “admin-level” accounts for each DBA that have no preproduction access, office applications or Office 365 access, Virtual Private Network (VPN), or even internet access. The idea is to encourage your DBA team to use its admin accounts only for administrative activities.

For example, Katherine is a DBA and uses DomainKatherine to access her “everyday” activities such as email, instant messaging, preproduction SQL instances, source control, Office 365, VPN, and more. But this domain account has limited access to production SQL Servers—for example, she can access server-level DMV’s, activity levels, SQL Agent job history, and the SQL Server error log. But she cannot create logins, read or update live production data, alter databases, and so on.

To perform any of those tasks, Katherine opens a remote desktop session to another server using Domainadmin-Katherine. This activity is deliberate and requires heightened awareness—the production databases are important! Starting SQL Server Management Studio from within the remote desktop session, Domainadmin-Katherine is a member of the sysadmin server role and can accomplish anything she needs to in the production environment. When she’s done, she logs out.

Domainadmin-Katherine also has no VPN access; thus, if it is compromised, it cannot be used to both gain access to the corporate network remotely and access SQL Servers.

In the end, you might have good regulatory, corporate policy, security, or private reasons to separate a DBA’s “everyday” access from production SQL Server instance access. However, access to the production servers using their “admin-” account (many other naming conventions are common) requires deliberate steps and mental awareness of the task at hand, so the risk of accidentally running intended-for-development tasks in production is considerably reduced.

Later in this chapter, in the section “Creating custom server roles,” we talk about fixed server roles that you can use to separate duties among a team of DBAs, and a custom server role that you can create to set up read-only access to an entire server.

The sa account also has utility as the authorization (aka owner) for database objects, schemas, availability groups, and the databases themselves. This known administrator account, however, has obvious potential consequences.

Applications, application developers, and end users should never use the sa account. This much should be obvious. If it is, consider changing the password and assigning any entities who were using it the permissions necessary and appropriate for their role. The sa account, like any SQL Server–authenticated account, could potentially have its password reverse-engineered by a malicious actor who has access to or a copy of the master database .mdf file.

The sa account is a common vector for brute-force attacks to compromise a SQL Server. For this reason, if your SQL Server is exposed to the internet, we recommend that you rename or disable the sa account.

Because its password is commonly known to multiple administrators, it also can serve as an anonymous backdoor for malicious or noncompliant activity by current or former employees.

Because your SQL Server instance’s DBAs should be using Windows Authentication for all administrative access (more on that later), no DBA should actively use the sa account. Its password doesn’t need to be known to DBAs. There are no use cases nor best practices that require accessing the SQL Server by using the sa account.

Beginning with SQL Server 2008, this group was no longer added to SQL Server instances by default, because it is an obvious and serious security back door. Although it was potentially convenient for administrators, it was also targeted by malicious actors.

Do not add the BUILTINAdministrators group to your SQL Server instance—it is no longer there by default for a reason.

Service accounts are usually given the minimal permissions needed to run the services to which they are assigned by SQL Server Configuration Manager. For this reason, do not use the Windows Services (services.msc) to change SQL Server feature service accounts. Changing the instance’s service account with the Windows Services administrative page will likely result in the service’s failure to start.

It is not necessary to grant any additional SQL Server permissions to SQL Server service accounts. (You might need to grant additional NT File System (NTFS)–level permissions to file locations, etc.) Although the SQL Server Agent service account likely needs to be a member of the sysadmin role, the SQL Server service account does not. For these reasons and more, different service accounts for different services is necessary.

You also should never grant the NT AUTHORITYSYSTEM account, which is present by default in a SQL Server instance, any additional permissions. Many Windows applications run under this system account and should not have any nonstandard permissions.

Databases created or altered on a SQL Server instance by using CONTAINMENT = PARTIAL bypass the server’s authentication, creating authentication directly at the database level, and creating the confusing concept of a “Contained SQL user with password.”

Table 6-2 compares contained database users to database users and server logins (previously shown in Table 6-1).

Table 6-2 Comparing users, logins, and contained users

Database user	Server login	Contained database user
• Set a database context • Linked to a server login • Does not have a password • Assigned to database roles • Stored in the user database • Brought along with a User DB Restore • Given access to SELECT, UPDATE, EXECUTE, CREATE TABLE, etc.	• Authenticates to a SQL Server • Can be linked to Active Directory (Windows Authentication) • Or can have a password (SQL Authentication) • Assigned to server roles • Stored in the master database • Not affected by User DB Restore • Given access to RESTORE, CONNECT, CREATE DATABASE, etc.	• Authenticates to a SQL Server • Has a password (SQL Authentication) • Assigned to database roles • Stored in the database • Brought along with a User DB Restore • Given access to database-level permissions typically granted to both logins and users

You can move contained databases from SQL Server instance to instance without the need to re-create server-level objects, such as server-level security (logins). Some features are only partially contained, however. Use the views sys.dm_db_uncontained_entities and sys.sql_modules to return information about uncontained objects or features. By determining the containment status of the elements of your database, you can discover what objects or features must be replaced or altered to promote containment.

A significant security factor to be aware of with contained databases is that any user with the ALTER ANY USER permission, and of course any user who is a member of the db_owner fixed database role, can grant access to the database and therefore the server’s resources. Users with this permission could grant access to net new users and applications independently of the SQL Server instance’s administrators.

Though the concept of creating databases with the specific CONTAINMENT option does not exist in Azure SQL Database (in v12), contained databases were developed specifically to assist with the concept of a cloud-based database as a service, to allow Azure SQL databases to be mobile between different cloud hosts, and to assure very high levels of availability.

• BULK INSERT

• DELETE

• INSERT

• MERGE

• SELECT

• UPDATE

Three more DML statements are deprecated and are needed to modify permissions for the deprecated text, ntext, and image data types. Do not use them, except for legacy support.

• READTEXT

• UPDATETEXT

• WRITETEXT

• ALTER

• CREATE

• DROP

• TRUNCATE TABLE

• ENABLE TRIGGER

• DISABLE TRIGGER

• UPDATE STATISTICS

• GRANT

• DENY

• REVOKE

GRANT allows a permission to occur, DENY disallows it, and REVOKE removes the current GRANT or DENY on the object specified. Here is the basic syntax:

GRANT|DENY|REVOKE permission
ON objecttype::Securable
TO principal

However, the ON portion of the permission statement could be optional to apply to a current context. For example, you can omit the portion to GRANT a permission to a principal for the current database by doing this:

Click here to view code image

GRANT EXECUTE TO [domainkatie.sql]

Keep in mind that this statement would grant EXECUTE permissions for any stored procedure in the database, but not to each stored procedure individually. Any stored procedures created in the future could also be run by the principal.

Click here to view code image

GRANT SELECT on SCHEMA::sales to [domainkatie.sql];
DENY SELECT on OBJECT::sales.InvoiceLines to [domainkatie.sql];

As a result, the database user [domainkatie.sql] would have permissions to SELECT every object in the sales schema, except for the sales.SalesInvoice table. Let’s assume that no other permissions or role memberships have been granted to the database user [domainkatie.sql].

If this is run by [domainkatie.sql]

Click here to view code image

use WideWorldImporters;
go
SELECT TOP 100 * FROM sales.Invoices;
SELECT TOP 100 * FROM sales.InvoiceLines;

the result is this:

Click here to view code image

Msg 229, Level 14, State 5, Line 4
The SELECT permission was denied on the object 'InvoiceLines', database 'WideWorldIm-
porters', schema 'sales'.

(100 row(s) affected)

The sales.Invoices table was still accessible to [domainkatie.sql] because it was in the sales schema, even though the user was denied access to sales.InvoiceLines.

Let’s continue to explore this scenario. First, from an administrative account, let’s remove the GRANT and DENY we issued in the previous command. As a result, we are back to our original state. No permissions or role memberships have been granted to the database user [domainkatie.sql]:

Click here to view code image

REVOKE SELECT on SCHEMA::sales to [domainkatie.sql]
REVOKE SELECT on OBJECT::sales.Invoices to [domainkatie.sql]

This results in the following when [domainkatie.sql] runs the same pair of SELECT statements:

Click here to view code image

Msg 229, Level 14, State 5, Line 4
The SELECT permission was denied on the object 'Invoices', database
'WideWorldImporters', schema 'sales'.
Msg 229, Level 14, State 5, Line 5
The SELECT permission was denied on the object 'InvoiceLines', database
'WideWorldImporters', schema 'sales'.

Now, from an administrative account, let’s GRANT overlapping GRANT and DENY statements, only this time the GRANT and DENY are reversed:

Click here to view code image

DENY SELECT on SCHEMA::sales to [domainkatie.sql]
GRANT SELECT on OBJECT::sales.InvoiceLines to [domainkatie.sql]

This also results in permission denied errors when [domainkatie.sql] runs the same pair of SELECT statements:

Click here to view code image

Msg 229, Level 14, State 5, Line 4
The SELECT permission was denied on the object 'Invoices', database
'WideWorldImporters', schema 'sales'.
Msg 229, Level 14, State 5, Line 5
The SELECT permission was denied on the object 'InvoiceLines', database
'WideWorldImporters', schema 'sales'.

The DENY on the entire sales schema overlapped and won.

As a DBA, you should be aware of permissions that your IT colleagues can be granted short of the server sysadmin role or database db_owner roles.

Many of these common securables are server-level and so are not supported in Azure SQL Database v12. They are not supported even when run in the master database of the Azure SQL Database server.

The following subsections present some examples of permissions that you can grant developers in a production database that do not grant them access to view table data or change database objects.

A developer might need this permission to trace the SQL Server as part of a troubleshooting expedition into the SQL Server. (Though you should remind him after granting this permission that traces are deprecated, and extended events are a much better diagnostic tool.

For more information, see Chapter 13.

Because ALTER TRACE is a server-level securable, developers would be able to trace all events on the server, from all databases and processes. Certain sensitive events cannot be traced; for example, the T-SQL statement of CREATE LOGIN for a SQL authenticated login.

A developer might need this permission to trace the SQL Server as part of a troubleshooting expedition into the SQL Server, after you tell them about extended events. This will grant them access to create extended events sessions with T-SQL commands, but will not give them access to view server metadata in the New Extended Events Session Wizard in SQL Server Management Studio. For that, they will need one further commonly granted developer permission: VIEW SERVER STATE (discussed shortly).

Similar to traces, extended events sessions can capture events on the server from all databases and processes. You cannot trace certain sensitive events; for example, the T-SQL statement of CREATE LOGIN for a SQL authenticated login.

However, as of Azure SQL Database v12, for developers to view extended events sessions, you must grant them an ownership-level permission CONTROL DATABASE (discussed in just a moment). In production environments, this isn’t recommended for developers or non-administrators.

This permission at the server level allows the principal to view a large number of server metadata objects, system views, and dynamic management views (DMVs), many of which could be invaluable to a developer who is looking to troubleshoot, analyze, or performance tune.

Many of the DMVs mentioned in Chapter 12 need only the VIEW SERVER STATE permission.

This is a relatively safe permission to grant. With VIEW SERVER STATE, the principal still has no access to data, database objects, logins, or passwords. This is a read-only permission at the server level and is a great alternative to granting administrative permissions.

This provides permission to the developer to view the T-SQL code of database objects, without the rights to read or change the objects. This is known as the metadata of the objects.

Developers might need access to verify that code changes have deployed to production; for example, to compare the code of a stored procedure in production to what is in source control. This is also a safe permission to grant developers because it does not confer any SELECT or any modification permissions.

Instead of going through each object in a database, you might instead want to GRANT VIEW ANY DEFINITION TO [principal]. This applies the permission to all objects in the current database context. You can revoke it easily.

As part of performance tuning, developers almost certainly need access to view a specific query’s runtime plan, for queries against any database on the server. Seeing the execution plan is not possible even if the developer has the appropriate SELECT or EXECUTE permissions on the database objects in the query. This applies to both estimated and actual runtime plans.

The SHOWPLAN permission, however, is not enough: developers also must have the appropriate read or read/write permissions to run the query that generates the plan.

Non-administrators and developers can still view aggregate cached runtime plan statistics via the DMV sys.dm_exec_cached_plans without the SHOWPLAN permission, if they have the VIEW SERVER STATE permission.

Introduced in SQL Server 2014, this is a quick way to allow a login to set its context to any current or future database on the server. It grants no other permissions. Although it does not create a user in each database for the login, it behaves as if a user had been granted in each database for login, and has been given no other rights.

This permission alone doesn’t seem very useful, but it could be handy for setting up a DBA’s “everyday” account or, rather, granting this securable to a Windows-authenticated group to which all DBA “everyday” accounts belong. Consider granting this permission and the next, as well, SELECT ALL USER SECURABLES, to grant read-only access to a server, including each database on the server.

Introduced in SQL Server 2014, this permission grants the ability to SELECT from all readable database objects in all user databases. The object types include tables, views, table-valued functions. EXECUTE permissions are not implied. This is a fast way to give administrators access to read from all current and future databases and their objects, but is not appropriate for non-administrative end users or application logins.

Keep in mind that production data could contain sensitive, personally identified, or personal health information. In some regulatory environments, granting this permission would not be appropriate and might fail regulatory audit, unless SELECT permission on sensitive tables was denied, masked, or those tables were encrypted, perhaps with the Always Encrypted feature.

Similarly, you could also use this permission to DENY SELECT access to all data on a server. This could ensure that administrators can accomplish a variety of other server-level tasks in production systems with safe assurance that they cannot casually access data using their “everyday” accounts. Keep in mind that members of the sysadmin server role would not be affected by any DENY permission.

For more information on encryption of sensitive data, including Always Encrypted, see Chapter 7.

The IMPERSONATE permission allows the user of the EXECUTE AS statement and also the EXECUTE AS clause to run a stored procedure. This permission can create a complicated administrative environment and should be granted only after you have an understanding of the implications and potential inappropriate or malicious use. With this permission, it is possible to impersonate a member of the sysadmin role and assume those permissions, so this permission should be granted in controlled scenarios, and perhaps only temporarily.

This permission is most commonly granted for applications that use EXECUTE AS to change their connection security context. You can grant the IMPERSONATE permission on logins or users.

Logins with the CONTROL SERVER permission already have IMPERSONATE ANY LOGIN permission, which should be limited to administrators only. It is unlikely that any application that uses EXECUTE AS would need its service account to have permission to IMPERSONATE any login that currently or ever will exist. Instead, service accounts should be granted IMPERSONATE permissions only for known, appropriate, and approved principals that have been created for the explicit purpose of being impersonated temporarily.

An EXECUTE AS statement should eventually be followed by a REVERT. We use EXECUTE AS and discuss more about this statement later in this chapter.

This effectively grants all permissions on a server or database and is not appropriate for developers or non-administrators.

Granting the CONTROL permission is not exactly the same as granting membership to the sysadmin server role or a db_owner database role, but it has the same effect. Members of the sysadmin role are not affected by DENY permissions, but owners of the CONTROL permission might be.

Beginning with SQL Server 2008, “ownership” was redefined as “authorization.” Ownership is now a casual term, whereas AUTHORIZATION is the concept that establishes this relationship between an object and a principal.

Changing the AUTHORIZATION for any object, including a database, is the preferred, unified terminology than describing and maintaining object ownership with a variety of syntax and management objects. In the case of a database, however, although AUTHORIZATION does not imply membership in the db_owner role, it does grant the equivalent highest level of permissions.

For this reason, and for reasons involving the database ownership chain, named individual accounts (for example, your own [domainfirstname.lastname]) should not be the AUTHORIZATION of a database.

The problem—which many developers and administrators do not realize—is that when a user creates a database, that user is the “owner” of the database, and that user principal’s SID is listed as the owner_sid in sys.databases.

If the database’s “owner_sid” principal account was ever to be turned off or removed in Active Directory, and you move the database to another server without that principal, you will encounter problems with IMPERSONATION and AUTHORIZATION of child objects, which could surface as a wide variety of errors or application failures. This is because the owner_sid is the account used as the root for authorization for the database. It must exist and be a valid principal.

For this reason, DBAs should change the AUTHORIZATION of databases to either a known high-level, noninteractive service account or to the built-in sa principal (sid 0x01). It is a standard item on any good SQL Server health check.

If there are databases with sensitive data that should not allow any access from other databases, they should not have the same owner_sid as less-secure databases, and you should not turn on Cross Database Ownership Chaining at the server level (it is not by default).

Click here to view code image

ALTER AUTHORIZATION ON DATABASE::[databasename]  TO [server_principal];

In SQL Server databases, the new owner can be a SQL Server–authenticated login or a Windows-authenticated login. To change the ownership of a database by running the ALTER AUTHORIZATION statement, the principal that’s running needs the TAKE OWNERSHIP permission and the IMPERSONATE permission for the new owner.

The new owner of the database must not already exist as a user in the database. If it does, the ALTER will fail with the error message: “The proposed new database owner is already a user or aliased in the database.” You will need to drop the user before you can run the ALTER AUTHORIZATION statement.

For Azure SQL Database, the new owner can be a SQL Server–authenticated login or a user object federated or managed in Azure AD, though groups are not supported.

To change the ownership of a database in Azure SQL Database, there is no sysadmin role of which to be a member. The principal that alters the owner must either be the current database owner, the administrator account specified upon creation, or the Azure AD account associated as the administrator of the database. As with any permission in Azure SQL Database, only Azure AD accounts can manage other Azure AD accounts. You can manage SQL Server–authenticated accounts by SQL Server–authenticated or Azure AD accounts.

Simply put, EXECUTE permissions on stored procedures and SELECT permissions on views allow a user to access data from the objects in the same database that are included in the procedure or view definition, without explicit permissions on the objects included in the procedure or view definition.

This is an important concept to understand, so that you as a DBA can follow a principle of least privilege and grant only the minimum rights necessary for an application or end user to access data. We could even go so far as to DENY SELECT access to application users and still provide them with data access via the stored procedures, view, and functions we have designed for appropriate data access.

In the case of stored procedures there are three important caveats that would break this abstraction and require that whoever is running the procedure also have permissions to the underlying database objects:

• The procedure cannot perform any ALTER operations, which are not abstracted by the stored procedure. This includes IDENTITY_INSERT.

• The procedure does not perform any dynamic SQL Server command such as sp_executesql or EXEC (@SQL) to access data. This is a built-in safeguard against SQL Server injection attacks.

• The underlying database objects referenced by the stored procedure have an ownership chain; that is, they have the same ownership chain.

Not violating any of those conditions, thanks to the intact database permission chain, you can GRANT EXECUTE permission to a principal and no other permissions, and you can run the procedure successfully. Now, the database principal has no way to access the database objects outside of your stored procedure.

Views and functions work similarly, without the first two caveats (they obviously don’t apply), and you can use them to provide a horizontal or vertical partition of a table to users who do not have explicit SELECT permissions to that table.

Click here to view code image

USE [master]
GO
CREATE LOGIN DenyPrincipal WITH PASSWORD=N'deny'
GO
GRANT CONNECT SQL TO DenyPrincipal
ALTER LOGIN DenyPrincipal ENABLE
GO
USE [WideWorldImporters]
GO
CREATE USER DenyPrincipal FOR LOGIN DenyPrincipal
GO
CREATE TABLE dbo.DenyPrincipalTable (
ID INT IDENTITY (1,1) NOT NULL CONSTRAINT PK_DenyPrincipalTable PRIMARY KEY,
Text1 VARCHAR(100) )
GO
INSERT INTO dbo.DenyPrincipalTable (Text1) VALUES ('test')
GO 3

We’ve inserted three rows into the dbo.DenyPrincipalTable.

Now let’s test various ways to access this table, without granting any permissions to it.

Test permissions using a view Now, we will create a view on the table dbo.DenyPrincipalTable and try to access it. Note that we just created the [DenyPrincipal] database principal and have not granted it any other permissions. Outside of what is granted to the [public] role (more on that later), [DenyPrincipal] has no permissions. Execute this and all following code in this section while logged in as a member of the sysadmin role:

Click here to view code image

CREATE VIEW dbo.denyview WITH SCHEMABINDING AS

SELECT DenyView = text1 FROM dbo.DenyPrincipalTable

GO

GRANT SELECT ON dbo.denyview TO [DenyPrincipal]

GO

The [DenyPrincipal] principal now has access to the view dbo.DenyView, but not to the table dbo.DenyPrincipalTable.

Now, attempt to read data from the table:

Click here to view code image

EXECUTE AS USER = 'DenyPrincipal';
SELECT * FROM dbo.DenyPrincipalTable;
GO
REVERT;

This results in the following error:

Click here to view code image

Msg 229, Level 14, State 5, Line 41
The SELECT permission was denied on the object 'DenyPrincipalTable', database 'Wide-
WorldImporters', schema 'dbo'.

Why? Remember that we have granted no permissions to the DenyPrincipalTable. This is as intended.

But the user [DenyPrincipal] can still access the data in column text1 via the view:

Click here to view code image

EXECUTE AS USER = 'DenyPrincipal';
select * from dbo.DenyView;
GO
REVERT;

Here are the results:

DenyView
test
test
test

Note also that [DenyPrincipal] has access only to the columns (and if desired, the rows) that the view dbo.DenyView provides. Applications can use views and stored procedures to provide appropriate SELECT, INSERT, UPDATE and DELETE access to underlying table data by blocking access to rows and columns and no SELECT access directly to the table.

For more information on techniques to grant appropriate data access, including Always Encrypted, see Chapter 7.

Test permissions using a stored procedure Let’s prove the same abstraction of permissions by using a stored procedure, and then also demonstrate when it fails:

Click here to view code image

CREATE PROC dbo.DenySproc AS
BEGIN
SELECT DenySproc = text1
FROM dbo.DenyPrincipalTable;
END
GO
GRANT EXECUTE ON dbo.DenySproc to [DenyPrincipal];
GO

EXECUTE AS USER = 'DenyPrincipal';
EXEC dbo.DenySproc;
GO
REVERT;
GO

Here are the results:

DenySproc
test
test
test

It works! Without any access to the dbo.DenyPrincipalTable table, the user DenyPrincipal was able to access the table data.

Now, let’s break the stored procedure’s ability to abstract the permissions:

Click here to view code image

CREATE PROC dbo.DenySproc_adhoc
AS
BEGIN
DECLARE @sql nvarchar(1000)
SELECT @sql = 'select ExecSproc_adhoc = text1 FROM dbo.DenyPrincipalTable';
EXEC sp_executesql @SQL;
END
GO
GRANT EXECUTE ON dbo.DenySproc_adhoc to [DenyPrincipal];
GO
EXECUTE AS USER = 'DenyPrincipal';
EXEC dbo.DenySproc_adhoc;
GO
REVERT;

Here are the results:

Click here to view code image

Msg 229, Level 14, State 5, Line 75
The SELECT permission was denied on the object 'DenyPrincipalTable', database 'Wide-
WorldImporters', schema 'dbo'.

Note that we used the dynamic SQL command sp_executesql, passing in a string of T-SQL, which as a security feature automatically breaks the permission abstraction. Note also that the failure message indicates that the user [DenyPrincipal] does not have permission to SELECT from the underlying table.

Test permissions using a table-valued function Finally, you’ll find that table-valued functions work in the same way. Let’s create an inline table-valued function to demonstrate:

Click here to view code image

CREATE FUNCTION dbo.DenyFunc ()
RETURNS TABLE
AS RETURN
    SELECT DenyFunc = Text1
    FROM dbo.DenyPrincipalTable;
GO
GRANT SELECT ON dbo.DenyFunc TO [DENYPRINCIPAL];
GO
EXECUTE AS USER = 'DenyPrincipal';
SELECT * FROM DenyFunc();
GO
REVERT;
GO

Here are the results:

DenyFun
test
test
test

Using a function also works! Without any access to the dbo.DenyPrincipalTable table, the user DenyPrincipal was able to access the table data via the table function DenyFunc().

Access a table even when SELECT is denied Let’s take it one step further and DENY SELECT permissions to [DenyPrincipal]. Will we still be able to access the underlying table data via a view and stored procedure?

Click here to view code image

DENY SELECT ON dbo.DenyPrincipalTable TO [DenyPrincipal];
GO
EXECUTE AS USER = 'DenyPrincipal';
SELECT * FROM dbo.denyview; --test the view
GO
EXEC dbo.DenySproc; --test the stored procedure
GO
SELECT * FROM DenyFunc();
GO
REVERT;
GO

Yes! And, again, here are the results:

DenyView
test
test
test

DenySproc
test
test
test

DenyFunc
test
test
test

Logins are granted membership to other server roles most commonly for situations in which the DBA team will be separated in terms of responsibility or when service accounts need access to perform server-level operations.

Note that server roles are not a feature of Azure SQL Database, though database roles (covered later in this chapter) are provided.

To manage user assignment server roles, SQL Server Management Studio provides the membership page in the Login Properties dialog box (Figure 6-3). By default, only the Public check box is selected (you cannot clear it). Initially, a new login is assigned to only the public built-in server role, from which that login cannot be removed.

You also can use T-SQL to add and remove members from server roles, as shown in this example:

Click here to view code image

ALTER SERVER ROLE serveradmin ADD MEMBER [domainkatie.sql]
GO
ALTER SERVER ROLE processadmin DROP MEMBER [domainkatie.sql]
GO

Let’s explore the list of built-in server roles, beginning with the unlimited sysadmin role.

• sysadmin. The sysadmin server role has unrestricted access to all operations. It is appropriate for DBA administrative accounts only. Although software vendors or other accounts can request membership to the sysadmin server role to simplify their installations, this is not appropriate, and a responsible DBA should push back on granting membership to this role.

  When granting the sysadmin role, it is unnecessary to grant membership to any other server role. Granting membership to every server role is comedically the equivalent of pushing all the buttons on an elevator.

  The sysadmin role is also granted certain other permissions, especially in SQL Server Management Studio. The sysadmin role is nearly the equivalent of the GRANT CONTROL SERVER permission, with some differences. The most notable is the fact that the sysadmin role is unaffected by any DENY permissions; for example:

  Click here to view code image

  USE Master;
  GO
  CREATE LOGIN [domainkatie.sql] FROM WINDOWS;
  GO
  GRANT CONTROL SERVER TO  [domainkatie.sql] ;
  DENY VIEW SERVER STATE TO  [domainkatie.sql];
  GO
  EXECUTE AS LOGIN = 'domainkatie.sql';
  SELECT * FROM sys.dm_exec_cached_plans;
  GO
  REVERT;
  GO

  Where the DENY would have no effect on a user that is a member of the sysadmin server role, here is the result:

  Click here to view code image

  Msg 300, Level 14, State 1, Line 7
  VIEW SERVER STATE permission was denied on object 'server', database 'master'.
  Msg 297, Level 16, State 1, Line 7
  The user does not have permission to perform this action.

• bulkadmin. The bulkadmin server role has been granted permissions to perform BULK INSERT operations from local files. It could be suitable for service accounts for unattended processes that perform automated mass data movement. Bulk operations from any local folders are allowed; this is the main difference between granting membership to this role and granting the ADMINISTER BULK OPERATIONS permission.

  Principals with this permission can use bcp, SQL Server Integration Services, or T-SQL to perform BULK INSERT statements. Note that for BULK INSERT operations, permissions to INSERT into the destination tables are additionally required, and permissions to ALTER TABLE for the destination table might also be required.

• dbcreator. Service accounts for applications that generate databases automatically, such as Microsoft SharePoint On-Premises, can be granted membership to this server role instead of sysadmin, to allow databases to be created. You can create new databases directly or via the restore from a backup.

  The dbcreator server role has the CREATE ANY DATABASE permission. Keep in mind that this permission also gives the user the capability to ALTER and DROP any database for which that user has AUTHORIZATION.

• diskadmin. A subset of the serveradmin fixed server role, the diskadmin server role has the rights to affect drive resources; for example, to create and drop backup devices.

  In addition to other permissions, diskadmin has been granted the ALTER RESOURCES permission, which is fairly limited, poorly documented, and is not recommended to grant individually. Instead, grant membership only to the diskadmin role.

• processadmin. This role grants admin-level visibility to sessions and requests, and to view and reset server performance information. These permissions can prove useful to non-administrators who monitor activity. You will likely find it useful for these types of users to combine processadmin with SQL Agent job-related permissions, discussed later in this chapter.

  The role is granted the ALTER ANY CONNECTION permissions, allowing members of this role to view and stop sessions. The role is also granted VIEW and ALTER SERVER STATE, making it possible for members to view a wide array of helpful DMVs.

  Any connection can view its own sessions in the sys.dm_exec_sessions, but with the ALTER ANY CONNECTION permission, a connection can view all sessions and requests active on the server, including system sessions below session_id 50.

  The ALTER SERVER STATE allows access to DBCC SQLPERF, a well-documented command that can view and reset wait and latch statistics as well as view space utilization data from transaction log files. In Azure SQL Database, resetting wait and latch statistics is not supported.

• public. To be as plain as possible, no permissions should ever be granted to the public server role (or corresponding public roles in each database, discussed later.)

  Every login is a member of the public server role. Every user is a member of the public database role. Do not grant any additional permissions to the public roles, because they will be granted to all current and future logins and users.

  securityadmin. The security admin permission should be considered as close to sysadmin as it gets. The ability to create logins at the server level and users in each database, to grant and revoke permissions at the server and database level, should not be granted lightly. Members of the securityadmin role can create and add logins to the sysadmin role, so membership should be given scrutiny equivalent to the sysadmin role.

  Membership in the securityadmin role is required by some service accounts to delegate the management of security to applications, especially those that create databases procedurally and thus need to provision security for them; for example, the setup and farm accounts for Microsoft SharePoint On-Premises installations.

  The securityadmin role possesses the ALTER ANY LOGIN permission and more, including security permissions inside each database, plus management of account status and passwords for SQL Server–authenticated logins.

• Serveradmin. Membership in the serveradmin server role grants the ability to alter and create endpoints, sp_configure settings, and to SHUTDOWN the SQL Server instance. The role is also granted VIEW and ALTER SERVER STATE, allowing the permission to view a wide array of helpful DMVs.

  The ALTER SERVER STATE allows access to DBCC SQLPERF, a well-documented command that can view and reset wait and latch statistics as well as view space utilization data from transaction log files. In Azure SQL Database, resetting wait and latch statistics is not supported.

  The serveradmin has no access to data or database-level settings or security-related permissions, and so is often combined with other roles to provide a subset of administrative capability.

• Setupadmin. The setupadmin role only grants permissions to deal with linked servers using T-SQL statements. To use SQL Server Management Studio to set up linked servers, the sysadmin role is required.

Inside a DBA team, we might seek to break down duties and grant permissions to suit, for example, junior administrators or high availability administrators, who should not need full sysadmin rights.

The key to creating custom server roles is to have a good understanding of the permissions involved to perform certain tasks and then divvying up permissions. You also can make custom server roles to be members of any built-in server role except for sysadmin.

Similarly, you have the ability to create custom database roles in each database. We discuss that later in this chapter.

Following is an example of a potentially useful custom server role. You could create it to allow read-only access to administrators to a server. In the section “Providing logins to the DBA team” earlier in the chapter, we discussed separating the Windows credentials used by DBAs into an “everyday” account and an administrative account. This custom server role could be useful to provide read-only access to a DBA’s “everyday” account.

Click here to view code image

--Create a new custom server role

CREATE SERVER ROLE SupportViewServer;

GO
--Grant permissions to the custom server role
--Run DMVs, see server information

GRANT
VIEW SERVER STATE
to SupportViewServer;
--See metadata of any database

GRANT
VIEW ANY DATABASE
to SupportViewServer;

--Set context to any database

GRANT
CONNECT ANY DATABASE
to SupportViewServer;

--Permission to SELECT from any data object in any databases

GRANT
SELECT ALL USER SECURABLES
to SupportViewServer;

GO

--Add the DBA team's accounts
ALTER SERVER ROLE SupportViewServer ADD MEMBER [domainKatherine]
ALTER SERVER ROLE SupportViewServer ADD MEMBER [domainColby]
ALTER SERVER ROLE SupportViewServer ADD MEMBER [domainDavid]
…

Similar to server roles, database roles (Figure 6-4) in each database provide for packages of permissions to ease the provisioning of database users. You also can create your own user-defined database roles to further customize the packages of permissions granted to users.

Let’s examine the list of built-in database roles, their permissions, and appropriate use.

• db_owner. The db_owner database role’s name is a bit misleading because it can have many members. It provides unrestricted access to the database to make any/all changes to the database and contained objects. This is not the same as being identified as the owner_sid within the database. Changing the AUTHORIZATION for the database to a principal confers the same rights as db_owner because the server principal will be mapped to the dbo built-in user when accessing the database, which is a member of the db_owner role.

  The db_owner role does not grant the CONTROL DATABASE permission but is equivalent. Similar to how the sysadmin server role operates, the db_owner ignores any DENY permissions.

  The only users in the database who can add or remove members from built-in database roles are members of the db_owner role and the principal that holds AUTHORIZATION for the database.

For more on AUTHORIZATION, the equivalent of “ownership” terminology, see the section “Ownership versus authorization” earlier in the chapter.

• db_accessadmin. The db_accessadmin role not only has the right to create and manage database users and database roles, but to create schemas, grant permissions on all database objects. Among other permissions, the db_accessadmin has the ALTER ANY LOGIN and CREATE SCHEMA permissions.

  Members of the db_accessadmin role can create users with or without an association to existing logins. However, members of the db_accessadmin database role cannot fix orphaned users or change the login that a user is assigned to, because they do not have the CONTROL DATABASE permission.

  Even though members of the db_accessadmin role can create schemas, they cannot change the authorization for schemas, because they do not have the ALTER ANY SCHEMA permission.

  Keep in mind that in a contained database, members of the db_accessadmin role (and the db_owner role) can create users with passwords, allowing new access to the SQL Server. Because of the high level of control over permissions and membership in the database, this role should be considered as important as the db_owner role and not given out lightly.

  Because of the high level of control over permissions and membership in the database, this role should be considered as important as the db_owner role and not given out lightly.

• db_backupoperator. The db_backupoperator role has permissions to BACKUP DATABASE (including full and differential backups), BACKUP LOG, and to CHECKPOINT the database. Note that this role has no rights to RESTORE the database, because that requires server-level permissions found in the sysadmin and dbcreator fixed server roles, or the owner of the database.

• db_datareader. The db_datareader role given rights to SELECT from any object in the database, including tables and views. This is a heavy-handed and brute-force way to give access to application accounts, and it ignores the ability for views to abstract the permissions necessary to read from tables. It is preferable to add permissions to individual objects or schemas instead of granting SELECT access to all objects, your database might not always need to grant SELECT permissions to all tables and views.

• db_datawriter. The db_datawriter permission can INSERT, UPDATE, or DELETE any table in the database. This is a heavy-handed and brute-force way to give access to application accounts, and it ignores the ability for stored procedures to provide approved or audited methods for data changes by abstracting the permissions necessary to write to tables. You should instead grant write permissions on specific objects to specific principals, or use stored procedures to accomplish writes.

• db_ddladmin. The db_ddladmin role has the rights to perform DDL statements to alter any object in the database, but it has no permission to create or modify permissions, users, roles, or role membership. This role also does not have the permission to EXECUTE objects in the database, even objects that members of this role create. There is no built-in database role that provides EXECUTE permissions, which you should grant more granularly than at the database level.

• db_denydatareader. The inverse of db_datareader, the db_denydatareader role denies SELECT on all objects.

• db_denydatawriter. The inverse of db_datawriter, the db_denydatawriter role denies INSERT, UPDATE, and DELETE on all objects.

• db_securityadmin. Members of the db_securityadmin role can manage fixed database roles (but not change their membership), create and manage custom roles, role membership, and GRANT, REVOKE, and DENY permissions on database objects in the database.

  Note that members of the db_accessadmin role can create and manage users, but members of the db_securityadmin cannot.

• public. To be as clear as possible, no permissions should ever be granted to the public database role (or the public server role, discussed earlier in this chapter).

  Every database user is a member of the public database role. Do not grant any additional permissions to the public roles in any database, because they will be granted to all current and future users.

As with server roles, the key to creating custom database roles is to have a good understanding of the permissions involved and the appropriateness of data access. It is unlikely that all users in a database will need the same data access, and not all read-only access will be the same.

Click here to view code image

--Create a new custom database role
USE [WideWorldImporters]
GO

-- Create the database role
CREATE ROLE SalesReadOnly AUTHORIZATION [dbo];
GO

-- Grant access rights to a specific schema in the database
GRANT EXECUTE
ON [Website].[SearchForSuppliers]
TO SalesReadOnly;
GO

Click here to view code image

--Add User to built-in database role
ALTER ROLE db_owner ADD MEMBER [domainkatie.sql];
GO

-- Add User to custom database role
ALTER ROLE SalesReadOnly ADD MEMBER [domainJames]
ALTER ROLE SalesReadOnly ADD MEMBER [domainAlex]
ALTER ROLE SalesReadOnly ADD MEMBER [domainNaomi]
ALTER ROLE SalesReadOnly ADD MEMBER [domainAmos]
ALTER ROLE SalesReadOnly ADD MEMBER [domainShed]
GO

--Remove User from database role
ALTER ROLE SalesReadOnly DROP MEMBER [domainShed];
GO

Examples could be misconfiguration of security, misconfiguration of the Resource Governor, misconfiguration of prompts created FOR LOGON, or other interesting conditions that block even members of the sysadmin server role.

Only one member at a time of the server sysadmin role can connect using the DAC, so similar to a database in single-user mode, do not attempt to connect to the DAC via Object Explorer in SQL Server Management Studio. (Object Explorer cannot connect to the DAC, by design.)

In SQL Server Management Studio, you might see an error message that says Failed To Connect To Server, but in the background, the query window has in fact connected to the DAC, evidenced by the server name in the bottom of the window. This occurs only when reusing a previously connected query window in SQL Server Management Studio. You can avoid the error by opening a new Database Engine Query window in SQL Server Management Studio, but the result is the same.

The DAC also has resource limitations intended to limit the impact of DAC commands. You will not be able to perform all administrative tasks through the DAC; for example, you cannot issue BACKUP or RESTORE commands from the DAC. You should instead use the DAC only for diagnostic and remediation of the issues that prevent normal access, and then return to a normal connection. Do not use the DAC to carry out long-running queries against user data, DBCC CHECKDB, or to query the dm_db_index_physical_stats DMV.

When connecting to a database in Azure SQL Database with the DAC, you must specify the database name in your connection string or connection dialog box. Because you cannot change database contexts with the USE syntax in Azure SQL Database, you should always make connections directly to the desired database in Azure SQL Database, via the database or initial catalog parameters of the connection string.

There are several ways to sign in to a SQL Server instance or Azure SQL Database using the DAC via a login that is a member of the sysadmin role:

• In SQL Server Management Studio, open a new query or change the connection of a query, providing the servername as usual, but preceded by ADMIN:; for example:

  ADMIN:servername

  Or, for a named instance (ensure that the SQL browser is running):

  Click here to view code image

  ADMIN:servernameinstancename

• From a command prompt, you can connect to the DAC via sqlcmd with the parameter -A; for example:

  Click here to view code image

  C:UsersKatie>sqlcmd -S servername -A

  Or, for a named instance (ensure that the SQL browser is running):

  Click here to view code image

  C:UsersKatie>sqlcmd -S servernameinstancename -A

• In SQL Server Management Studio, change a query window to SQLCMD mode, and then use the following query:

  :CONNECT ADMIN:servername

  Or, for a named instance (ensure that the SQL browser is running):

  Click here to view code image

  :CONNECT ADMIN:servernameinstancename

• In Windows PowerShell, the DedicatedAdministratorConnection parameter of the Invoke-SQLCMD cmdlet provides a connection to the DAC. For example:

  Click here to view code image

  Invoke-SQLCmd -ServerInstance servername -Database master -Query "Select @@Server-
  name" -DedicatedAdministratorConnection

  Or, for a named instance (ensure that the SQL browser is running):

  Click here to view code image

  Invoke-SQLCmd -ServerInstance servernameinstnacename -Database master -Query
  "Select @@Servername" -DedicatedAdministratorConnection

We recommend that you do because it could prove invaluable to gain access to a SQL Server when remote desktop protocol or similar technologies are unable to connect to the Windows host of the SQL Server instance. Turning on the Remote DAC does not require a service restart.

The endpoint port that SQL Server uses to listen to DACs is announced in the SQL Server error log upon startup; for example, you will see this shortly after the SQL Server service starts: “Dedicated admin connection support was established for listening locally on port 1434.” This is the default port for default instances, whereas named instances will use a randomly-assigned port, changed each time the service is started.

Connecting to the DAC remotely with SQL Server Management Studio is also possible by addressing the port number of the DAC instead of the ADMIN: syntax. For example, providing a connection string in SQL Server Management Studio to servernameinstancename,49902 would connect to the DAC endpoint.

Moving Windows-authenticated logins, SQL Server–authenticated logins, and all server-level permissions are three discrete steps that are accomplished via different methods. The end goal of your maturity as a DBA is to use SQL Server Management Studio’s dialog boxes as little as possible, because GUI-driven solutions to this scenario are the most time consuming and could result in an unagreeable amount of button-clicking. T-SQL scripts are superior in terms of manageability, repeatability, and to deepen your understanding of the underlying security objects.

In a server migration, keep in mind that all database-level permissions, database roles, and users will be moved with the backup/restore of each database.

In this section, we discuss various methods of migrating security, some of which apply to either SQL Server instances or Azure SQL Database.

You use SQL Server Data Tools to create a new SQL Server Integration Services project. As Figure 6-5 shows, this provides an in-the-box, do-it-yourself alternative to the steps that follow, which involve custom scripts to migrate permissions from one server to another. The task is highly configurable, allowing for both Windows- and SQL Server–authenticated logins to be created on the far side, with their original SIDs if desired. A Fail/Replace/Skip option is provided for login names that already exist on the destination.

Logins created by the Transfer Logins task arrive at the destination server turned off; you must turn them on again before you can use them.

Unfortunately, until the SQL Server Integration Services SQL Management Object connection manager supports an Azure SQL Database connection, this solution does not work for Azure SQL Database.

Keep in mind that this SQL Server Integration Services task does not move any of the role memberships or server permissions that these logins might have been granted on the source instance.

To read more, see the sections “Moving server roles by using T-SQL (SQL Server only)” and “Moving server permissions by using T-SQL (SQL Server only)” later in this chapter.

You do not necessarily need to use Object Explorer in SQL Server Management Studio for this operation. The system catalog view sys.server_principals contains the list of Windows-authenticated logins (types 'U' for Windows user, and 'G' for Windows group). The default_database_name and default_language_name also are provided and you can script them with the login.

Here’s a sample script:

Click here to view code image

--Create windows logins
SELECT
CreateTSQL_Source = 'CREATE LOGIN ['+ name +']

FROM WINDOWS WITH

DEFAULT_DATABASE=['+default_database_name+'],

DEFAULT_LANGUAGE=['+default_language_name+']'
FROM sys.server_principals
WHERE type in ('U','G')
AND name NOT LIKE 'NT %'
AND is_disabled = 0
ORDER BY name, type_desc;

Keep in mind that this script does not generate T-SQL for any of the role memberships or server permissions that these logins might have been granted on the source instance.

To read more, see the sections “Moving server roles by using T-SQL (SQL Server only)” and “Moving server permissions by using T-SQL (SQL Server only)” later in this chapter.

With the aid of these stored procedures, it is possible to generate a hash of a SQL Server–authenticated password with its login and then re-create the SQL Server–authenticated login on another server with the same password. It is not possible to reverse-engineer the SQL Server–authenticated login password, but you can re-create it without the need to change dependent application connection strings.

But don’t stop there! Note that these provided stored procedures only re-create the SQL Server–authenticated logins; they do not re-create any of the role memberships or server permissions that those logins might have been granted on the source instance. In the next two sections, we discuss moving server roles and server permissions.

Click here to view code image

--SERVER LEVEL ROLES
SELECT DISTINCT
   SERVER_ROLE_NAME  =  QUOTENAME(R.NAME)
,  ROLE_TYPE  =  R.TYPE_DESC
,  PRINCIPAL_NAME  =  QUOTENAME(M.NAME)
,  PRINCIPAL_TYPE  =  M.TYPE_DESC
,  SQL2008R2_BELOW_CREATETSQL      = 'SP_ADDSRVROLEMEMBER  @LOGINAME=  '''+M.NAME+''',  
                                       @ROLENAME = '''+R.NAME+''''
,  SQL2012_ABOVE_CREATETSQL      = 'ALTER SERVER ROLE [' + R.NAME + '] ADD MEMBER
                                      [' + M.NAME + ']'
FROM      SYS.SERVER_ROLE_MEMBERS AS RM
INNER JOIN SYS.SERVER_PRINCIPALS R ON RM.ROLE_PRINCIPAL_ID = R.PRINCIPAL_ID
INNER JOIN SYS.SERVER_PRINCIPALS M ON RM.MEMBER_PRINCIPAL_ID = M.PRINCIPAL_ID
WHERE R.IS_DISABLED = 0 AND M.IS_DISABLED = 0 -- IGNORE DISABLED ACCOUNTS
AND M.NAME NOT IN ('DBO', 'SA') -- IGNORE BUILT-IN ACCOUNTS
ORDER BY QUOTENAME(R.NAME);

Instead, we advise that you script the permissions to re-create on the destination server by using internal catalog views. Following is a sample script:

Click here to view code image

--SERVER LEVEL SECURITY
   PERMISSION_STATE  =  RM.STATE_DESC
,  PERMISSION  =  RM.PERMISSION_NAME
,  PRINCIPAL_NAME  =  QUOTENAME(U.NAME)
,  PRINCIPAL_TYPE  =  U.TYPE_DESC
,  CREATETSQL_SOURCE = RM.STATE_DESC + N' ' + RM.PERMISSION_NAME +
    CASE WHEN E.NAME IS NOT NULL THEN 'ON ENDPOINT::[' + E.NAME + '] ' ELSE '' END +
    N' TO ' + CAST(QUOTENAME(U.NAME COLLATE DATABASE_DEFAULT) AS NVARCHAR(256)) + ';'
FROM SYS.SERVER_PERMISSIONS RM
INNER JOIN SYS.SERVER_PRINCIPALS U
ON RM.GRANTEE_PRINCIPAL_ID = U.PRINCIPAL_ID
LEFT OUTER JOIN SYS.ENDPOINTS E
ON E.ENDPOINT_ID = MAJOR_ID AND CLASS_DESC = 'ENDPOINT'
WHERE U.NAME NOT LIKE '##%' – IGNORE SYSTEM ACCOUNTS
AND U.NAME NOT IN ('DBO', 'SA'– IGNORE BUILT-IN ACCOUNTS
ORDER BY RM.PERMISSION_NAME, U.NAME;

And because you do not have access to sys.server_principals, sys.server_role_members, or sys.server_permissions, scripting these server-level permissions in Azure SQL Database isn’t possible. (The system catalog view sys.server_principals is a dependent of sp_help_revlogin.)

Further, creating a login with a password HASH is not supported in Azure SQL Database (as of v12).

As of this writing, the solution for migrating Azure SQL Database logins from one server to another is to have the original script or to re-create the logins on the destination server with a new password.

The three types of Azure-authenticated principals are actually stored in the Azure SQL database, not at the Azure SQL server level, and are administered via the Azure portal. Like other database users and permissions, you can move those principals to a destination server along with the database itself.

Other security objects include Linked Server connections and SQL Server Audits, for which you can generate scripts, albeit without passwords in the case of Linked Servers. Given this hindsight, it is definitely advantageous to securely store your linked server creation scripts with their passwords.

You also should re-create SQL Server Credentials (and any corresponding proxies in use by SQL Server Agent) on the destination server, although you cannot script credentials (you must re-create them manually). You can script proxies in SQL Server Agent by using SQL Server Management Studio, and you should re-create them, including their assigned subsystems.

There are some third-party products available to accomplish the task. There is also a free package of Windows PowerShell cmdlets available, including some designed to assist with security migrations. You can find these in the dbatools free open-source GPL-licensed Windows PowerShell project, which is available at http://www.dbatools.io.

If your SQL Server resides on a VM, performing a clone of the instance at the VM level might provide some transportability for the VM from one environment to another, to bypass the process of rebuilding a SQL Server instance altogether. For version upgrades, hardware changes, or partial migrations, a VM-level clone is obviously not a solution.

However, restoring a master database from one server to another involves myriad potential changes to server-specific encryption keys, service account, user permissions, and server identification information. These changes might or might not be supported configuration changes. The process is not outlined in any support documentation, and we do not recommend it.

Keep in mind that a migration of the master database is advisable only when the destination server of the restored database has the identical volume letters and NTFS permissions, access to the same service accounts, in addition to the same SQL Server version and edition.