Problem

Your SQL Server instance is configured for mixed mode authentication. Now you need to add a Windows principal as a login within that instance.

Solution

Use the CREATE LOGIN command to add a Windows group or login to the SQL Server instance. When using mixed authentication mode, you can create your own database logins and passwords within SQL Server.

The abridged syntax for creating a login from a Windows group or user login is as follows:

CREATE LOGIN login_name
FROM WINDOWS
[ WITH DEFAULT_DATABASE = database
| DEFAULT_LANGUAGE = language ]
| CERTIFICATE certname ASYMMETRIC KEY asym_key_name

Table 28-1 describes the arguments of this command.

In this recipe, I assume you already have certain Windows accounts and groups on the local machine or in your domain. This example creates a Windows login on the SQL Server instance, which is internally mapped to a Windows user.

USE master;
GO
CREATE LOGIN [ROISFrederic]
FROM WINDOWS
WITH DEFAULT_DATABASE = AdventureWorks2012,
DEFAULT_LANGUAGE = English;
GO

In the second example, a new Windows login is created, based on a Windows group. This is identical to the previous example, except that you are mapping to a Windows group instead of a Windows user.

USE master;
GO
CREATE LOGIN [ROISDuMonde]
FROM WINDOWS
WITH DEFAULT_DATABASE = AdventureWorks2012;
GO

CREATE LOGIN [ROISFrederic]

FROM WINDOWS

WITH DEFAULT_DATABASE = AdventureWorks2012, DEFAULT_LANGUAGE = English

CREATE LOGIN [ROISDuMonde]

FROM WINDOWS
WITH DEFAULT_DATABASE = AdventureWorks2012

Problem

You need to report on all Windows principals that have been added as logins in a SQL Server instance.

Solution

You can view Windows logins and groups by querying the sys.server_principals system catalog view. This example shows the name of each Windows login and group with access to SQL Server, along with the security identifier (sid). Each principal in the system catalog view has an sid, which helps uniquely identify it on the SQL Server instance.

USE master;
GO
SELECT name, sid
FROM sys.server_principals
WHERE type_desc IN ('WINDOWS_LOGIN', 'WINDOWS_GROUP')
ORDER BY type_desc;
GO

This returns the following results (your own results will vary):

How It Works

In this recipe, I demonstrated how to query Windows logins on the SQL Server instance using the sys.server_principals system catalog view. This view actually allows you to see other principal types too, which will be reviewed later in the chapter.

Problem

You have discovered that a Windows login in your SQL Server instance is configured for the wrong default database. You need to change the default database for this login.

Solution

Once a Windows login is added to SQL Server, it can be modified using the ALTER LOGIN command (this command has several more options that are applicable to SQL logins, as you’ll see reviewed later in the chapter). Using this command, you can perform tasks such as the following:

• Changing the default database of the login
• Changing the default language of the login
• Enabling or disabling a login from being used

The abridged syntax is as follows (arguments similar to CREATE LOGIN):

ALTER LOGIN login_name { ENABLE | DISABLE
WITH
DEFAULT_DATABASE = database DEFAULT_LANGUAGE = language }

In the first example, a Windows login (associated with a Windows user) is disabled from use in SQL Server. This prevents the login from accessing SQL Server and, if connected, ceases any further activity on the SQL Server instance.

USE master;
GO
ALTER LOGIN [ROISFrederic] DISABLE;
GO
This next example demonstrates enabling this account again.
USE master;
GO
ALTER LOGIN [ROISFrederic] ENABLE;
GO

In this example, the default database is changed for a Windows group.

USE master;
GO
ALTER LOGIN [ROISDuMonde]
 WITH DEFAULT_DATABASE = master;
GO

How It Works

In the first example, a Windows login was disabled using ALTER LOGIN and the login name.

ALTER LOGIN [ROISFrederic]

Following this was the DISABLE keyword, which removes this account’s access to the SQL Server instance (it removes the account’s access but still keeps the login in the SQL Server instance for the later option of reenabling access).

DISABLE

The second example demonstrated reenabling access to the login by using the ENABLE keyword.

The third example changed the default database for a Windows group. The syntax for referencing Windows logins and groups is the same—both principal types are designated within square brackets.

ALTER LOGIN [ROISDuMonde]

The second line then designated the new default database context for the Windows group.

WITH DEFAULT_DATABASE = master

Problem

An employee has changed departments and no longer needs access to a SQL Server instance. You need to remove the employee’s login.

Solution

In this recipe, I’ll demonstrate dropping a login from the SQL Server instance entirely by using the DROP LOGIN command. This removes the login’s permission to access the SQL Server instance. If the login is currently connected to the SQL Server instance when the login is dropped, any actions attempted by the connected login will no longer be allowed. The syntax is as follows:

DROP LOGIN login_name

The only parameter is the login name, which can be a Windows or SQL login (demonstrated later in the chapter), as this recipe demonstrates.

USE master;
GO
-- Windows Group login
DROP LOGIN [ROISDuMonde];
-- Windows user login
DROP LOGIN [ROISFrederic];
GO

How It Works

This recipe demonstrated the simple DROP LOGIN command, which removes a login from SQL Server. If a login owns any securables (see the next chapter for more information on securables), the DROP attempt will fail. For example, if the ROISFrederic login had been a database owner, an error like the following would have been raised:

Msg 15174, Level 16, State 1, Line 3
Login 'ROISFrederic' owns one or more database(s).
Change the owner of the database(s) before
dropping the login.

Problem

You need to temporarily prevent a group of users from connecting to a SQL Server instance.

Solution

Use the DENY CONNECT SQL command to deny a Windows user or group access to SQL server. Here’s an example:

USE master;
GO
DENY CONNECT SQL TO [ROISFrancois];
GO

To allow access again, you can use GRANT.

USE master;
GO
GRANT CONNECT SQL TO [ROISFrancois];
GO

How It Works

This section is a sneak preview of Chapter 29, where GRANT and DENY will be explained in more detail. In a nutshell, the GRANT command grants permissions to securables, and DENY denies permissions to them. Use DENY CONNECT to restrict the Windows user or group login from accessing a SQL Server instance the next time a login attempt is made. In both GRANT CONNECT and DENY CONNECT, it is assumed that the Windows user or group already has a login in SQL Server. Keep in mind that there are limitations to which logins you can deny permissions to. For example, if you try to DENY CONNECT to your own login with the following code:

DENY CONNECT SQL TO [ROISAdministrator]

you will see the following warning:

Cannot grant, deny, or revoke permissions to sa, dbo, information_schema, sys, or yourself.

Problem

You need to create a SQL login for a user that does not have a Windows login.

Solution

To create a new SQL Server login, use the CREATE LOGIN command.

CREATE LOGIN login_name
[WITH PASSWORD = ' password ' [ HASHED ] [ MUST_CHANGE ],
SID = sid],
DEFAULT_DATABASE = database,
DEFAULT_LANGUAGE = language,
CHECK_EXPIRATION = { ON | OFF},
CHECK_POLICY = { ON | OFF},
CREDENTIAL = credential_name ]

Table 28-2 describes the arguments of this command.

This example first demonstrates how to create a SQL Server login with a password and a default database designated.

USE master;
GO
CREATE LOGIN Pipo
WITH PASSWORD = 'BigTr3e',
DEFAULT_DATABASE = AdventureWorks2012;
GO

Assuming you are using Windows Server 2003 or newer, as well as mixed authentication, the recipe goes on to create a SQL login with a password that must be changed the first time the user logs in. This login also is created with the CHECK_POLICY option ON, requiring it to comply with Windows password policies.

USE master;
GO
CREATE LOGIN Marcus
WITH PASSWORD = 'ChangeMe' MUST_CHANGE
, CHECK_EXPIRATION = ON
, CHECK_POLICY = ON;
GO

How It Works

The first example in this recipe demonstrated creating a SQL login named Pipo. The login name was designated after CREATE LOGIN.

CREATE LOGIN Pipo

The second line designated the login’s password.

WITH PASSWORD = ' BigTr3e',

The last line of code designated the default database that the login’s context would first enter after logging into SQL Server.

DEFAULT_DATABASE = AdventureWorks2012

The second SQL login example demonstrated how to force a password to be changed on the first login by designating the MUST CHANGE token after the password.

CREATE LOGIN Marcus
WITH PASSWORD = 'ChangeMe' MUST_CHANGE ,

This password policy integration requires Windows Server 2003, as did the password expiration and password policy options also designated for this login.

CHECK_EXPIRATION = ON, CHECK_POLICY = ON

Problem

During an audit, a request has been submitted to you to provide a list of all SQL logins.

Solution

Again, you can view SQL Server logins (and other principals) by querying the sys.server_principals system catalog view.

USE master;
GO
SELECT name, sid
FROM sys.server_principals
WHERE type_desc IN ('SQL_LOGIN')
ORDER BY name;
GO

This returns the following results:

How It Works

This recipe’s query returned the name and sid of each SQL login on the SQL Server instance by querying the sys.server_principals catalog view.

Problem

You need to change the password for a SQL Server login.

Solution

Use the ALTER LOGIN command. Once a login is added to SQL Server, it can be modified using the ALTER LOGIN command. Using this command, you can perform several tasks:

• Change the login’s password
• Change the default database or language
• Change the name of the existing login without disrupting the login’s currently assigned permissions
• Change the password policy settings (enabling or disabling them)
• Map or remove mapping from a SQL login credential
• Enable or disable a login from being used
• Unlock a locked login

The syntax arguments are similar to CREATE LOGIN (I’ll demonstrate usage in this recipe).

ALTER LOGIN login_name { ENABLE | DISABLE
WITH PASSWORD = ' password '
[ OLD_PASSW0RD = ' oldpassword '
| [ MUST_CHANGE | UNLOCK ] ]
DEFAULT_DATABASE = database
DEFAULT_LANGUAGE = language
NAME = login_name
CHECK_POLICY = { ON | OFF }
CHECK_EXPIRATION = { ON | OFF }
CREDENTIAL = credentialjiame I NO CREDENTIAL }

In the first example of this recipe, a SQL login’s password is changed from BigTr3e to TwigSlayer.

USE master;
GO
ALTER LOGIN Pipo
WITH PASSWORD = 'TwigSlayer'
OLD_PASSWORD = 'BigTr3e';
GO

The OLD_PASSWORD option designates the current password that is being changed; however, sysadmin fixed server role members don’t have to know the old password in order to change it.

This second example demonstrates changing the default database of the Pipo SQL login.

USE master;
GO
ALTER LOGIN Pipo
WITH DEFAULT_DATABASE = [AdventureWorks2012];
GO

This third example in this recipe demonstrates changing both the name and password of a SQL login.

USE master;
GO
ALTER LOGIN Pipo
WITH NAME = Patmos, PASSWORD = 'AN!celIttul@isl3';
GO

Changing the login name instead of just dropping and creating a new one offers one major benefit: the permissions associated to the original login are not disrupted when the login is renamed. In this case, the Pipo login is renamed to Patmos, but the permissions remain the same.

How It Works

In the first example of this recipe, ALTER LOGIN was used to change a password designating the old password and the new password. If you have sysadmin fixed server role permissions, you only need to designate the new password. The second example demonstrated how to change the default database of a SQL login. The last example demonstrated how to change a login’s name from Pipo to Patmos, as well as change the login’s password.

Problem

You have multiple users that are unable to log in to SQL Server. You would like to check the password settings for these users.

Solution

Use the LOGINPROPERTY function to retrieve login policy settings.

SQL Server provides the LOGINPROPERTY function to return information about login and password policy settings and state. Using this function, you can determine the following qualities of a SQL login:

• Whether the login is locked or expired
• Whether the login has a password that must be changed
• Bad password counts and the last time an incorrect password was given
• Login lockout time
• The last time a password was set and the length of time the login has been tracked using password policies
• The password hash for use in migration (to another SQL instance, for example)

This function takes two parameters: the name of the SQL login and the property to be checked. In this example, I want to return properties for logins to determine whether the login may be locked out or expired.

USE master;
GO
SELECT p.name, ca.IsLocked, ca.IsExpired, ca.IsMustChange, ca.BadPasswordCount, ca.BadPasswordTime, ca.HistoryLength, ca.LockoutTime,ca.PasswordLastSetTime,ca.DaysUntilExpiration
    From sys.server_principals p
     CROSS APPLY (SELECT IsLocked = LOGINPROPERTY(p.name, 'IsLocked') ,
     IsExpired = LOGINPROPERTY(p.name, 'IsExpired') ,
     IsMustChange = LOGINPROPERTY(p.name, 'IsMustChange') ,
     BadPasswordCount = LOGINPROPERTY(p.name, 'BadPasswordCount') ,
     BadPasswordTime = LOGINPROPERTY(p.name, 'BadPasswordTime') ,
     HistoryLength = LOGINPROPERTY(p.name, 'HistoryLength') ,
     LockoutTime = LOGINPROPERTY(p.name, 'LockoutTime') ,
     PasswordLastSetTime = LOGINPROPERTY(p.name, 'PasswordLastSetTime') ,
     DaysUntilExpiration = LOGINPROPERTY(p.name, 'DaysUntilExpiration')
    ) ca
    WHERE p.type_desc = 'SQL_LOGIN'
     AND p.is_disabled = 0;
GO
In SQL 2012, the PasswordHashAlgorithm property has been added. This property returns the algorithm used to hash the password. In this next example, I want to demonstrate this new property for the LOGINPROPERTY function.
USE master;
GO
SELECT p.name,ca.DefaultDatabase,ca.DefaultLanguage,ca.PasswordHash
    ,PasswordHashAlgorithm = Case ca.PasswordHashAlgorithm
     WHEN 1
     THEN 'SQL7.0'
     WHEN 2
     THEN 'SHA-1'
     WHEN 3
     THEN 'SHA-2'
     ELSE 'login is not a valid SQL Server login'
     END
    FROM sys.server_principals p
    CROSS APPLY (SELECT PasswordHash = LOGINPROPERTY(p.name, 'PasswordHash') ,
     DefaultDatabase = LOGINPROPERTY(p.name, 'DefaultDatabase') ,
     DefaultLanguage = LOGINPROPERTY(p.name, 'DefaultLanguage') ,
     PasswordHashAlgorithm = LOGINPROPERTY(p.name, 'PasswordHashAlgorithm')
    ) ca
    WHERE p.type_desc = 'SQL_LOGIN'
     AND p.is_disabled = 0;
GO

This query returns the following:

How It Works

LOGINPROPERTY allows you to validate the properties of a SQL login. You can use it to manage password rotation, for example, checking the last time a password was set and then modifying any logins that haven’t changed within a certain period of time.

You can also use the password hash property in conjunction with CREATE LOGIN and the hashed_password HASHED argument to re-create a SQL login with the preserved password on a new SQL Server instance.

In each of the examples, I queried the sys.server_principals catalog view and then used a CROSS APPLY with a subquery that utilized the LOGINPROPERTY function.

    FROM sys.server_principals p
    CROSS APPLY (SELECT PasswordHash = LOGINPROPERTY(p.name, 'PasswordHash') ,
     DefaultDatabase = LOGINPROPERTY(p.name, 'DefaultDatabase') ,
     DefaultLanguage = LOGINPROPERTY(p.name, 'DefaultLanguage') ,
     PasswordHashAlgorithm = LOGINPROPERTY(p.name, 'PasswordHashAlgorithm')
    ) ca

This method was used so I could retrieve information about multiple SQL logins at once. Rather than pass each login name into the first parameter of the LOGINPROPERTY function, I referenced the outer catalog view, sys.server_principals. This allows me to retrieve the properties for multiple logins simultaneously.

To limit the query to just SQL Server logins, I added the following in the WHERE clause:

    WHERE p.type_desc = 'SQL_LOGIN'
     AND p.is_disabled = 0;

I aliased the CROSS APPLY subquery and used the aliases to reference the columns I needed to return in the SELECT clause.

SELECT p.name,ca.DefaultDatabase,ca.DefaultLanguage,ca.PasswordHash
     ,PasswordHashAlgorithm = Case ca.PasswordHashAlgorithm
     WHEN 1
     THEN 'SQL7.0'
     WHEN 2
     THEN 'SHA-1'
     WHEN 3
     THEN 'SHA-2'
     ELSE 'login is not a valid SQL Server login'
     END

Here, you will also see that I utilized a case statement. This was done to render the output more easily understood than the numeric assignments of those values.

Problem

After an audit, you discover that a login exists that should have been removed some time ago. You now need to remove that login.

Solution

Use the DROP LOGIN command to remove SQL logins.

This recipe demonstrates dropping a SQL login from a SQL Server instance by using the DROP LOGIN command.

The syntax is as follows:

DROP LOGIN login_name

The only parameter is the login name, which can be a Windows (as demonstrated in earlier in this chapter) or SQL login, as this recipe demonstrates:

USE master;
GO
DROP LOGIN Patmos;
GO

How It Works

This recipe demonstrated the simple DROP LOGIN command, which removes a login from SQL Server. The process is simple; however, if a login owns any securables (see the next chapter for information on securables), the DROP attempt will fail. For example, if the Patmos login had been a database owner, an error like the following would have been raised:

Msg 15174, Level 16, State 1, Line 3
Login 'Patmos' owns one or more database(s).
Change the owner of the database(s) before dropping the login.

Problem

You have a new user account that you need to create. Upon creation of this account, the user needs to be added to the diskadmin fixed server role.

Solution

To add a login to a fixed server role, use ALTER SERVER ROLE.

Fixed server roles are predefined SQL groups that have specific SQL Server–scoped (as opposed to database- or schema-scoped) permissions assigned to them. Prior to SQL Server 2012, you could not create new server roles; you could only add or remove membership to such a role from other SQL or Windows logins. With SQL Server 2012, you can now create a user-defined server role.

The sysadmin fixed server role is the role with the highest level of permissions in a SQL Server instance. Although server roles are permissions based, they have members (SQL or Windows logins/groups) and are categorized by Microsoft as principals.

The syntax to add a member to a fixed server role is as follows:

ALTER SERVER ROLE server_role_name
    ADD MEMBER server_principal

The first parameter (server_role_name) is the fixed server role to which you are adding the login. The second parameter (server_principal) is the login name to add to the fixed server role.

In this example, the login Titan is created and then added to the sysadmin fixed server role.

USE master;
GO
CREATE LOGIN Apollo WITH PASSWORD = 'De3pd@rkCave';
GO
ALTER SERVER ROLE diskadmin
    ADD MEMBER [Apollo];
GO

To remove a login from a fixed server role, use ALTER SERVER ROLE. The syntax is almost identical to adding a server_principal.

ALTER SERVER ROLE server_role_name
    DROP MEMBER server_principal

This example removes the Apollo login from the sysadmin fixed role membership.

USE master;
GO
ALTER SERVER ROLE diskadmin
    DROP MEMBER [Apollo];
GO

How It Works

Once a login is added to a fixed server role, that login receives the permissions associated with the fixed server role. ALTER SERVER ROLE was used to add a new login to a fixed role membership; ALTER SERVER ROLE was also used to remove a login from a fixed role membership.

Adding SQL or Windows logins to a fixed server role should never be done lightly. Fixed server roles contain far-reaching permissions, so as a rule of thumb, seek to grant only those permissions that are absolutely necessary for the job at hand. For example, don’t give sysadmin membership to someone who just needs SELECT permission on a table.

Problem

You need to report on all users who are members of the sysadmin fixed server role.

Solution

You can execute the system stored procedure sp_helpsrvrolemember or query the sys.server_role_members catalog view.

Fixed server roles define a grouping of SQL Server–scoped permissions (such as backing up a database or creating new logins). Like SQL or Windows logins, fixed server roles have a security identifier and can be viewed in the sys.server_principals system catalog view. Unlike SQL or Windows logins, fixed server roles can have members (SQL and Windows logins) defined within them that inherit the permissions of the fixed server role.

To view a list of fixed server roles, query the sys.server_principals system catalog view.

USE master;
GO
SELECT name
FROM sys.server_principals
WHERE type_desc = 'SERVER_ROLE';
GO

This query returns the following:

name
public
sysadmin
securityadmin
serveradmin
setupadmin
processadmin
diskadmin
dbcreator
bulkadmin

You can also view a list of fixed server roles by executing the sp_helpserverrole system stored procedure.

USE master;
GO
EXECUTE sp_helpsrvrole;
GO

This query returns the following:

Table 28-3 details the permissions granted to each fixed server role.

To see the members of a fixed server role, you can execute the sp_helpsrvrolemember system stored procedure.

EXECUTE sp_helpsrvrolemember 'sysadmin'

This returns the following results (your results will vary):

Alternatively, to see the members of a fixed server role, you can query the sys.server_role_members catalog view.

USE master;
GO
SELECT SUSER_NAME(SR.role_principal_id) AS ServerRole
     , SUSER_NAME(SR.member_principal_id) AS PrincipalName
     , SP.sid
    FROM sys.server_role_members SR
    INNER JOIN sys.server_principals SP
     ON SR.member_principal_id = SP.principal_id
    WHERE SUSER_NAME(SR.role_principal_id) = 'sysadmin';
GO

This returns the following results (your results will vary):

How It Works

You can query the system catalog view sys.server_principals in order to view fixed server roles, or you can use the sp_helpsrvrole system stored procedure to view descriptions for each of the roles. To view members of a role (other principals), use the sp_helpsrvrolemember system stored procedure or query the sys.server_role_members catalog view. The next recipe will show you how to add or remove other principals to a fixed server role.

Problem

A SQL login has been created, and now you want that login to have access to a database.

Solution

Once a login is created, it can then be mapped to a database user. A login can be mapped to multiple databases on a single SQL Server instance—but only one user for each database it has access to. Users are granted access with the CREATE USER command. The syntax is as follows:

CREATE USER user_name [ FOR
{ LOGIN login_name
| CERTIFICATE cert_name
I ASYMMETRIC KEY asym_key_name
} ] [ WITH DEFAULT_SCHEMA = schema_name ]

Table 28-4 describes the arguments of this command.

In this first example of the recipe, a new user called Apollo is created in the TestDB database.

USE master;
GO
IF NOT EXISTS (SELECT name FROM sys.databases
    WHERE name = 'TestDB')
BEGIN
    CREATE DATABASE TestDB
END
GO
USE TestDB;
GO
CREATE USER Apollo;
GO

In the second example, a Windows login is mapped to a database user called Doe with a default schema specified.

USE TestDB;
GO
CREATE USER Helen
FOR LOGIN [ROISHelen]
WITH DEFAULT_SCHEMA = HumanResources;
GO

How It Works

In the first example of the recipe, a user named Apollo was created in the TestDB database. If you don’t designate the FOR LOGIN clause of CREATE USER, it is assumed that the user maps to a login with the same name (in this case, a login named Apollo). Notice that the default schema was not designated, which means Apollo’s default schema will be dbo.

In the second example, a new user named Helen was created in the AdventureWorks2012 database, mapped to a Windows login named [ROISHelen] (notice the square brackets). The default schema was also set for the Helen login to HumanResources. For any unqualified object references in queries performed by Helen, SQL Server will first search for objects in the HumanResources schema.

Problem

You want to query to find more information about a database user.

Solution

You can report database user (and role) information for the current database connection by using the sp_helpuser system stored procedure. The syntax is as follows:

sp_helpuser [ [ @name_in_db= ] ' security_account ' ]

The single, optional parameter is the name of the database user for which you want to return information. Here’s an example:

USE TestDB;
GO
EXECUTE sp_helpuser 'Apollo';
GO

This returns the following results:

How It Works

The sp_helpuser system stored procedure returns the database users defined in the current database. From the results, you can determine important information such as the user name, login name, default database and schema, and user’s security identifier. If a specific user isn’t designated, sp_helpuser returns information on all users in the current database you are connected to.

Problem

You want to modify the default schema for a database user.

Solution

You should use the ALTER USER command. You can rename a database user or change the user’s default schema by using the ALTER USER command.

The syntax is as follows (argument usages are demonstrated in this recipe):

ALTER USER user_name
WITH NAME = new_user_name DEFAULT_SCHEMA = schema_name

In this first example of this recipe, the default schema of the Apollo database user is changed.

USE TestDB;
GO
ALTER USER Apollo
WITH DEFAULT_SCHEMA = Production;
GO

In the second example of this recipe, the default schema for a principal based on a Windows group is changed.

USE [master]
GO
CREATE LOGIN [ROISSQLTest] FROM WINDOWS
WITH DEFAULT_DATABASE = [TestDB];
GO
USE [TestDB]
GO
CREATE USER [ROISSQLTest]
FOR LOGIN [ROISSQLTest];
GO
ALTER USER [ROISSQLTest]
WITH DEFAULT_SCHEMA = Production;
GO

In the last example of this recipe, a database user name is changed.

USE TestDB;
GO
ALTER USER Apollo
WITH NAME = Phoebus;
GO

How It Works

The ALTER USER command allows you to perform one of two changes: renaming a database user or changing a database principal’s default schema. The first example changed the default schema of the Apollo login to the Production schema. The second example changed the default schema of the ROISSQLTest principal. In SQL Server 2012, you can now modify the default schema for principals mapped to a Windows group, certificate, or asymmetric key. The last example renamed the database user Apollo to Phoebus.

Problem

While maintaining a SQL Server instance, you have found a database user exists for a login that was removed the prior month. You want to now remove this database user.

Solution

Use the DROP USER command to remove a user from the database. The syntax is as follows:

DROP USER user_name

The user_name is the name of the database user, as this example demonstrates:

USE TestDB;
GO
DROP USER Phoebus;
GO

How It Works

The DROP USER command removes a user from the database but does not impact the Windows or SQL login that is associated to it. Like DROP LOGIN, you can’t drop a user that is the owner of database objects. For example, if the database user Phoebus is the schema owner for a schema called Test, you’ll get an error like the following:

Msg 15138, Level 16, State 1, Line 2
The database principal owns a schema in the database, and cannot be dropped.

Problem

You have restored a database to a different server. The database users in the restored database have lost their association to the server logins. You need to restore the association between login and database user.

Solution

When you migrate a database to a new server (by using BACKUP/RESTORE, for example), the relationship between logins and database users can break. A login has a security identifier, which uniquely identifies it on the SQL Server instance. This sid is stored for the login’s associated database user in each database that the login has access to. Creating another SQL login on a different SQL Server instance with the same name will not re-create the same sid unless you specifically designated it with the sid argument of the CREATE LOGIN statement.

For this recipe, we will create an orphaned user. This is done by first creating a login and a user. Then drop the login and re-create it, leaving the user untouched.

USE AdventureWorks2012;
GO
If not exists (select name from sys.server_principals
     where name = 'Apollo')
Begin
CREATE LOGIN Apollo
WITH PASSWORD = 'BigTr3e',
DEFAULT_DATABASE = AdventureWorks2012;
End
GO
If not exists (select name from sys.database_principals
     where name = 'Apollo')
Begin
CREATE USER Apollo;
END
DROP LOGIN [APOLLO];
CREATE LOGIN Apollo
WITH PASSWORD = 'BigTr3e',
DEFAULT_DATABASE = AdventureWorks2012;
GO

The following query demonstrates the link between Login and User by joining the sys.database_principals system catalog view to the sys.server_principals catalog view on the sid column in order to look for orphaned database users in the database.

USE AdventureWorks2012;
GO
SELECT dp.name AS OrphanUser, dp.sid AS OrphanSid
FROM sys.database_principals dp
LEFT OUTER JOIN sys.server_principals sp
    ON dp.sid = sp.sid
WHERE sp.sid IS NULL
    AND dp.type_desc = 'SQL_USER'
    AND dp.principal_id > 4;
GO

This query returns the following (your results will vary):

If you RESTORE a database from a different SQL Server instance onto a new SQL Server instance—and the database users don’t have associated logins on the new SQL Server instance—the database users can become “orphaned.” If there are logins with the same name on the new SQL Server instance that match the name of the database users, the database users still may be orphaned in the database if the login sid doesn’t match the restored database user sid.

Beginning with SQL Server 2005 Service Pack 2, you can use the ALTER USER WITH LOGIN command to remap login/user associations. This applies to both SQL and Windows accounts, which is very useful if the underlying Windows user or group has been re-created in Active Directory and now has an identifier that no longer maps to the generated sid on the SQL Server instance.

The following query demonstrates remapping the orphaned database user Sonja to the associated server login:

USE TestDB;
GO
ALTER USER Apollo WITH LOGIN = Apollo;
GO

The next example demonstrates mapping a database user ([Phoebus]) to the login [ROISPhoebus] (assuming that the user became orphaned from the Windows account or the sid of the domain account was changed because of a drop/re-create outside of SQL Server):

USE TestDB;
GO
ALTER USER [Phoebus]
WITH LOGIN = [ROISPhoebus];
GO

This command also works with remapping a user to a new login—whether or not that user is orphaned.

How It Works

In this recipe, I demonstrated querying the sys.database_principals and sys.server_principals catalog views to view any database users with an sid that does not exist at the server scope (no associated login sid). I then demonstrated using ALTER USER to map the database user to a login with the same name (but different sid). I also demonstrated how to remap a Windows account in the event that it is orphaned using ALTER USER.

Problem

You need to provide a list of database roles and associated members per role.

Solution

To view role membership, you can use sp_helprolemember.

Fixed database roles are found in each database of a SQL Server instance and have database-scoped permissions assigned to them (such as SELECT permission on all tables or the ability to CREATE tables). Like fixed server roles, fixed database roles have members (database users) that inherit the permissions of the role.

A list of fixed database roles can be viewed by executing the sp_helpdbfixedrole system stored procedure.

USE TestDB;
GO
EXECUTE sp_helpdbfixedrole;
GO

This returns the following results:

To see the database members of a fixed database role (or any user-defined or application role), you can execute the sp_helprolemember system stored procedure.

USE TestDB;
GO
EXECUTE sp_helprolemember;
GO

This returns the following results (the member sid refers to the sid of the login mapped to the database user):

How It Works

Fixed database roles are found in each database on a SQL Server instance. A fixed database role groups important database permissions together. These permissions can’t be modified or removed. In this recipe, I used sp_helpdbfixedrole to list the available fixed database roles.

EXECUTE sp_helpdbfixedrole;

After that, the sp_helprolemember system stored procedure was used to list the members of each fixed database role (database users), showing the role name, database user name, and login sid.

EXECUTE sp_helprolemember;

As with fixed server roles, it’s best not to grant membership to them without assurance that all permissions are absolutely necessary for the database user. Do not, for example, grant a user db_owner membership when only SELECT permissions on a table are needed.

The next recipe shows you how to add or remove database users to a fixed database role.

Problem

You have been given a list of new users that need to be added to specific roles within the database.

Solution

To associate a database user or role with a database role (user-defined or application role), use the ALTER ROLE command. The syntax is as follows:

ALTER ROLE database_role_name
ADD MEMBER database_principal

The first parameter (database_role_name) is the role name, and the second parameter (database_principal) is the name of the database user.

To remove the association between a database user and role, you will also use the ALTER ROLE command.

ALTER ROLE database_role_name
DROP MEMBER database_principal

The syntax for removing a database user is similar to adding a user to a role. To remove a user, you need to use the keyword DROP in lieu of ADD.

This first example demonstrates adding the database user Helen to the fixed db_datawriter and db_datareader roles.

USE TestDB
GO
If not exists (select name from sys.database_principals
     where name = 'Apollo')
Begin
CREATE LOGIN Apollo
WITH PASSWORD = 'BigTr3e',
DEFAULT_DATABASE = TestDB;
CREATE USER Apollo;
END
GO
ALTER ROLE db_datawriter
    ADD MEMBER [APOLLO];
ALTER ROLE db_datareader
    ADD MEMBER [APOLLO];
GO

This second example demonstrates how to remove the database user Apollo from the db_datawriter role.

USE TestDB;
GO
ALTER ROLE db_datawriter
    DROP MEMBER [APOLLO];
GO

How It Works

This recipe began by discussing ALTER ROLE, which allows you to add a database user to an existing database role. The database user Apollo was added to db_datawriter and db_datareader, which gives the user cumulative permissions to SELECT, INSERT, UPDATE, or DELETE from any table or view in the AdventureWorks2012 database.

ALTER ROLE db_datawriter
    ADD MEMBER [APOLLO];
ALTER ROLE db_datareader
    ADD MEMBER [APOLLO];
GO

The first parameter (database_role_name) was the database role, and the second parameter (database_principal) was the name of the database user (or role) to which the database role is associated.

After that, ALTER ROLE was used to remove Apollo’s membership from the db_datawriter role.

ALTER ROLE db_datawriter
    DROP MEMBER [APOLLO];
GO

Problem

You have several users that require the same permissions within a database. You want to reduce the administration overhead with managing the permissions for this group of users.

Solution

Create a user-defined database role. User-defined database roles allow you to manage permissions to securables more easily than if you had to individually grant the same permissions to multiple database users over and over again. Instead, you can create a database role, grant it permissions to securables, and then add one or more database users as members to that database role. When permission changes are needed, you have to modify the permissions of only the single database role, and the members of the role will then automatically inherit those permission changes.

Use the CREATE ROLE command to create a user-defined role in a database.

The syntax is as follows:

CREATE ROLE role_name [ AUTHORIZATION owner_name ]

The command takes the name of the new role and an optional role owner name. The owner name is the name of the user or database role that owns the new database role (and thus can manage it).

You can list all database roles (fixed, user-defined, and application) by executing the sp_helprole system stored procedure.

USE TestDB;
GO
EXECUTE sp_helprole;
GO

This returns the following abridged results (the IsAppRole column shows as a 1 if the role is an application role and 0 if not):

RoleName RoleId	IsAppRole
public   0      0
db_owner 16384  0
. . .    . . .  . . . 

Once a database role is created in a database, you can grant or deny it permissions as you would a regular database user (see the next chapter for more on permissions). I will demonstrate granting permissions to a database role in a moment.

If you want to change the name of the database role, without also disrupting the role’s current permissions and membership, you can use the ALTER ROLE command, which has the following syntax:

ALTER ROLE role_name WITH NAME = new_name

The command takes the name of the original role as the first argument and the new role name in the second argument.

To drop a role, use the DROP ROLE command. The syntax is as follows:

DROP ROLE role_name

If a role owns any securables, you’ll need to transfer ownership to a new owner before you can drop the role.

In this example, I’ll create a new role in the AdventureWorks2012 database.

USE AdventureWorks2012;
GO
CREATE ROLE HR_ReportSpecialist AUTHORIZATION db_owner;
GO

After being created, this new role doesn’t have any database permissions yet. In this next query, I’ll grant the HR_ReportSpecialist database role permission to SELECT from the HumanResources.Employee table:

Use AdventureWorks2012;
GO
GRANT SELECT ON HumanResources.Employee TO HR_ReportSpecialist;
GO

To add Apollo as a member of this new role, I execute the following:

Use AdventureWorks2012;
GO
If not exists (select name from sys.server_principals
     where name = 'Apollo')
Begin
CREATE LOGIN Apollo
WITH PASSWORD = 'BigTr3e',
DEFAULT_DATABASE = AdventureWorks2012;
End
GO
If not exists (select name from sys.database_principals
     where name = 'Apollo')
Begin
CREATE USER Apollo;
END
GO
EXECUTE sp_addrolemember 'HR_ReportSpecialist', 'Apollo';
GO
If later I decide that the name of the role doesn’t match its purpose, I can change its name using ALTER ROLE.
Use AdventureWorks2012;
GO
ALTER ROLE HR_ReportSpecialist WITH NAME = HumanResources_RS;
GO

Even though the role name was changed, Apollo remains a member of the role. This last example demonstrates dropping a database role.

Use AdventureWorks2012;
GO
DROP ROLE HumanResources_RS;
GO

This returns an error message, because the role must be emptied of members before it can be dropped.

Msg 15144, Level 16, State 1, Line 1
The role has members. It must be empty before it can be dropped.

So, the single member of this role is then dropped, prior to dropping the role.

Use AdventureWorks2012;
GO
EXECUTE sp_droprolemember 'HumanResources_RS', 'Apollo';
GO
DROP ROLE HumanResources_RS;
GO

How It Works

The CREATE ROLE command creates a new database role in a database. Once created, you can apply permissions to the role as you would a regular database user. Roles allow you to administer permissions at a group level—allowing individual role members to inherit permissions in a consistent manner instead of applying permissions to individual users, which may or may not be identical.

This recipe demonstrated several commands related to managing user-defined database roles. The sp_helprole system stored procedure was used to list all database roles in the current database. CREATE ROLE was used to create a new user-defined role owned by the db_owner fixed database role.

CREATE ROLE HR_ReportSpecialist AUTHORIZATION db_owner

I then granted permissions to the new role to SELECT from a table.

GRANT SELECT ON HumanResources.Employee TO HR_ReportSpecialist

The Apollo user was then added as a member of the new role.

EXECUTE sp_addrolemember 'HR_ReportSpecialist', 'Apollo'

The name of the role was changed using ALTER ROLE (still leaving membership and permissions intact).

ALTER ROLE HR_ReportSpecialist WITH NAME = HumanResources_RS

The Apollo user was then dropped from the role (so that I could drop the user-defined role).

EXECUTE sp_droprolemember 'HumanResources_RS', 'Apollo'

Once emptied of members, the user-defined database role was then dropped.

DROP ROLE HumanResources_RS

Problem

You have an application that requires limited permissions in a database. Any user using this application should use the permissions of the application over their individual permissions. You need to create a database principal for this application.

Solution

You should create an application role. An application role is a hybrid between a login and a database role. You can assign permissions to application roles in the same way that you can assign permissions to user-defined roles. Application roles differ from database and server roles, however, in that application roles do not allow members. Instead, an application role is activated using a password-enabled system stored procedure. When you use an application role, it overrides all of the other permissions your login would otherwise have.

Because an application role has no members, it requires a password for the permissions to be enabled. In addition to this, once a session’s context is set to use an application role, any existing user or login permissions are nullified. Only the application role’s permissions apply.

To create an application role, use CREATE APPLICATION ROLE, which has the following syntax:

CREATE APPLICATION ROLE application_role_name
WITH PASSWORD = ' password ' [ , DEFAULT_SCHEMA = schema_name ]

Table 28-5 describes the arguments of this command.

In this example, a new application role name, DataWareHouseApp, is created and granted permissions to a view in the AdventureWorks2012 database.

USE AdventureWorks2012;
GO
CREATE APPLICATION ROLE DataWareHouseApp
WITH PASSWORD = 'mywarehousel23!', DEFAULT_SCHEMA = dbo;
GO

An application role by itself is useless without first granting it permissions to do something. So, in this example, the application role is given SELECT permissions on a specific database view.

-- Now grant this application role permissions
USE AdventureWorks2012;
GO
GRANT SELECT ON Sales.vSalesPersonSalesByFiscalYears
TO DataWareHouseApp;
GO

The system stored procedure sp_setapprole is used to enable the permissions of the application role for the current user session. In this next example, I activate an application role and query two tables.

USE AdventureWorks2012;
GO
EXECUTE sp_setapprole 'DataWareHouseApp', -- App role name
    'mywarehousel23!' -- Password
 ;
GO
-- This query Works
SELECT COUNT(*)
 FROM Sales.vSalesPersonSalesByFiscalYears;
-- This query Doesn't work
SELECT COUNT(*) FROM HumanResources.vJobCandidate;
GO

This query returns the following:

-----------
14

(1 row(s) affected)

Msg 229, Level 14, State 5, Line 7
SELECT permission denied on object 'vJobCandidate',
database 'AdventureWorks2012', schema
'HumanResources'.

Even though the original connection login was for a login with sysadmin permissions, using sp_setapprole to enter the application permissions means that only that role’s permissions apply. So, in this case, the application role had SELECT permission for the Sales.VSalesPersonSalesByFiscalYears view, but not the HumanResources.vJobCandidate view queried in the example.

To revert to the original login’s permissions, you must close out the connection and open a new connection.

You can modify the name, password, or default database of an application role using the ALTER APPLICATION ROLE command.

The syntax is as follows:

ALTER APPLICATION ROLE application_role_name WITH NAME = new_application_role_name
PASSWORD = ' password '
DEFAULT_SCHEMA = schema_name

Table 28-6 shows the arguments of the command.

In this example, the application role name and password are changed.

USE AdventureWorks2012;
GO
ALTER APPLICATION ROLE DataWareHouseApp
WITH NAME = DW_App, PASSWORD = 'newsecret!123';
GO

To remove an application role from the database, use DROP APPLICATION ROLE, which has the following syntax:

DROP APPLICATION ROLE rolename

This command takes only one argument, the name of the application role to be dropped. Here’s an example:

USE AdventureWorks2012;
GO
DROP APPLICATION ROLE DW_App;
GO

How It Works

This recipe demonstrated how to do the following:

• Create a new application role using CREATE APPLICATION ROLE
• Activate the role permissions using sp_setapprole
• Modify an application role using ALTER APPLICATION ROLE
• Remove an application role from a database using DROP APPLICATION ROLE

Application roles are a convenient solution for application developers who want to grant users access only through an application. Savvy end users may figure out that their SQL login can also be used to connect to SQL Server with other applications such as Microsoft Access or SQL Server Management Studio. To prevent this, you can change the login account to have minimal permissions for the databases and then use an application role for the required permissions. This way, the user can access the data only through the application, which is then programmed to use the application role.