Learn to set up SQL Server account security through Access projects
In "Access Granted," December 2000, I discuss securing access to SQL Server systems by using login accounts, which let users log in to SQL Server but don't necessarily provide access to the individual databases on the server. In this article, I review some database security principles and demonstrate simple techniques for securing the tables within a database on a SQL Server machine. You can use both user accounts and fixed database roles with login accounts to control access to SQL Server database tables. First, I briefly explain important design considerations for database access. Then, I describe techniques for manipulating the Microsoft Access project UI to control security for the tables within a database.
User Accounts
A login account lets a user access a database server; a user account identifies a user within a database. Any login can have multiple user accounts—one for each database on the SQL Server machine that the login account can access. The collection of user accounts within a database defines the users who can access that database.
Some important relationships exist between logins and two special user accounts. The Database Owner (dbo) user account is the default owner of a database. Every database must have a dbo user. You can't drop the dbo user, which has all permissions for all objects within the database, including permission to create new user accounts. The database creator holds dbo user status unless database ownership changes. Also, any login, such as sa, that belongs to the System Administrators fixed server role is automatically a dbo user for all databases on a server.
The guest user account is an optional user account. All logins—even if they don't have a specific user account—can access a database through the guest account if it exists on the specific database. If the database has no guest user account, a login attempting to connect to a database will fail unless the login has a user account explicitly defined for it. By default, the guest user account has no permissions relating to user-defined database objects, such as tables. You can use the Access project UI to create the guest user account. And you can assign database access permission to the guest account just as you do to other user-defined accounts.
Fixed Database Roles
SQL Server has nine fixed database roles, which define standard kinds of database access. When you assign a user to a fixed database role, you give that user a predefined cluster of permissions. From their memberships in fixed database roles, users inherit permissions.
Several fixed database roles are especially pertinent to the use of tables in a database. The db_datareader role lets a user account perform SELECT statements against any table in a database. However, this role doesn't enable other typical functions for tables, such as INSERT, UPDATE, and DELETE. If you assign a user to the db_datawriter role and the db_datareader role, then the user can execute INSERT, UPDATE, and DELETE statements against all the tables in a database. SQL Server Books Online (BOL) states that the db_datawriter role supports data-maintenance functions, such as UPDATE, without the addition of the db_datareader role. However, in my experience, a user requires both roles to enable UPDATE statements.
User accounts that belong to the db_ddladmin role can create and drop tables in a database. However, membership in this role doesn't include the permission to execute SELECT, INSERT, UPDATE, or DELETE statements. A user can belong to any combination of fixed database roles. Therefore, by assigning a user account to the db_datareader and db_datawriter roles but not the db_ddladmin role, you can enable only SELECT, INSERT, UPDATE, and DELETE permissions without conveying table-creation capabilities.
The db_owner role has all the permissions in a database, including the ability to assign role status to other users. Membership in this role establishes a user as a dbo user. If a login is a dbo user or if a user has membership in the db_owner role, the corresponding user account has unlimited permissions within the database.
Two system stored procedures can give you more information about fixed database roles. The sp_helpdbfixedrole procedure lists and briefly describes all fixed database role names. The system stored procedure sp_dbfixedrolepermission lists the specific permissions associated with each fixed database role. You can create a script inside an Access project's stored procedure template to run either sp_helpdbfixedrole or sp_dbfixedrolepermission. The following example code demonstrates the syntax for listing the fixed database role names and descriptions:
ALTER PROCEDURE list_fixed_db_roles
AS
EXEC sp_helpdbfixedrole