Conclusion

In this chapter, we discussed how to secure access to a SQL Server instance, its databases, and objects inside a database. It is important to understand the role of each security element in the security hierarchy in a SQL Server system. The first step is to enable SQL Server to accept network connections, without which there would be no access to the system.

Once the physical network is possible, you must enable users to connect to a SQL Server instance. You can perform this task by creating logins for Windows users and groups and SQL Server logins. Before doing so, decide what authentication method the system should use.

Getting access to a SQL Server instance only gives access to perform specific server-wide operations, for which you can apply specific permissions through the use of fixed server roles or through assigning specific permissions to the logins.

In order to get access to a specific database, it is necessary to create users on that database. These users are typically mapped to a specific login in SQL Server. Once a user exists in a database, you can apply specific permissions for database-wide operations.

You control a user's access to database objects in different ways, depending on the type of object. These permissions can be applied to individual users or to database roles, which can either be fixed or be created on demand to satisfy business needs.

It is better to assign permissions to programmable objects, instead of tables and columns, to provide for easier maintenance and encapsulation.

Controlling the execution context also provides a way of letting users perform operations that can't be controlled through permissions.

Chapter 2 Quick Reference

To	Do this
Enable remote connections	In the SQL Server Surface Area Configuration tool, click on the Surface Area Configuration For Services And Connections item, expand the Database Engine icon, click the Remote Connections item, select the Local And Remote Connections option, then select a protocol option.
Configure the authentication mode	In SQL Server Management Studio, right-click the SQL Server instance and choose Properties. Choose the Security icon in the Select A Page pane. In the Server Authentication section, select the authentication mode.
Grant SQL Server access to a Windows domain user	Execute the SQL statement CREATE LOGIN [<domain>\<user>] FROM WINDOWS;
Create a SQL Server login for a particular database	Execute the SQL statement CREATE LOGIN <user> WITH PASSWORD = '<password>', DEFAULT_DATABASE =<database>;
List SQL Server logins	Execute the SQL statement SELECT * FROM sys.sql_logins;
Turn on strict password policy	Execute the SQL statement CREATE LOGIN <user> WITH PASSWORD = '<password>' MUST_CHANGE, CHECK_EXPIRATION = ON, CHECK_POLICY = ON;
Grant individual permissions to a user	Execute the SQL statement GRANT ALTER <permission> TO <user>;
Remove a SQL Server user	Execute the SQL statement DROP LOGIN <user>;
Report all orphaned database users	Execute the SQL statement EXECUTE sp_change_users_login @Action='Report';
Enable the Guest user	Execute the SQL statement GRANT CONNECT TO Guest;