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


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


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


Solid Quality Learning - Microsoft SQL Server 2005. Applied Techniques Step by Step
Microsoft SQL Server 2005: Applied Techniques Step by Step (Pro-Step by Step Developer)
ISBN: 0735623163
EAN: 2147483647
Year: 2006
Pages: 115

Similar book on Amazon © 2008-2017.
If you may any questions please contact us: