We'll discuss some of the techniques for providing a solid security foundation for the data tier of an application that resides on Microsoft SQL Server 7 and 2000. While this is a broad topic, we'll focus on basic security concepts as well as column- and row-level security.
Basic SQL Server security concepts The security implementations in this article will rely on the basic internal security system provided by SQL Server. Before a user can be granted any level of permissions, a user account must be created on the SQL Server. The user account can be either a domain or local account (using sp_grantlogin), or if the server is operating in the Mixed Security Mode, it can be a built-in SQL Server login (using sp_addlogin). Once a login has been created on the server, the login must then be added as a valid user for any of the databases the user intends to access (using sp_grantdbaccess).
Now that the user has the ability to connect to the server (a login was created) and use one or more databases on the server (a user account was created for each database), the next step is to specify the level of access the user has inside each database. This is done with the GRANT, DENY, and REVOKE SQL DDL statements. The SQL GRANT statement is fairly well understood, so we won't dive into it here, but the DENY and REVOKE statements are less well known, especially for new database developers, so we'll focus on them.
The DENY statement The DENY statement is the opposite of the GRANT statement. Granting a database user access to a table (or object) specifies that the user has the specific right to access the table. Denying a database user access to a table (or object) specifies that under no circumstances should the user have access to the object. Therefore, if a user is denied access to an object either through his or her own user account or through any role (previously known in SQL 6.5 as groups) he or she belongs to, then the user will not be given access to the object.
The REVOKE statement The REVOKE statement simply indicates that a previously assigned security setting should be undone. For example, if a user had previous access to a table with the GRANT statement, the REVOKE statement would undo that GRANT (note that the user may still have access to the table through one of the roles he or she belongs to). On the other hand, if the user had been denied access to the table at the user level, the REVOKE statement would delete (or undo) that denial.
The SQL Server security system allows for the definition of roles. An interesting note about the SQL Server security system is that role access is hierarchical, meaning that a SQL Server role can contain other SQL Server roles. As a result, whenever a hierarchical structure is put in place, user access can quickly become very difficult to manage or to determine. The user will receive the union of all of the permissions that have been granted to the user or to any one of the roles to which the user belongs, with denials taking precedence over grants -- i.e., one denial anywhere in the chain and the user has no access. ANALYSIS
We'll discuss some of the techniques for providing a solid security foundation for the data tier of an application that resides on Microsoft SQL Server 7 and 2000. While this is a broad topic, we'll focus on basic security concepts as well as column- and row-level security.
Basic SQL Server security concepts The security implementations in this article will rely on the basic internal security system provided by SQL Server. Before a user can be granted any level of permissions, a user account must be created on the SQL Server. The user account can be either a domain or local account (using sp_grantlogin), or if the server is operating in the Mixed Security Mode, it can be a built-in SQL Server login (using sp_addlogin). Once a login has been created on the server, the login must then be added as a valid user for any of the databases the user intends to access (using sp_grantdbaccess).
Now that the user has the ability to connect to the server (a login was created) and use one or more databases on the server (a user account was created for each database), the next step is to specify the level of access the user has inside each database. This is done with the GRANT, DENY, and REVOKE SQL DDL statements. The SQL GRANT statement is fairly well understood, so we won't dive into it here, but the DENY and REVOKE statements are less well known, especially for new database developers, so we'll focus on them.
The DENY statement The DENY statement is the opposite of the GRANT statement. Granting a database user access to a table (or object) specifies that the user has the specific right to access the table. Denying a database user access to a table (or object) specifies that under no circumstances should the user have access to the object. Therefore, if a user is denied access to an object either through his or her own user account or through any role (previously known in SQL 6.5 as groups) he or she belongs to, then the user will not be given access to the object.
The REVOKE statement The REVOKE statement simply indicates that a previously assigned security setting should be undone. For example, if a user had previous access to a table with the GRANT statement, the REVOKE statement would undo that GRANT (note that the user may still have access to the table through one of the roles he or she belongs to). On the other hand, if the user had been denied access to the table at the user level, the REVOKE statement would delete (or undo) that denial.
The SQL Server security system allows for the definition of roles. An interesting note about the SQL Server security system is that role access is hierarchical, meaning that a SQL Server role can contain other SQL Server roles. As a result, whenever a hierarchical structure is put in place, user access can quickly become very difficult to manage or to determine. The user will receive the union of all of the permissions that have been granted to the user or to any one of the roles to which the user belongs, with denials taking precedence over grants -- i.e., one denial anywhere in the chain and the user has no access. 
