Azure Active Directory Universal Authentication with MFA

The Authentication drop-down list in the Connect to Server dialog box (refer to Figure 12-1) contains an Azure Active Directory – Universal with MFA option, where MFA stands for multifactor authentication. Universal Authentication uses Azure MFA, and when you attempt to connect to your resources with it, SSMS opens an Azure security dialog box that lets you authenticate.

In addition to the password, you are initially prompted to log in with the universal login client, and asked to provide your domain account and whatever extra authentication the AD administrator has configured—for example a PIN, smart card, authenticator app, or even a code that is emailed or texted to you.

As is true for any account on the Internet, it is always better to use MFA whenever possible. It can be more burdensome to use, but someone who gets your password cannot then log in without the second factor in their possession.

Azure Active Directory password authentication

Azure AD accounts can be used for authentication with a username and password for users that have been created in the Azure tenant and granted access. This authentication method makes it possible for you to employ your Azure account to sign into SQL Server via a username 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 an Azure AD account with a Microsoft Office 365 license direct access to a database in Azure SQL Database over the web.

Azure Active Directory integrated authentication

This mode is analogous to Windows Authentication, but using an Azure AD account rather than a Windows Server AD account. Once logged into a Windows machine with your Azure AD account, you can use that Azure AD account to authenticate to the database in a manner very similar to Windows Authentication. No username or password is requested; instead, your profile’s local connections are used to connect to the SQL Server. For example, you can use this authentication method when connected via Remote Desktop to an Azure AD–authenticated session on an Azure VM.

This also includes connections from App Services using a managed identity. Managed identities (much like managed service accounts, mentioned in Chapter 4) enable you to eliminate secrets from your app by allowing Azure to manage the principal used by the application to connect to SQL Server. To enable Azure AD authentication in SQL Server, you must register the server in the Azure portal.

• For more information about Azure AD authentication in SQL Server 2022, see https://cloudblogs.microsoft.com/sqlserver/2022/07/28/azure-active-directory-authentication-for-sql-server-2022/.

Azure Active Directory access token

Some command line clients like PowerShell and SQLCMD allow you to authenticate using an Azure AD token created using the Microsoft Authentication Library (MSAL). The documentation for SQLCMD details how to create a token using the -P parameter.

• For more details, visit https://learn.microsoft.com/sql/connect/odbc/linux-mac/connecting-with-sqlcmd.

Server authentication mode

When we discuss server authentication mode in this section, we are referring to SQL Server on a Windows or Linux instance. There are two security modes in which your SQL Server can operate: Windows Authentication mode and Mixed mode (or, as the SSMS UI calls it, “SQL Server and Windows Authentication Mode”).

The goal is to use Windows Authentication mode for access whenever possible. 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 (in other words, configuring the SQL Server instance in Mixed mode) creates additional administrative overhead and possible security holes of which DBAs must be aware.

If, however, you are in a situation where this is not possible (for example, when it is impossible to use Windows-authenticated accounts due to a certain client API or for network scenarios involving double-hop authentication when Kerberos is not available), it is important to configure your server logins properly. Be sure to enforce password length and password changes, just like you would any login to any service. You don’t want users to use PASSWORD1 for their SQL Server login, as it will be one of the first logins attempted in a brute force attack.

This isn’t to say that standard logins are insecure. Since SQL Server 2005, usernames and passwords for SQL Server–authenticated logins are no longer transmitted as plain text during the login process. And, unlike with early versions of SQL Server, passwords are not stored in plain text in the database. But because passwords are stored in the database with the server, anyone who gets access to a backup of your master database could rather easily discover the passwords for an instance. For these reasons and more, Windows-authenticated accounts are far more secure.

For the best of both worlds, try using an Azure SQL Managed Instance. That way, you can use the Azure AD authentication modes covered earlier in this chapter and pass in an Azure AD login and password. Azure AD logins are only represented as SID values locally, so this is a far more secure method of managing users, because the instance holds no private user information (and neither do backups and copies of your databases).

Enforcing password policies

As stated, one problem with SQL Server Authentication is that it is a redundant security system within each SQL Server. Included in each server configuration, and in each user, is whether a SQL Server login must adhere to the machine’s password policy. It is not required to be enforced.

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

The Enforce Password Policy check box is selected by default when you open the Login – New dialog box in SSMS (see Figure 12-2), but you can clear the check box to disable this option. So, with SSMS, it is possible to create a login with a noncomplex (or even blank) password.

When you create a login in Transact-SQL (T-SQL) code, the CHECK_POLICY option is not required, but it defaults to ON if not provided. According to Microsoft Docs, there is also no default for the -EnforcePasswordPolicy when using Add-SqlLogin in PowerShell.

• For more information, visit https://learn.microsoft.com/powershell/module/sqlserver/add-sqllogin.

In addition to enforcing password policy, you can enforce a maximum password age by selecting the Enforce Password Expiration check box. You also can force a user to change their password on their next login. Keep in mind, however, that although SSMS has built-in behavior to allow for this password to be changed with a simple dialog box, other applications might not allow users to change their passwords interactively. So, the UI programmer will need to provide this functionality, or the user will be stuck without the ability to access the database.

The bar has been raised by Azure SQL Managed Instance, which does not allow the password policy to be ignored.

Setting the login default database

Each login includes a default database option, which you should set based on how the login principal is to be used. It is convenient to be able to simply log in to a server and be in a desired database without specifying it at login, but it is not without a few concerns.

Authentication of the server principal will fail if its default database is not accessible, including if the database is restoring, is offline, or has been dropped from the instance. Authentication to the server will fail even if it is a member of the sysadmin server role, so you should rarely if ever change the default database of a known administrator login except to one of the system databases that must be there for the server to operate. The sysadmin role has all permissions to the SQL Server instance. We talk more about the sysadmin role and other server-level roles later in this chapter.

This guidance generally follows even for logins that are not a member of the sysadmin server role, unless they should be able to connect to a single database, and connection should be refused otherwise. In this way, the default database setting might be helpful because the login will be denied new connections if that single database is inaccessible, moved to another instance, or dropped from the instance. In each case, the user receives an error if the database is inaccessible.

Server level roles

A role in SQL Server is like a group in Windows terminology. It is a grouping to which other principals, referred to as members of the role, can be assigned. The role can then have permissions assigned to it, which every member of the role is granted by membership.

Roles are foundational to a solid security scheme and should be used to grant almost all rights on a SQL Server instance. When you grant rights to an individual login or user, over time, permissions can become so complicated, it becomes seemingly impossible to remove them. For example, if you have 10 DBAs on your team, and 100 users, you could end up with thousands of individual permissions to grant, with each user’s permissions ending up slightly different from everyone else’s over time.

Building a group of roles that match the tasks that your logins and users need to be able to accomplish, and then giving and taking them away as needed, allows for security defined in well-defined chunks of code that can be audited far more easily. If you need a DBA to be able to manage or back up databases, or view three tables in a database, you can create a role, give it a name, and test that it does only what you want it to.

You will work with two kinds of roles: built-in roles, and user-defined roles.

We start by reviewing server roles built into SQL Server, with a focus on when and why they should (and should not) be granted. Several server roles are included in SQL Server, including the one you are likely most familiar with: the all-powerful sysadmin role. We also cover how to create your own user-defined server roles if the ones provided by Microsoft aren’t granular enough for you (and they often are not).

Built-in server roles

Server roles bundle together one or more privileges that you want to give to a login. Built-in server roles are groups of broad permissions developed by Microsoft as general roles that people may need. The built-in server roles are generally used to grant administrative logins access to do certain tasks. Most of these are quite powerful in nature and must be given out cautiously, if at all.

In SQL Server 2022, Microsoft has added several new server roles with a naming standard to help them stand out (prefixed with ##MS and suffixed with ##). They were designed to be lighter roles than the previous set were, and should be considered for use before the classic roles where possible. We’ll review a few of these new server roles later in this section.

A common concern is that vendor specifications or developers request that inappropriate permissions be given to end users and service accounts via fixed server roles to run their applications. It is essential to understand what you are allowing an application to have access to. While it is not necessarily our assertation that any reputable third-party software is malicious, the bigger concern is whether they can withstand common attacks such as SQL injection.

Server roles are not a feature of Azure SQL Database, though database roles (covered later in this chapter) are provided. This is analogous to how a contained user behaves in SQL Server.

To manage the assignment of server roles to a user, SSMS provides the Membership page in the Login Properties dialog box (see Figure 12-3). By default, only the Public check box is selected, and cannot be cleared. So, new logins are assigned only the public built-in server role.

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 [domainkirby.sql]
GO
ALTER SERVER ROLE processadmin DROP MEMBER [domainkirby.sql]
GO

We recommend that whether you initially create your principals using the GUI or by script, you generate scripts for your logins and roles (other than passwords if you are using any SQL Server authenticated logins, though you may wish to include passwords for system accounts, provided that the script is stored in a secure location). This will help you to know what is supposed to be on any server. In many organizations, controlling security can be one of the more daunting tasks because getting permissions right in your environments is hard, and when production issues arise, sometimes maintaining scripts is the last thing you think of.

Security sprawl frequently happens when multiple administrators assign rights to users as they ask, but with scripted GRANTs and role assignments, you can add comments and perhaps reference documentation as to who authorized access in the script you have created. Maintaining idempotent (re-executable, not making changes unless changes are needed) scripts for adding server-/environment-specific security can help. Recording when that script was last executed in the script along with a check for principals that were modified after the last run is one strategy that can detect unscripted security changes.

Making matters worse, security can get complicated quickly because a role can be a member of a role, and that role the member of another role. The key here is to clearly understand what a role can do, and not just add system roles to user-defined ones without understanding what they do and why you are doing it.

Let’s explore the list of built-in server roles, beginning with the unlimited sysadmin and public roles, then the new roles for SQL Server 2022, followed by the legacy server roles. The new roles are prefixed and suffixed with ##.

• sysadmin. This server role has unrestricted access to all operations where there is no code to check for names (for example, row-level security may exclude sysadmin). It is appropriate for properly vetted DBA administrative accounts only. Although software vendors or other accounts can request membership to the sysadmin server role to simplify their security configuration, this is not appropriate. A responsible DBA should push back on granting membership to this role, especially if a user for a single database is requesting sysadmin role membership.

  When granting the sysadmin role, it is unnecessary to grant membership to any other server role (unless needed for row-level security). Granting membership to every server role is redundant because sysadmin doesn’t just have rights to do everything; the instance basically ignores security for the members of sysadmin.

  The sysadmin role is also granted certain other permissions, especially in the SSMS code. The privileges of the sysadmin role are nearly equivalent to the GRANT CONTROL SERVER permission, with some slight differences. The most notable of these is the fact that the sysadmin role is unaffected by any DENY permissions; for example:

  Click here to view code image

  USE master;
  GO
  --using standard security for simplicity
  CREATE LOGIN TestSysadminDeny WITH PASSWORD = '<strong password>'
  GO
  GRANT CONTROL SERVER TO TestSysadminDeny;
  DENY VIEW SERVER STATE TO  TestSysadminDeny;
  GO
  EXECUTE AS LOGIN = 'TestSysadminDeny';
  SELECT * FROM sys.dm_exec_cached_plans;
  GO
  REVERT;
  GO

  The result is an error:

  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.

  But if you execute the following code to add the user as a member of the sysadmin role, and then re-execute the statement, it will succeed:

  Click here to view code image

  ALTER SERVER ROLE sysadmin ADD MEMBER TestSysadminDeny;

• ##MS_DatabaseConnector##. Members of this server role can connect to any database without a user or explicit CONNECT rights assigned. It provides CONNECT ANY DATABASE rights at the server, which gives CONNECT rights in every database. The rights conferred by membership in this role can be overridden by an explicit DENY CONNECT in any database.

• ##MS_DatabaseManager##. Service accounts for applications that generate databases, such as an on-premises Microsoft SharePoint environment, need permissions to automatically create databases. These service accounts can be granted membership to this server role instead of sysadmin. Members can create new databases directly or by restoring from a backup.

  At the server level, the role has CREATE ANY DATABASE and ALTER ANY DATABASE permissions. At the database, it has the ALTER privilege. ALTER ANY DATABASE not only allows the login to alter databases, but to drop them as well.

  It does not provide rights to access the data inside databases it has created other than running ALTER commands. (If the user creates a database with itself as the owner, then the user has unlimited access to it.)

  In an Azure SQL Database logical server, use ##MS_DatabaseManager## instead of the dbmanager database level role that exists in the master.

  This is quite a powerful role. For example, consider the following script. It creates a new database owned by sa, then one by a new principal named TestDbManager. The only thing this login cannot do with the database is change the owner, but it can drop the database and make important setting changes.

  Click here to view code image

  USE master;
  GO
  --using standard security for simplicity
  CREATE LOGIN TestDbManager WITH PASSWORD = '<strong password>';
  GO
  ALTER SERVER ROLE ##MS_DatabaseManager## ADD MEMBER TestDbManager;
  GO

  Now, still logged in with the sysadmin-enabled login you are using to administer your test instance, create a database and make it owned by the built-in sa login:

  Click here to view code image

  CREATE DATABASE TestDropSa
  ALTER AUTHORIZATION ON DATABASE::TestDropSa TO sa;
  GO

  Next, impersonating the TestDbManager principal, attempt to create, alter, and drop databases:

  Click here to view code image

  EXECUTE AS LOGIN = 'TestDbManager';

  You should always make sure you are in the right context when writing test scripts. Too often you think it worked, but in fact you were in the wrong security context.

  Click here to view code image

  if SUSER_SNAME() <> 'TestDbManager'
            THROW 50000,'You are not in the expected context',1;

  Then test that you can do what you expect, as follows:

  Click here to view code image

  CREATE DATABASE TestDrop;
  ALTER AUTHORIZATION ON DATABASE::TestDrop TO sa;

  This command fails with the following error, indicating that it does not have permissions to change the owner. The login will be able to alter authorization to itself or any other account it can impersonate.

  Click here to view code image

  Msg 15151, Level 16, State 1, Line 17
  Cannot find the principal 'sa', because it does not exist or you do not have
  permission.

  You can, however, change very important settings:

  Click here to view code image

  ALTER DATABASE TestDropSa SET SINGLE_USER;
  ALTER DATABASE TestDropSa SET READ_COMMITTED_SNAPSHOT ON;

  And you can drop the databases:

  Click here to view code image

  DROP DATABASE TestDrop;
  DROP DATABASE TestDropSa;
  GO
  REVERT; --Go back to original, sysadmin role

  You must now verify your user context again to make sure you are back out of the previous context. If you change database context—for example, to tempdb—you cannot revert.

  Note that the ##MS_DatabaseManager## role does not confer any rights in the databases created. However, if a role member makes themselves the owner of the database, they will have access. You can determine this using the following query:

  Click here to view code image

  SELECT db.name as databaseName
  FROM sys.databases db
  JOIN sys.server_principals sp
  ON db.owner_sid = sp.sid
  WHERE sp.name= 'TestDbManager';

• ##MS_DefinitionReader##. This server role lets you view the code and security information for any object on the SQL Server in any database. It is the same as having the server rights VIEW ANY DATABASE, VIEW ANY DEFINITITION, and VIEW ANY SECURITY DEFINITION, which provides VIEW DEFINITION and VIEW SECURITY DEFINITION permissions in each database. It can be overridden by an explicit DENY of the server or database permissions.

• ##MS_LoginManager##. This allows members to create and delete logins by giving members CREATE LOGIN and ALTER ANY LOGIN rights. This new role is very much like the securityadmin role that has existed for a long time, but with one major difference: It does not come with the power to execute GRANT statements. Members of the securityadmin role can create a new login and grant it CONTROL SERVER, which is almost equivalent to sysadmin. (More on this later in this chapter.)

  As an example, the next set of statements shows the basics of what this role can do. It starts with a new login and then adds it to the ##MS_LoginManager## server role.

  Click here to view code image

  CREATE LOGIN TestLoginManager WITH PASSWORD = '<strong password>';
  GO
  ALTER SERVER ROLE ##MS_LoginManager## ADD MEMBER TestLoginManager;
  GO

  Change to the security context of the TestLoginManager login:

  Click here to view code image

  EXECUTE AS LOGIN = 'TestLoginManager';

  Now, create a new login. (Be careful to test your security context when doing these tests. It is easy to get lost and be confused by something working or not working because you are not actually in the security context you expect.)

  Click here to view code image

  CREATE LOGIN WhatCanIDo with PASSWORD = '<strong password>';

  The next question is, what else can you do to the login? You can add it to a role of which it is a member:

  Click here to view code image

  ALTER SERVER ROLE ##MS_LoginManager## ADD MEMBER WhatCanIDo;

  However, each of the next three statements will fail. The first two of these will question if the role exists:

  Click here to view code image

  ALTER SERVER ROLE ##MS_DatabaseConnector## ADD MEMBER WhatCanIDo;
  ALTER SERVER ROLE sysadmin ADD MEMBER WhatCanIDo;

  And the next statement will fail with the error “Grantor does not have GRANT permission”:

  Click here to view code image

  GRANT CONTROL SERVER TO WhatCanIDo;

  This is an important distinction between the ##MS_LoginManager## role and the securityadmin role (covered later in this section). It can only grant new rights for which it explicitly has the GRANT permission or add users to roles it is already a member of itself.

  If you want your new login to be able to grant rights, you must give it either the account CONTROL rights over a resource or use the WITH GRANT OPTION on your GRANT statements to allow the account to grant the same rights to other principals.

  Now, return to your sysadmin level account:

  REVERT;

  Add the login to the ##MS_DatabaseConnector##, and you will see that you can now add your WhatCanIDo account to it, too:

  Click here to view code image

  ALTER SERVER ROLE ##MS_DatabaseConnector##
            ADD MEMBER TestLoginManager;
  EXECUTE AS LOGIN = 'TestLoginManager';
  ALTER SERVER ROLE ##MS_DatabaseConnector##
            ADD MEMBER WhatCanIDo;
  REVERT;

• ##MS_SecurityDefinitionReader##. This gives the user the VIEW ANY SECURITY DEFINITION right at the server level plus the VIEW SECURITY DEFINITION right in any database to which the user has access. Providing an example of what this gives you access to is a bit trickier than with other permissions. For example, if you do not have access to security definitions, and if you execute SELECT * FROM sys.server_principals; you will get a list that includes sa, all the built in roles, and your login. But if you are a member of this role, you will see every login and role for the server.

  • For details on the security catalog views, visit https://learn.microsoft.com/sql/relational-databases/system-catalog-views/security-catalog-views-transact-sql.

• ##MS_ServerStateReader##. This role allows member logins access to view the state of the server in all the dynamic management objects (DMOs) and functions, and to view the database state. The role confers VIEW SERVER STATE, VIEW SERVER PERFORMANCE STATE, and VIEW SERVER SECURITY STATE at the server level, and VIEW DATABASE STATE, VIEW DATABASE PERFORMANCE STATE, and VIEW DATABASE SECURITY STATE at the database level.

• ##MS_ServerStateManager##. This is the same as the role in the previous bullet, with the addition of ALTER SERVER STATE permission. This allows access to some management operations, including certain DBCC commands like those that free cache (FREEPROCACHE and FREESYSTEMCACHE). It also allows the login to show performance details using DBCC SQLPERF, a well-documented command that can view and reset the wait and latch statistics via DMOs, as well as view space utilization data from transaction log files.

  Note

  In Azure SQL Database, resetting wait and latch statistics is not supported.

• Bulkadmin. This server role confers permissions to perform BULK INSERT operations from local files. It can be suitable for service accounts for unattended processes that perform automated mass data movement. Only 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, which also allows external sources via OPENROWSET.

  Principals with this permission can use BCP, SQL Server Integration Services (SSIS), or T-SQL to perform BULK INSERT statements. Note that for BULK INSERT operations, permissions to access the target database and INSERT into the destination tables are also required. ALTER TABLE permissions for the destination table might also be needed, depending on the exact settings used, as it can be set to ignore constraints, which is technically a change to the table.

• dbcreator. This role allows members to create and delete databases. It is analogous to the new ##MS_DatabaseManager## role.

• processadmin. This role grants admin-level visibility to sessions and requests, and allows users to view and reset server performance information. These permissions can prove useful to non-administrators who monitor activity.

  The role is granted ALTER ANY CONNECTION permissions, allowing members to view and stop (KILL) sessions. The role is also granted VIEW and ALTER SERVER STATE, allowing use of the DMOs.

  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.

• public. This role allows you to give access to any authenticated user of your server—which is to say that permissions should be granted to the public server role only in extremely rare occasions. This role should never be used as an easy-button solution to a security issue.

  Every login is a member of the public server role. So, you should not grant additional permissions to this role unless you have considered all the downfalls of doing so, because those permissions will be granted to all current and future logins and users. It is also generally not a good idea to deny access to the public role unless you want to ensure that only sysadmin users can perform some action.

  We don’t want to say “never” in either case, because there are use cases for every tool, but most of the time, using the public role is just taking the easy route, much like adding application logins to the sysadmin role.

• securityadmin. This role is 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. As with the new ##MS_SecurityAdmin## role, members cannot add principals to a role they are not in, but they can grant any server permissions, including CONTROL SERVER, which is analogous to sysadmin in most ways (other than it being subject to denies, where sysadmin is not).

  Membership in the securityadmin role is required for some service accounts to delegate the management of security to applications, especially those that create databases procedurally and thus must 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 and sp_configure settings, and execute the SHUTDOWN command to shut down the SQL Server instance. The role also grants VIEW and ALTER SERVER STATE permissions allowing for the viewing of a wide array of helpful DMOs.

  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. As noted, resetting wait and latch statistics is not supported on Azure SQL Database.

  The serveradmin role does not confer 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 capabilities.

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

  Note

  In addition to other permissions, diskadmin has been granted the ALTER RESOURCES permission, which is limited, poorly documented, and not recommended for granting on an individual basis. Instead, grant ALTER RESOURCES only to the diskadmin role.

• setupadmin. This role only grants permissions to deal with linked servers using T-SQL statements. To use SSMS to set up linked servers, the sysadmin role is required.

User-defined server roles

If the built-in server roles don’t do exactly what you need them to do—and they rarely will match 1:1 with your needs (other than sysadmin)—it is useful to create your own roles. First available in SQL Server 2012, you can create custom server roles to help you further define the roles that various administrators and non-administrators can be assigned. This can be especially helpful when crafting a package of less-than-sysadmin permissions for deployment managers, security administrators, auditors, developers, integration testers, or for external access.

Inside a DBA team, we might break down duties and grant permissions to suit, for example, junior and senior administrators, or specifically HA administrators, who do not need full sysadmin rights, but do need advanced rights that are not packaged together in any of the built-in roles. 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, so if one role has almost everything you need, you can add the custom role as a member of the built-in role and then grant additional rights to the custom role. Similarly, you can 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 can create it to allow read-only access to administrators to an instance. In the next section, “Logins for the DBA team,” we discuss separating the Windows credentials used by DBAs into an “everyday” account and an administrative account. This custom server role is also 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 DMOs, 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 [domainKirby];
ALTER SERVER ROLE SupportViewServer ADD MEMBER [domainColby];
ALTER SERVER ROLE SupportViewServer ADD MEMBER [domainDavid];

This doesn’t give them complete access to everything, but it does cover many of the things one needs to do day-to-day DBA work in a safe manner and to diagnose problems without accidentally making new problems.

Grant commonly used server privileges

Granting membership to the sysadmin role is appropriate only for administrator accounts that absolutely need complete control. It is inappropriate for developers, power users, and analysts. But what permissions they might need, short of “all of them,” is argued over more than you might expect.

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, give them access to specific activities they need. Avoid handing out superpowers except for DBAs and support personnel who truly need them.

These common securables are server-level and so are not supported (or necessary) in Azure SQL Database. They are not supported even when run in the master database of the Azure SQL Database logical server.

The following subsections present some examples of permissions that you can grant users to do certain tasks, or in one case, all tasks. Hence, it is not only important to know what you can do, but also to understand what privileges mean when you perform an audit of what privileges users have been given on an existing server.

It is not a simple task to determine exactly how a server principal obtained the privileges they have, but it is straightforward to determine what effective rights a user has at the server level with fn_my_permissions, which you can use at the server or database level to see what the current security context has access to. If you run this as a sysadmin, you will see every privilege listed.

Click here to view code image

USE master;
CREATE LOGIN ListEffectivePermissions WITH PASSWORD = '<strong password>';
GRANT CONNECT ANY DATABASE TO ListEffectivePermissions;

Next, you can check the login’s effective permissions by passing SERVER to the function (the first parameter is for the object to check permissions), which we will use at the database level:

Click here to view code image

EXECUTE AS LOGIN = 'ListEffectivePermissions';
SELECT permissions.permission_name
FROM    fn_my_permissions(NULL, 'SERVER') AS permissions
REVERT;

From this you will see the following:

Click here to view code image

permission_name
------------------------------------------------------------
CONNECT SQL
VIEW ANY DATABASE
CONNECT ANY DATABASE

One of these we granted, but there are two others that we did not grant. Every login has one privilege granted on creation: CONNECT SQL (which allows you to connect to the SQL Server). VIEW ANY DATABASE is inherited from the public group, which allows all server principals to see all databases in sys.databases, unless you explicitly DENY this privilege.

VIEW SERVER STATE

Click here to view code image

GRANT VIEW SERVER STATE TO [server_principal]

This permission at the server level allows the principal to view several server metadata objects, system views, and DMOs, many of which are essential to a developer who is looking to troubleshoot, analyze, or performance tune. Most of the DMOs need only the VIEW SERVER STATE permission.

This is a relatively safe permission to grant in terms of damage that can be done or data that can be seen. With VIEW SERVER STATE, the principal still has no access to data (other than some values that might show up in a query plan), database objects, logins, or passwords. This is a read-only permission at the server level and provides a lot of diagnostic information for someone doing support without the ability to affect major changes.

CONNECT ANY DATABASE

Click here to view code image

GRANT CONNECT ANY DATABASE TO [server_principal]

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, so while the login can execute USE DatabaseName;, it does not indicate that they can execute a SELECT statement and see data in the database. Although it does not create a user in each database for the login, it behaves as if a user had been granted login permission in each database but has been given no other rights (similar to what the CONNECT right confers to a user in a database).

This permission alone doesn’t seem very useful, but it is 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 as well as the next, SELECT ALL USER SECURABLES, to grant read-only access to a server, including each database on the server.

SELECT ALL USER SECURABLES

Click here to view code image

GRANT SELECT ALL USER SECURABLES TO [server_principal]

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, and table-valued functions. It does not give the user access to EXECUTE stored procedures. 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.

Production data could contain sensitive, personally identifiable, personal information, for instance, so that should not be accessible to even typical support people. 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 read access to all data on a server by denying this right. This could ensure that administrators (other than those in the sysadmin role) 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. Members of the sysadmin server role would not be affected by any DENY permission.

• For more information on the encryption of sensitive data, including Always Encrypted, see Chapter 13, “Protect data through classification, encryption, and auditing.”

CONTROL SERVER

Click here to view code image

GRANT CONTROL SERVER TO [server_principal]

This permission effectively grants all permissions on a server and all of its databases, and is not appropriate for developers or non-administrators.

While it is similar, granting the CONTROL permission is different from granting membership to the sysadmin server role, but it typically has a very similar effect. Members of the sysadmin role are not affected by DENY permissions, but owners of the CONTROL SERVER permission are.

IMPERSONATE

Click here to view code image

GRANT IMPERSONATE ON LOGIN::[server_principal] TO [server_principal]

The IMPERSONATE permission allows the server principal to use the EXECUTE AS statement, the EXECUTE AS clause on a coded object like a stored procedure, or the EXECUTE statement to execute T-SQL code in the security context of another server principal.

This permission can create a complicated administrative environment and should be granted only after you understand the implications and potential inappropriate or malicious use. With this permission, it is possible to configure a login 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.

Other than for developers and support persons doing testing, this permission is commonly granted for applications that use EXECUTE AS to change their connection security context. You can grant the IMPERSONATE permission on logins or users at the database scope.

Logins with the CONTROL SERVER permission already have IMPERSONATE ANY LOGIN permission (unless it has been denied), which should be limited only to administrators who have to occasionally verify what permissions a production user has. 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.

ALTER ANY EVENT SESSION

Click here to view code image

GRANT ALTER ANY EVENT SESSION TO [server_principal]

A developer might need this permission to trace the SQL Server as part of a troubleshooting expedition 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 SSMS. For that, they will need one further commonly granted developer permission: VIEW SERVER STATE.

Like traces in Profiler (the tool that won’t ever fade away), Extended Events sessions can capture events on the server from all databases and processes. You cannot use a trace to capture certain sensitive events—for example, the T-SQL statement CREATE LOGIN for a SQL authenticated login.

ALTER TRACE

Click here to view code image

GRANT ALTER TRACE TO [server_principal]

A developer might need this permission to trace the SQL Server as part of a troubleshooting expedition into the SQL Server instance (though you should remind them prior to granting this permission that traces are deprecated, and Extended Events are a much better diagnostic tool).

• For more information on monitoring SQL Server, see Chapter 8, “Maintain and monitor SQL Server.”

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

Special purpose logins

This section discusses some important special logins to be aware of, including special administrative access, which you should tightly control.

The sa login

The sa login is a special SQL Server–authenticated login that is, simply put, all powerful. It is a known member of the sysadmin server role with a unique SID value of 0x01, and you can (though rarely should) use it for all administrative access. If your instance is in Mixed mode (in which both Windows Authentication and SQL Server Authentication are turned on), and the sa password is known, it can be used to do anything on the server.

Even if you never use the sa account for authentication, it has utility as the authorization (in other words, the owner) of databases on a server for many configurations, such as a general corporate server where all the databases are used by the same enterprise. In cases where a server is used for multiple customers, you may wish to have each customer’s login own their own database(s). When two databases are owned by the same user, you can enable the DB_CHAINING database setting to allow cross database queries.

• For more information about cross-database chaining, see https://learn.microsoft.com/dotnet/framework/data/adonet/sql/enabling-cross-database-access-in-sql-server.

This known administrator account, however, has obvious potential consequences if used for typical access, much as any sysadmin level login does, with the addition of not being able to tell one user of the account from another. This means it could serve as an anonymous backdoor for malicious or noncompliant activity by current or former employees. In the best case, the sa account should have a wickedly complex password that is locked away for safekeeping.

Applications, application developers, and end users should never use the sa account. This much should be obvious. The sa account, like any SQL Server–authenticated account, can 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 and/or disable this account.

The BUILTINAdministrators Windows group

If you have experience administering SQL Server 2005 or older, you probably remember the BUILTINAdministrators group login, which was created by default to grant access to the sysadmin server role to any account that is also a member of the local Windows Administrator group. This was a convenience that might seem logical. But should anyone who has administrator rights to a server have administrator rights to everything that server has on it? The answer is no.

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

Chapter 4 discusses service accounts in greater depth, but it is worth pointing out that service accounts for a server are purely server logins. They are usually given the minimal permissions needed to run the services to which they are assigned by SQL Server Configuration Manager. For this reason, you should not use the Windows Services (services.msc) to change SQL Server feature service accounts.

It is not necessary to grant additional SQL Server permissions to SQL Server service accounts. (You might need to grant additional file-system-level permissions for file locations, however.) The SQL Server Agent service account likely needs to be a member of the sysadmin role in a typical configuration. It is technically not necessary, because you can set credentials that you assign to each job being executed using credentials and proxies.

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.

Logins for the DBA team

The first logins you need to create when setting up a new server are for the DBAs so they have access to do everything they need to do. In all but the most rudimentary IT departments, SQL DBAs need access to production SQL Server instances, but need their access governed and constrained to certain uses and privileges—at least most of the time. By this we mean that during your normal duties, it is better if you don’t have rights to drop the primary sales database because you “thought you were on your local machine.” A day of lost activity (or worse, sales), plus the need to creatively explain to potential employers why you left your previous employer abruptly, is not worth avoiding a few extra steps to make changes and apply upgrades to your production server.

Assuming you are primarily using Windows Authentication for your server access, this means that SQL Server DBAs will sign into a Windows instance using the domain credentials they use to access their email account, timesheet application, and so on. Then, they connect to the SQL Server instance with Windows Authentication on that same account and begin their work. DBAs often use this same method whether they are connecting to a production environment SQL Server or a preproduction (development/testing) environment SQL Server, though we’ll talk about using different credentials for each next.

To illustrate, 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 SSMS, 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?”

Too often, the problem is exceedingly simple: The DBA is working on the wrong server. In many cases, the development and production servers may look completely alike except for a few letters in the name or DNS entry. A DNS naming standard might just differ by sql.d.company and sql.p.company, and in a query window, it might not be noticed.

At a conference this author once attended, the speaker asked how many SQL Server instances people managed. Well over half of the attendees managed 50+ servers. If one-third of them are production, the other two-thirds look extremely similar to those servers, just in a preproduction configuration. For too many DBAs, it is sadly a rite of passage to muck up a production server, and then repair it with little or no data loss.

If your DBA team isn’t already using two or more Windows-authenticated accounts each, 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. Then consider creating “admin-level” accounts for each DBA that allow no access to preproduction systems, office applications, Office 365, virtual private networks (VPNs)—not even the Internet, except for what is required for the job. The idea is to encourage your DBA team to use its admin accounts only for administrative activities.

For example, suppose Kirby is a DBA. They use DomainKirby to access tools for “everyday” activities, such as email, instant messaging, preproduction SQL instances, source control, Office 365, VPN, and more. However, this domain account has limited access to production SQL Servers. For example, they can access server-level DMVs, activity levels, SQL Agent job history, and the SQL Server Error Log, but they cannot create logins, read or update live production data, alter databases, and so on. To perform any of those tasks, Kirby starts SSMS using Run AS another user or opens a remote desktop session to another server using Domainadmin-Kirby. This activity is deliberate and requires heightened awareness—the production databases are important! Starting SSMS from within the remote desktop session, Domainadmin-Kirby is a member of the sysadmin server role and can accomplish anything they need to in the production environment. When they’re done, they log out. Domainadmin-Kirby also has no VPN access; thus, if it is compromised, it cannot be used to both gain remote access to the corporate network and access SQL Servers.

Of course, since the DBA team are employees just like anyone else, they may have to access the SQL Server as part of their job—for example, to track projects and enter timesheets. However, access to the production servers using their admin account in a way that they have elevated access 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.

After separating the day-to-day from the admin logins for each member of the DBA team, it’s time to assign permissions to each as appropriate. If your DBA team has been operating with a single login, this will likely resolve revoking permissions from their existing account and creating a new admin account. Refer to the “User-defined server roles” section, earlier in this chapter, about 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.

Database users

There are four major types of database users: users mapped to logins and groups, users mapped to Windows Authentication principals directly, users who cannot authenticate at all, and contained users.

Users mapped to logins and groups

Users mapped to logins are by far the most common type you will encounter. For example, suppose you created a standard login named Bob and a Windows authenticated login named [DomainFred] using the following code:

Click here to view code image

CREATE LOGIN Bob WITH PASSWORD = 'Bob Is A Graat Guy';
--Misspellings in passwords can be helpful!
CREATE LOGIN [DomainFred] FROM WINDOWS;

Then, you could create users for these logins using:

Click here to view code image

CREATE USER Bob FOR LOGIN Bob;
CREATE USER [DomainFred] FOR LOGIN [DomainFred];

There is nothing stating that the name of the login must match the domain name, but it is a very typical way to create users, and helps to document your database users and where they come from. The following is perfectly legal syntax as well, where you name the user differently from the Windows security principal:

Click here to view code image

CREATE USER fred FOR LOGIN [DomainFred];

The Windows principal that the login and the database user it references needn’t be a Windows-based login. It can be a Windows group, for example, which will allow every member of that group to access the server without being named individually.

You cannot use a (backslash) character in a login or database username unless it is a Windows Authentication based login. Trying to execute the two following statements:

Click here to view code image

CREATE LOGIN [DomainFred] WITH PASSWORD = '$3cure1';
CREATE USER [DogGone] FOR LOGIN [DomainFred];

results in two error messages saying DomainFred and DogGone are not valid names because they contain invalid characters, and this is true even if DomainFred or DogGone is a valid login.

To use the same login name as the user for a Windows Authenticated login, the names must be the same. If you execute:

Click here to view code image

CREATE USER [DogGone] FOR LOGIN [DogGone];

Then you will either get an error about DogGone not being a valid Windows user or group, or you have a really cool domain name!

Users mapped to Windows Authentication principals directly

In the previous section, we created the login for [DomainFred], so the CREATE USER statement referenced that login. However, a user could be created for that login regardless of the existence of the explicit login principal:

Click here to view code image

CREATE USER [DomainSam] FOR LOGIN [DomainSam];

In a non-contained database, this user can be used by DomainSam, if and only if DomainSam can authenticate to the server. So, if Sam was a member of DomainDatabaseUsers, and there was a login mapped to DomainDatabaseUsers, creating the user DomainSam in the database would not only extend DomainSam access to the database, but it would give all members of DomainDatabaseUsers rights to access the server (and as we discussed earlier, rights to see the existence of the database, but not access it).

Users who cannot authenticate at all

A user does not have to have a login at all, even in a non-contained database. This means it cannot be authenticated to, but it can still exist and have rights assigned. In this scenario, the user will not have a SID assigned at all.

While the user principal cannot be authenticated to, it can be impersonated using EXECUTE AS, and is a very useful tool for testing security. It will also be used frequently in the “Understand permissions and authorization” section later in this chapter. The syntax for creating a login-less user is simply:

Click here to view code image

CREATE USER Sally WITHOUT LOGIN;
ALTER RoleYouWantToTest ADD MEMBER Sally;
EXECUTE AS USER = 'Sally';

You can then test the role all you want to without connecting to the server with a new login or creating an unneeded login. Database roles are covered in more detail later in this chapter.

Of course, in either case, if you create users and logins for testing, be sure to drop them when you are finished with them. You don’t want to leave any test code/data around in your databases when you are finished, even in your preproduction servers.

Users contained in the database

Databases created or altered on a SQL Server instance by using CONTAINMENT = PARTIAL allow the creation of database principals referred to as contained users. Contained users are authenticated directly to the database in which they are located. They are not used frequently, but are interesting in how they can show up in the system metadata.

Contained users can be contained users with password or contained Windows Authentication users. Contained users with password behave like SQL Server Authentication principals, and can be used directly, bypassing the server’s authentication.

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. Use the catalog 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 computing and storage resources. Users with this permission can grant access to 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, contained databases are specifically developed to assist with the concept of a cloud-based database as a service, to allow databases in Azure SQL Database to be mobile between different cloud hosts and to ensure very high levels of availability.

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

Database roles

Database roles, much like the server roles discussed in the “Server level roles” section earlier in this chapter, allow you to provide packages of permissions to ease the provisioning of database users. You also can create your own user-defined database roles to customize the packages of permissions granted to users.

This section reviews the database roles, both built-in and custom, with a focus on when and why they should be granted. The same list of roles applies to SQL Server and Azure SQL Database.

Built-in database roles

Let’s first examine the list of built-in database roles, their permissions, and their appropriate use. These roles have some utility when setting up your security, but in a well-configured database, most users will not be a member of any of these roles. Rather, they will be made a member of a custom role, covered in the next section.

• db_owner. This database role’s name is a bit misleading because it can have many members. It provides unrestricted access to the database to make any and all changes to that database and contained objects. This is different from being identified as the login that owns the database (represented by the owner_sid in sys.databases). 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 specifically confer the CONTROL DATABASE permission to its members, but is equivalent in terms of what it is allowed to do. Different from the sysadmin server role, the db_owner role does not bypass DENY permissions. For example:

  Click here to view code image

  CREATE USER fred WITHOUT LOGIN;
  ALTER ROLE db_owner ADD MEMBER fred;
  DENY SELECT ON dbo.test TO fred;
  GO
  EXECUTE AS USER = 'fred';
  SELECT *
  FROM dbo.test;

  For a real database, this would return an error message: “The SELECT permission was denied.” However, while you can deny members of the db_owner group access to some resource, the actual owner of the database will not be subject to the DENY. Moreover, as a member of the db_owner role, the user will be able to impersonate the dbo user unless you deny them that ability. (While impersonating dbo, the user can easily revoke any denied rights, so it is important to audit users with elevated rights if they have access to any sensitive data.)

  Click here to view code image

  EXECUTE AS USER = 'dbo'
  SELECT *
  FROM dbo.test;
  GO
  REVERT; REVERT; --Revert twice, once to get back to fred, and another to get back
  to your security context.

  The only users in the database who can add or remove members from built-in database roles are members of the db_owner role or principals that hold AUTHORIZATION rights for the database. However, a loophole to this is a database role such as:

  Click here to view code image

  CREATE ROLE ALLPowerful;
  ALTER ROLE db_owner ADD MEMBER allPowerful;

  Unlike the server-level sysadmin role, db_owner can be added to database roles. This is why the general prescription is to avoid adding the db_owner database role to custom roles unless it makes perfect sense for your purposes and you understand the implications.

  • For more on AUTHORIZATION, the equivalent of “ownership” terminology, see the section “Understand authorization” later in this chapter.

• db_accessadmin. This role has the right not only to create and manage database users and custom database roles, but also to create schemas and to grant permissions on all database objects. Among other permissions, 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 to which a user is assigned 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.

  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 instance. 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, especially for contained databases.

• db_backupoperator. This role has BACKUP DATABASE (including full and differential backups), BACKUP LOG, and CHECKPOINT permissions for the database. This role does not have rights to restore the database, however, because that requires server-level permissions found in the sysadmin and dbcreator fixed server roles, as well as the owner of the database. So, while this is generally safe in terms of harm that can be done to the server, it does give the user rights to back up the database and do with it what they want to.

  Note

  Remember: Security data in your databases is important, but possibly far more important is making sure someone can’t take a copy of your database home with them, either accidentally or on purpose.

• db_datareader. This role has rights to execute a SELECT statement using any object in the database, including tables, views, and table-valued functions. Other than utility-based usage, 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 schemas or specific objects instead of granting SELECT access to the entire database. (Note that there is a database level SELECT privilege that can be granted, which is equivalent to this role in terms of permissions.) There may come a time when you need to add a table, view, or function that you don’t want your user to have access to by default, and placing this in a schema that you have not granted rights to typical users is an easy way to accomplish this.

• db_datawriter. Members of this role can execute INSERT, UPDATE, and DELETE statements on any table or view in the database. Even more than db_datareader, this role can be dangerous to give to users for it applies to the entire database. You should instead grant write permissions on specific schemas or objects to use stored procedures to accomplish writes. In the same way there was a SELECT database privilege, there are also full database INSERT, UPDATE, and DELETE privileges, which are analogous to this role’s offering.

• db_ddladmin. This role has the rights to perform DDL statements to alter objects in the database. 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.

  Note

  There is no built-in database role that provides EXECUTE permissions, which you should grant more granularly than at the database level. There is an EXECUTE privilege at the database level, however, that will allow you to convey EXECUTE permission to every object at the database level.

• db_denydatareader. The inverse of db_datareader, this role denies SELECT on all objects. We discuss this later in this chapter in the “Understand ownership chaining” section, but this is not a complete “no user can read any data” tool because it does not stop certain kinds of access, such as access through a stored procedure.

• db_denydatawriter. Like db_denydatareader, this is the opposite of db_datawriter, in that the db_denydatawriter role denies INSERT, UPDATE, and DELETE statements on all objects.

• db_securityadmin. Members of this role can manage fixed database roles (but not change the membership of the db_owner role), and 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. Every database user is a member of the public database role. Under almost all circumstances, you should not grant additional permissions to the public roles in any database, because they will be granted to all current and future users.

Custom database roles

You can create custom database roles to define the roles that various application users or service accounts need for proper data access. Unlike server roles, custom database roles are not just for administrative purposes. Ideally, you assign collections of data access and database object permissions, assign these permissions only to roles, and then add users to roles. Custom database roles can own schemas and objects, just like database user principals.

As with server roles, the key to creating custom database roles is to have a solid understanding of the tasks you want the members of the role to be able to do and the permission set required to do them. 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 WebsiteExecute AUTHORIZATION dbo;
GO
-- Grant access rights to a specific schema in the database
GRANT EXECUTE ON Schema::Website TO WebsiteExecute;
GO

Like users, custom database roles can themselves be made members of other database roles. Be careful putting built-in database roles as members of custom roles, however, as you may end up giving users more rights than you intend. It is a very good practice to use proper naming of roles so you don’t have a role like:

Click here to view code image

CREATE ROLE ReadOneTable AUTHORIZATION dbo;

That ends up with the rights of the db_owner role because someone later runs the following:

Click here to view code image

ALTER ROLE db_owner ADD MEMBER ReadOneTable;

It’s even worse because while members of the db_securityadmin built-in role cannot change membership in db_owner, they can change membership in the ReadOneTable role, which then conveys db_owner level rights.

In an ideal configuration, you should create security groups for access roles based around job function, levels of oversight, and zones of control. So, if you have people who can report on data in the Warehouse schema, you could create a WarehouseReporting role and give it all the necessary rights in the database to do that task. DatabaseManagers might then have their own role. One method that you may find useful is to create domain groups to match the job function, for example, DomainWarehouseReporting, DomainWarehouseManagement, and DomainDatabaseManagers.

Your AD environment might already have different groups for different job functions, including SQL Server DBAs (for both their “everyday” and administrative accounts). These existing groups can be assigned to database roles, so all security is managed outside of database but still allows for the level of control desired. Security can certainly be difficult to configure properly, but a well-thought-out list of groups and database roles can help manage ongoing security in your SQL Server database and application.

Use role membership to handle environment differences

Security is one of the most complicated parts of the DBA’s job. Most code and objects in a database are of the sort where you strive to ensure that your preproduction servers (development, testing, QA, and so on) and production servers have the same structures and security for the same purposes. There is obviously a lifecycle involved, and the development environment will have changes in progress, QA will have changes you believe are ready to release, and production will have the least up-to-date code, because this is the environment for well-tested code. But over time, version by version, the code will be the same.

In security, no two environments will look even somewhat alike when it comes to who uses them. You will not want a salesclerk to have the same access on your development server as the production server where they are taking point-of-sale actions (which is likely done in another security context).

This is where roles come into play. If you only ever grant and deny privileges to roles, you will be able to put that security code into your source control system, test it in development and QA, and then apply it to production. For example, you might create the following role:

Click here to view code image

CREATE ROLE SalesSchemaRead;

And grant it rights:

Click here to view code image

GRANT SELECT ON SCHEMA::Sales TO SalesSchemaRead;

Because you will have tested that the role works, you will be certain that it works in every environment. In the development environment, you can test this schema with user [DomainTestUser], and it will have the same access as the [DomainRealUser] does in production if you make them a member of the SalesSchemaRead group. Then the only thing you need to manage outside of your code source control is which users are members of which roles in the different environments. Of course, it is a good idea to have your environmental scripts, like security, in source control as well.

Grant commonly used database level privileges

Much like at the server level, it is rare that we want to just give a user complete db_owner level access to a database. Typically, if you are being careful not to give users too much access to a database, you will find that none of the database roles match your desires well enough. This section discusses several permissions that are commonly useful to grant to users and programmers at the database level.

VIEW DEFINITION

Click here to view code image

GRANT VIEW DEFINITION ON schema.objectname TO [database_principal];

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—for example, to be able to compare the code and structures in the production environment to prepare a release, without being able to see what data is stored in the objects.

SHOWPLAN

Click here to view code image

GRANT SHOWPLAN TO [server_principal]

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 must also 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 several DMVs, such as sys.dm_exec_cached_plans without the SHOWPLAN permission, if they have the VIEW SERVER STATE permission.

IMPERSONATE

Click here to view code image

GRANT IMPERSONATE ON USER::[database_principal] TO [database_principal]

The IMPERSONATE permission allows the user of the EXECUTE AS statement and the EXECUTE AS clause on a coded object like a stored procedure to impersonate another user temporarily. This permission can create a complicated administrative environment and should be granted only after you understand the implications and potential inappropriate or malicious use. With this permission, it is even possible to impersonate a member of the sysadmin role and assume those permissions temporarily, so it should be granted only in controlled scenarios, and perhaps only on a temporary basis.

This permission is often 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 needs its service account to have permission to IMPERSONATE any login that currently exists 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.

CONTROL DATABASE

Click here to view code image

GRANT CONTROL ON DATABASE::[Database_Name] TO [database_principal]

This effectively grants all permissions on database and is not appropriate for most developers or pretty much any non-administrators.

Granting the CONTROL permission is different from granting membership to the db_owner database role, but it has a very similar effect. Members of the sysadmin role or logins mapped to the dbo database user are not affected by DENY permissions, but members of a database role (even db_owner) or users who have been granted CONTROL will still be affected by a DENY.

• For more information about monitoring SQL Server activity, see Chapter 8.

DDL

Sample DDL statements include things like CREATE TABLE, ALTER TABLE, UPDATE STATISTICS, CREATE PROCEDURE, CREATE OR ALTER PROCEDURE, and so on. Pretty much any statement that is used to modify the code or settings of the database applies.

The security needed to execute these statements include the following base categories:

• ALTER. Grants the ability to change the properties of a specific named object, or of all objects if used without referencing a specific object. It is also the permission required to execute the TRUNCATE statement on a table.

• ALTER ANY. Gives a user the right to change any database securable.

• CREATE <securable type>. Gives a user the right to create a given type of securable.

• VIEW DEFINITION. Grants the database principal the right to look at the code of any object in the database.

As you can tell from this list, you must be careful with these permissions. Most security concerns will not be programmers doing things incompetently or perhaps maliciously; rather, they are more likely to be edge cases that somehow get missed during testing—for example, an application generating code that drops a different object than expected, like dbo.Sales instead of temp.Sales, because the schema was accidentally left off.

DML

DML statements manipulate data in tables. The following statements access and modify data in tables and are commonly used: DELETE, INSERT, BULK INSERT, MERGE, SELECT, UPDATE, TRUNCATE TABLE, EXECUTE PROCEDURE.

The rights given to perform DML include the following:

• SELECT, INSERT, UPDATE, DELETE. These four permissions are the foundational ones. They give the user the right to either view, change, create, or delete data from a certain securable, or, if no securable is included in the call, the entire database.

• EXECUTE. Used to let a user execute a stored procedure or scalar function.

Three more DML statements are deprecated, but 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

Change database authorization

When ownership was redefined as authorization, the stored procedure sp_changedbowner was deprecated in favor of the ALTER AUTHORIZATION syntax. For example:

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 statement 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. The login, when they access the database, will have db_owner rights, because they are the owner of the database.

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.

Understand ownership chaining

Views, stored procedures, triggers, and functions abstract the permissions necessary to read and write from tables and other views. They do this using a concept called ownership chaining. Ownership chaining is a name that pre-dates the concept of authorization and has remained the common term used. Ownership chaining says that if the owner of a coded object is the same as the owner of all referenced objects, all a caller needs is access to the coded object. This section explores how coded objects can simplify the minimum permissions you must assign.

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

Users accessing the database with minimal rights would only need EXECUTE permissions on stored procedures and scalar functions, and SELECT permissions on views and table valued functions.

There are several important caveats that can break this ownership chaining abstraction and require that whoever is accessing data via a coded object also has permissions for 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 command such as sp_executesql or EXEC ('SQL statements') to access objects. This is one of the built-in safeguards against abuse like SQL injection attacks. Dynamic SQL doesn’t rule out chaining for other objects, but it does for any statement executed dynamically.

• The underlying database objects referenced by the coded object must have the same authorization. User A cannot confer rights to user B based on what it has access to via privileges, only rights that have been obtained by being the authorization principal on the objects.

• If the referenced objects are in different databases, the databases must have the same authorization, and cross database chaining must be turned on at the server level with EXEC sp_configure 'cross db ownership chaining'; or at the database level using ALTER DATABASE <databaseName> SET DB_CHAINING ON;.

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 a stored procedure successfully that accesses many objects, owned by the same principal that owns the procedure. Now, the database principal has no way to access the database objects outside of your stored procedure.

A demonstration of permissions with views, stored procedures, and functions

The following code demonstrates how to create a testing user and a testing table in the tempdb database. (You can use any database where you have rights to create objects.) Run this code while logged in as a member of the sysadmin role:

Click here to view code image

USE tempdb;
GO
CREATE USER TestOwnershipChaining WITHOUT LOGIN;
GO
CREATE SCHEMA Demo;
GO
CREATE TABLE Demo.Sample (
SampleId INT IDENTITY (1,1) NOT NULL CONSTRAINT PKOwnershipChain PRIMARY KEY,
Value NVARCHAR(10) );
GO
INSERT INTO Demo.Sample (Value) VALUES ('Value');
GO 2 --runs this batch 2 times so we get two rows

We’ve inserted two rows into the Demo.SampleTable. Now let’s test various ways to access this table, without granting any permissions to it.

Test permissions using a view

In this section, we will create a view on the Demo.Sample table and try to access it. Note that we just created the TestOwnershipChaining database principal and have not granted it any other permissions. Outside of what is granted to the public role, TestOwnershipChaining has no permissions. Execute this and all the following code in this section while logged in as a member of the db_owner database role (or with your administrator login that is a member of the sysadmin role):

Click here to view code image

CREATE VIEW Demo.SampleView
AS
        SELECT Value AS ValueFromView
        FROM Demo.Sample;
GO
GRANT SELECT ON Demo.SampleView TO TestOwnershipChaining;
GO

The TestOwnershipChaining principal now has access to the view Demo.SampleView, but not to the Demo.Sample table.

Now, attempt to read data from the table:

Click here to view code image

EXECUTE AS USER = 'TestOwnershipChaining';
SELECT * FROM Demo.Sample;
REVERT;

This results in the following error:

Click here to view code image

Msg 229, Level 14, State 5, Line 26
The SELECT permission was denied on the object 'Sample', database 'tempdb', schema
'Demo'.

Why? Remember that we have granted no permissions to the table Demo.Sample. This is as intended. However, the user TestOwnershipChaining can still access the data in Demo.Sample via the view:

Click here to view code image

EXECUTE AS USER = 'TestOwnershipChaining';
SELECT * FROM Demo.SampleView;
REVERT;

Here are the results:

ValueFromView
----------------------------
Value
Value

Note also that database principal TestOwnershipChaining has access only to the columns that the view Demo.SampleView provides (and rows if desired by using a WHERE clause). Applications can use view and stored procedure objects to provide appropriate SELECT, INSERT, UPDATE, and DELETE access to underlying table data by blocking access to specific rows and columns and not granting SELECT access (or other rights) directly to the table.

• For more information on techniques to allow appropriate data access, including Always Encrypted, see Chapter 13.

Test permissions using a stored procedure

Let’s demonstrate the same abstraction of permissions by using a stored procedure, and then also demonstrate a case when it fails. Start by creating the stored procedure object:

Click here to view code image

CREATE PROCEDURE Demo.SampleProcedure AS
BEGIN
SELECT Value AS ValueFromProcedure
FROM Demo.Sample;
END
GO
GRANT EXECUTE ON Demo.SampleProcedure to TestOwnershipChaining;

Now try to run as the TestOwnershipChaining principal:

Click here to view code image

EXECUTE AS USER = 'TestOwnershipChaining';
EXEC Demo.SampleProcedure;
REVERT;

The output from the procedure shows the rows of the table:

ValueFromProcedure
----------------------------
Value
Value

Just like the view object, this works without any access to the Demo.Sample table. The user TestOwnershipChaining was able to access the data in the table due to ownership chaining.

Now, let’s break a stored procedure’s ability to abstract the permissions using dynamic SQL:

Click here to view code image

CREATE OR ALTER PROCEDURE Demo.SampleProcedure_Dynamic AS
BEGIN
DECLARE @sql nvarchar(max)
SELECT @sql = 'SELECT Value as ValueFromProcedureDynamic FROM Demo.Sample;';
EXEC sp_executesql @sql;
END
GO
GRANT EXECUTE ON Demo.SampleProcedure_Dynamic to TestOwnershipChaining;

When you execute this version of the procedure:

Click here to view code image

EXECUTE AS USER = 'TestOwnershipChaining';
EXEC Demo.SampleProcedure_Dynamic;
REVERT;

Here are the results:

Click here to view code image

Msg 229, Level 14, State 5, Line 63
The SELECT permission was denied on the object 'Sample', database 'tempdb', schema
'Demo'.

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.

It is possible to get around this built-in safety feature by using the EXECUTE AS setting on the stored procedure. With EXECUTE AS, you can have the code in the procedure behave as a different principal. The same can be said of using the AS clause of the EXECUTE statement:

Click here to view code image

EXECUTE ('<Statements>') AS USER = '<UserName>';

The most common mistake that people make with EXECUTE AS is they default to the all-powerful dbo user, and then they write a procedure to let the user pass in any string to execute to a dynamic SQL statement. It seems fine; it uses stored procedures for access, and the application can do pretty much what it needs. Then the user finds it and realizes they can do anything. Or worse, the application doesn’t deal with SQL injection issues and a hacker exploits this risk to steal your data.

If you absolutely must use sp_executesql in a stored procedure, a far safer way is to create a user principal without a login, for which you can grant access to the specific resource in the specific manner that they need. So, if the stored procedure should be able to query the sales table for sales figures, you might grant that user SELECT rights on the sales object only. Note that this does require the user to be in all environments with the same rights, so it may require extra scripts for your general deployment processes, because you want this user to be checked into your source control with the code and not the environmental users. If you use a source control tool with decent filtering capabilities, you could check in only database users that have no related logins.

In this case, we create a database principal named ElevatedRights, grant it select rights to Demo.Sample, and then execute the code as that user:

Click here to view code image

CREATE USER ElevatedRights WITHOUT LOGIN;
GRANT SELECT ON OBJECT::Demo.Sample TO ElevatedRights;
GO
CREATE OR ALTER PROCEDURE Demo.SampleProcedure_Dynamic
WITH EXECUTE AS 'ElevatedRights'
AS
BEGIN
DECLARE @sql nvarchar(1000)
SELECT @sql = 'SELECT Value as ValueFromProcedureDynamic FROM Demo.Sample;';
EXEC sp_executesql @SQL;
END;
GO
GRANT EXECUTE ON OBJECT::Demo.SampleProcedure_Dynamic to TestOwnershipChaining;

Now, executing the procedure, access to the data is possible and data is returned.

Click here to view code image

EXECUTE AS USER = 'TestOwnershipChaining';
EXEC Demo.SampleProcedure_Dynamic;
REVERT;

The ElevatedRights user will not even be able to execute the procedure, just the SELECT statement. And because the user has no login, if you don’t grant any user IMPERSONATE permissions on it, it will be completely benign, security-wise.

Let us reiterate: Security is difficult to do well because there is so much to it. Not only that, getting it really right means asking, “What can my system users do?” Too often, though, we think, “Only lock down the valuables,” and put the key under the mat in the front of the house. It is going to take a great deal more work for you as an administrator to make sure security is done right, but keeping information away from the wrong eyes both inside and outside of your organization is completely worth all of it.

Access a table even when SELECT is denied

Let’s take these examples one final step further and DENY SELECT permissions to TestOwnership Chaining. 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 Demo.Sample TO TestOwnershipChaining;
GO
EXECUTE AS USER = 'TestOwnershipChaining';
SELECT *
FROM   Demo.Sample; --show that the user in fact cannot access the table
GO
SELECT * FROM Demo.SampleView --test the view
GO
EXEC Demo.SampleProcedure; --test the stored procedure
GO
EXEC Demo.SampleProcedure_Dynamic; --test the stored procedure
GO
REVERT;
GO

Executing this code, you will see four blocks of output: one error message stating SELECT permission was denied and three more with column headings ValueFromView, ValueFromProcedure, and ValueFromProcedureDynamic and two rows of output each. We should note that this is both a good thing and a bad thing at times. It is, however, very important to realize that only the dynamic stored procedure could be affected by a DENY, and only if you deny the user that is impersonated.

Problem scenario

Suppose a database exists on server1 but does not exist on server2.

Original state

server1

SQL Login = Kirby

SID = 0x5931F5B9C157464EA244B9D381DC5CCC

Database User = Kirby

SID = 0x5931F5B9C157464EA244B9D381DC5CCC

server2

SQL Login = Kirby

SID = 0x08BE0F16AFA7A24DA6473C99E1DAADDC

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

Orphaned SID

server1

SQL Login = Kirby

SID = 0x5931F5B9C157464EA244B9D381DC5CCC

Database User = Kirby

SID = 0x5931F5B9C157464EA244B9D381DC5CCC

server2

SQL Login = Kirby

SID = 0x08BE0F16AFA7A24DA6473C99E1DAADDC

Database User = Kirby

SID = 0x5931F5B9C157464EA244B9D381DC5CCC ← Orphaned SID

The resolution

The resolution for this problem scenario is to use the following command to alter the user and tell SQL Server the existing login to match with:

Click here to view code image

ALTER USER Kirby WITH LOGIN = Kirby;

This changes the SID of the user 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, so it could be user Bob matched to login Kirby with the same resolution.

Assuming you are using the same name for user and login, you can use the following script in the restored database to generate an ALTER USER script for your SQL Server login–based database principals to map them to server logins.

If you are accustomed to using sp_change_users_login to fix orphaned SIDs, that stored procedure has been deprecated, and replaced by the ALTER USER … WITH LOGIN statement:

Click here to view code image

DECLARE @IncludeUsersWithoutLogin bit = 0;
SELECT   'ALTER USER ' + QUOTENAME(dp.name COLLATE DATABASE_DEFAULT) +
            CASE WHEN sp.sid IS NOT NULL THEN
        ' WITH LOGIN = ' + QUOTENAME(dp.name) + '; '
            ELSE ' WITHOUT LOGIN;' END AS SQLText,
            *
FROM     sys.database_principals AS dp
         LEFT OUTER JOIN sys.server_principals AS sp
             ON dp.sid = sp.sid
WHERE    dp.is_fixed_role = 0
    AND dp.sid NOT IN ( 0x00 ) --guest
    AND (sp.name IS NOT NULL or @IncludeUsersWithoutLogin = 1)
    AND dp.type_desc = 'SQL_USER'
    AND dp.name <> 'dbo'
ORDER BY dp.name;

It is a good practice to run this type of script to handle orphaned SIDs every time you finish a restore that brings a database from one server to another.

Move logins using SQL Server Integration Services (SQL Server only)

Since SQL Server 2008 R2, SSIS has shipped with a Transfer Logins task that you can use to move logins from one server to another, including between different versions of SQL Server.

You use SQL Server Data Tools (SSDT) to create a new SSIS project. As Figure 12-4 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 the creation of both Windows-authenticated and SQL Server–authenticated logins on the target instance, 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 disabled. You must enable them again before you can use them.

This SSIS task does not move any of the role memberships or server permissions that these logins might have been granted on the source instance. Ideally these should be scripted and stored in source control to be able to quickly re-create a server (unlike standard accounts, where you need passwords).

• To read more about moving permissions and roles, see the sections “Move server role membership using T-SQL (SQL Server only)” and “Move server permissions using T-SQL (SQL Server only)” later in this chapter.

Move Windows-authenticated logins using T-SQL (SQL Server only)

This is the easiest of the steps, assuming the source and target SQL Server instance are in the same domain. Moving Windows-authenticated logins is as easy as scripting out the CREATE LOGIN statements for each login.

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

Here’s a sample script:

Click here to view code image

--Create windows logins
SELECT CONCAT('CREATE LOGIN ', QUOTENAME(name) +
      ' FROM WINDOWS WITH DEFAULT_DATABASE =' + QUOTENAME(default_database_name)+
      ', DEFAULT_LANGUAGE = '+ QUOTENAME(default_language_name))  + ';'AS CreateTSQL_Source
FROM sys.server_principals
WHERE type_desc in ('WINDOWS_LOGIN','WINDOWS_GROUP')
AND name NOT LIKE 'NT SERVICE\%'
AND is_disabled = 0
ORDER BY name, type_desc;

As in the previous section, 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.

Move SQL Server–authenticated logins using T-SQL (SQL Server only)

A time-honored reference for this task has been made available by Microsoft for years, yet was never implemented with the SQL Server product itself. Since 2000, DBAs have referenced Microsoft support article 918992, “Transfer logins and passwords between instances of SQL Server” (https://support.microsoft.com/help/918992/), which provides scripts to move standard logins and their hashed passwords to a different server (which can be a different version or edition of SQL Server). Doing this requires you to create a pair of stored procedures on your server as part of the process: sp_hexadecimal and sp_help_revlogin.

With the aid of these stored procedures, you can 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 in the strictest terms to reverse-engineer the SQL Server–authenticated login password, but given enough time, a hacker can find a password that will hash to that value—called a hash collision—that will more than likely be the original password (such is the nature of hash values). Since you have the hash values, the same password that the user knows will match the hash that you re-create on the new server.

Once again, these 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. The next two sections discuss moving server roles and server permissions.

Move server role membership using T-SQL (SQL Server only)

If you do not manage your server role memberships in a script, you ought to. And if you need to build that script or to move accounts to a new server, you can use the following script instead of working through the dialog boxes in SSMS for each role. This script retrieves server role membership via SQL Server catalog views and includes options to add logins to server roles.

Click here to view code image

--server level roles
SELECT DISTINCT
       CONCAT('ALTER SERVER ROLE ', QUOTENAME([r].[name]), ' ADD MEMBER ',
QUOTENAME([m].[name])) AS [createtsql]
FROM [sys].[server_role_members] AS [rm]
    INNER JOIN [sys].[server_principals] AS [r]
        ON [rm].[role_principal_id] = [r].[principal_id]
    INNER JOIN [sys].[server_principals] AS [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

Move server permissions using T-SQL (SQL Server only)

Moving server permissions can be extremely time-consuming if you choose to do it by identifying them on the Securables page of each SQL Server Login Properties dialog box. Instead, we advise you to script the permissions to re-create them on the destination server by using catalog views. Ideally, the output of this script should be placed in source control. Security should not be given out without some minimal review process to make sure that security matches what the agreed-upon security says it should be.

Here is a script that will output a script of your permissions to groups:

Click here to view code image

--SERVER LEVEL SECURITY
SELECT   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))
         + ';' AS CREATETSQL
FROM     sys.server_permissions AS RM
         INNER JOIN sys.server_principals AS U
             ON RM.grantee_principal_id = U.principal_id
         LEFT OUTER JOIN sys.endpoints AS E
             ON E.endpoint_id = RM.major_id
                 AND RM.class_desc = 'ENDPOINT'
WHERE  u.is_fixed_role = 0
--Note, public is not considered a fixed role because you
--can grant it permissions
--NOTE: this ignores many of the built in accounts,
--but if you have made changes to these
--accounts you may need to make changes to the WHERE clause
AND U.name NOT LIKE '##%' -- IGNORE SYSTEM ACCOUNTS
AND U.name NOT IN ( 'DBO', 'SA', ) -- IGNORE BUILT-IN ACCOUNTS
AND U.name NOT LIKE 'NT SERVICE%'
AND U.name NOT LIKE 'NT AUTHORITY%'
ORDER BY RM.permission_name, U.name;

Move Azure SQL Database logins

It is not possible to use sp_hexadecimal and sp_help_revlogin on an Azure SQL Database server for SQL Server–authenticated logins. Scripting an Azure SQL Database login from SSMS obfuscates any password information, just as it does on a SQL Server instance. 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 dependency of sp_help_revlogin.) Further, creating a login with a password hash is not supported in Azure SQL Database.

The solution for migrating Azure SQL Database password-based logins from one server to another is to have a script for your special logins, to re-create other logins on the destination server with a new secure password, and to have the users change the password when they use them.

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

Move other security objects

Do not forget to move other server-level objects to the destination server, as appropriate. These 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, 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. (Thankfully, in terms of security, you must re-create them manually.) You can script proxies in SQL Server Agent by using SSMS, and you should re-create them, including their assigned subsystems.

Alternative migration approaches

There is no easy way to accomplish this goal within the SSMS dialog boxes or to “generate scripts” of all SQL Server server–level security.

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

If your SQL Server resides on a VM, cloning 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, however, a VM-level clone is obviously not a solution.

As has been said, and can’t be repeated enough, the number-one way of preparing for a migration is to have idempotent (repeatable) scripts to re-create most of your security at the server level, rather than trying to re-create a server from the thousands of independent changes that were made over time. This, along with checks to make sure no new, unscripted security has been added without being added to the main script, will save you tons of time.

There is one more potential SQL Server–based method of server login information migration that is no less complex or troublesome. If you are moving from one SQL Server to another of the exact same version, backing up and restoring the master database from one SQL Server instance to another is a potential, albeit not particularly recommended, solution. (This obviously does not apply to Azure SQL Database.) Restoring a master database from one server to another involves myriad potential configuration changes to server-specific encryption keys, service accounts, user permissions, and server identification information, which might not be supported. The process is not outlined in any support documentation, and we do not recommend it.

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 and access to the same service accounts, in addition to the same SQL Server version and edition.

Allowing remote DAC connections

By default, DAC connections are only allowed locally. You can use the Surface Area Configuration dialog box in the Facets section of SSMS (refer to Chapter 1, “Get started with SQL Server tools”) to allow remote DAC connections via the RemoteDacEnabled setting. You also can use sp_configure to enable the Remote Admin Connections option.

We recommend that you do so because it is invaluable to gaining access to a SQL Server when Remote Desktop Protocol (RDP) or similar technologies are unable to connect to the Windows host of the SQL Server instance. Enabling 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 use a randomly assigned port that changes each time the service is started.

Remotely connecting to the DAC with SSMS is also possible by addressing the port number of the DAC instead of the ADMIN: syntax. For example, providing a connection string in SSMS to servernameinstancename,49902 connects to the DAC endpoint.