Implementing Security

You can implement a security solution with Management Studio or Transact-SQL statements, using system and extended stored procedures. Security stored procedures can also be used to manage security or implement some additional security features from a client application.

Selection of Authentication Mode

You select an authentication mode during installation of the server or in the Server Properties dialog box.

To open the Security page, right-click the server in the Object Browser, select Properties, and then select Security page.

Managing Logins

To allow a person to access SQL Server, you must first define his or her (server) login account. To create a login, expand the Security branch of Management Studio, right-click Logins, and select New Login from the pop-up menu. To manage an existing login, right-click the login in the List pane and select Properties. Management Studio opens the Login window to enable you to manage login properties. On the General page, you can select a name and type of login, password, default database, and language. You can switch to other pages of the window to specify membership in server roles, permissions on server securables, and access to databases.

To allow a Windows user access to the server, you have to create a login associated with the Windows account. You can use Transact-SQL to do this in several ways, but the basic option is

 Create Login [MyDomain\NikolaS] FROM WINDOWS; 

Alternatively, you can create a server login for a whole Windows group (domain or local) that the user belongs to:

 CREATE   LOGIN    [MyDomain\MyGroup]    FROM  WINDOWS; 

You can use Create Login to create a SQL Server login as well:

 Create Login nsunderic WITH PASSWORD = 'my,password326' 

In this case, a person will need to use a password when authenticating to SQL Server.

Note 

In previous versions of SQL Server, you had to use system stored procedures such as sp_addlogin to achieve the same goals. They are still supported to preserve compatibility.

The set of Login statements is orthogonal—there are Drop Login and Alter Login statements as well. You can get a list of server principals from the sys.server_principals catalog view.

 if exists(select * from sys.server_principals where name = 'nsunderic')    Drop Login nsunderic 

You can get more information about specified login using the sp_HelpLogin system stored procedure. It will return the list of matching logins and the list of associated database users.

The Database Access page of the Login window manages the databases the user can access and the user's membership in database roles.

You can also grant logins using stored procedures. You can use sp_grantlogin to create a login on SQL Server. To give a Windows user access to a SQL server, only the name of the user is required as a parameter:

 exec sp_grantlogin @login = 'Accounting\TomB' 

Granting Database Access

As you have seen, database access can be granted to a login during the login's creation. There is also a way to approach user creation from the database side and create a user by specifying the login or granting access to additional database objects after the user has been created. Database users can be managed from the server | Databases | database | Security | Users node of a database in the Object Browser. From the context-sensitive menu, you can choose to create a new user or edit an existing one. Management Studio will open the Database User window:

It is best to select login names using the helper button on the right side and to set a username to be identical to the name of the login. This is not required, but it simplifies user management. You can set the default schema and select which schemas are owned by the user. In the Database Role Membership section, you check all database roles to which you want to grant the user membership. You can perform the same operations from Transact-SQL.

To grant access to the database, use the Create User statement:

 USE    [Asset5] GO CREATE USER [nsunderic] FOR LOGIN [nsunderic] GO 

In this case, login nsunderic will become associated with the new nsunderic user in the current database. If you omit the For Login clause, SQL Server will attempt to associate the user with a login that has the same name.

You can review access using sp_HelpUsers. You can delete users using the Drop User statement and you can change them using the Alter User statement. The sys.database principals catalog view can be used to list database users (and other principals).

 if exists(select * from sys.database_principals where name = 'nsunderic')    DROP USER nsunderic 

Granting Role Membership

To assign a user to a user-defined database role, you issue a command such as:

 exec sp_addrolemember @rolename='rolUser', @membername='nsunderic' 

The @membername parameter can be a database user, another database role, a server login based on a Windows user, or a server login based on a Windows group.

You can review membership using sp_helprolemember and revoke it using sp_droprolemember.

You can create roles using sp_addrole:

 Create Role 'rolBcmManagement' 

You can remove roles using Drop Role or change their names using Alter Role. To view a list of roles, use sp_helpfixeddbroles and sp_helproles.

Granting Schema Ownership

To give a user ownership of a schema, use

 ALTER AUTHORIZATION ON SCHEMA::[util] TO [nsunderic] 

Assigning Permissions

The system of permissions controls user and role access to database objects and statements. Permissions can exist in one of following two states:

• Granted Means that a user has permission to use an object, feature, or statement.

• Denied Means that a user is not allowed to use a statement, feature, or object, even if the user has previously inherited permission (that is, the user is a member of a role that has permission granted).

Physically, a record is stored in either the sys.database_permissions or the sys .server_permissions catalog views for each user (or role) and object (or statement) for which permission has been granted or denied.

Because of their physical implementation, permissions are cumulative. For example, a user can receive permissions from one role and other permissions from some other role. Or, the user can be denied permissions that have been granted to all other members of a role.

You can control permissions from the Permissions page of the Database User window:

The bottom list contains permissions for the object specified in the top window. You can use the Add Objects and Revoke (remove from the list) buttons to manage the Objects list, and you can grant or deny individual permissions on specified objects using checkboxes.

Grant Statement

There are many forms of the Grant statement (and the Deny and Revoke statements), because there are many forms of securables. I will try to give you an idea how to use them and to cover the most important.

The following statement allows JohnS (SQL Server login) and TomB from the Accounting domain (Windows domain user) to create a database on the current server:

 Grant Create Database To JohnS, [Accounting\TomB] 

You can also combine several permissions in one statement:

 GRANT   ADMINISTER BULK OPERATIONS,      ALTER ANY LINKED SERVER,      CREATE ANY DATABASE,      VIEW ANY DATABASE TO JohnS 

The following statement grants rolUser members rights to execute the stored procedure, view its definition, or change it:

 GRANT EXECUTE, VIEW DEFINITION, ALTER ON dbo.ap_ChargeLog_Insert TO rolUser GO 

The following statement allows members of the rolUser role to view, add, delete, change, and reference (in a foreign key) records in the Inventory table:

 Grant Select, Insert, Update, Delete, REFERENCES On dbo.Inventory To rolUser 

Deny Statement

The Deny statement is used to negate permissions explicitly. Its syntax is basically the same as the syntax of the Grant statement (except that the keyword Deny is used).

The following statement prevents TomB of the Accounting domain from creating a database:

 Deny Create Database To [Accounting\TomB] 

The following statement prevents JohnS from deleting and changing records from the Inventory table, even though he has inherited rights to view, store, delete, and change records as a member of the AssetOwners role:

 Deny Update, Delete On dbo.Inventory To JohnS 

A Deny statement, even at the user level, will supersede all Grant permissions, whether at the user or role level.

Revoke Statement

The Revoke statement is used to deactivate statements that have granted or denied permissions. It has the same syntax as the Grant and Deny statements (except that the keyword Revoke is used).

It is easy to understand that permission can be removed using the Revoke statement. It is a little more challenging to understand how a permission can be granted by revoking it. To help you understand this concept, consider the following example in which a user, JohnS, is a member of the AssetOwner role, which has permission to insert, update, select, and delete records from the Inventory table:

 exec sp_addrolemember 'AssetOwner', 'JohnS', 

The administrator then decides to deny JohnS permission to delete and update records from Inventory:

 Deny Update, Delete On dbo.Inventory To JohnS 

After a while, the administrator issues the following statement:

 Revoke Update, Delete On dbo.Inventory To JohnS 

In effect, this command has granted Update and Delete permission on the Inventory table to JohnS.

Since the Revoke statement removes records from the sys.database_protects table in the current database, the effect of the Revoke statement is to return permissions to their original state. Naturally, this means that the user will not have access to the object (or statement). In that respect, its effect is similar to the Deny statement. However, there are two major differences between revoked and denied permissions: the Revoke statement does not prevent permissions from being granted in the future; and the Revoke statement doesn't supersede any other granted permissions provided by membership in other roles, whereas Deny does supersede those permissions.