Windows 2000 and SQL Server Security

  • Article

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

Existing Microsoft® Windows® 2000 accounts (Active Directory Users or Groups) must be granted permissions to connect to Microsoft® SQL Server™ before they can access a database. If all members of a Windows group require connections to SQL Server, you can grant login permissions for the group as a whole.

Managing group permissions is easier than managing permissions for individual users. If you do not want a group to be granted permissions collectively, you can grant permissions to connect to SQL Server for each individual user.

Active Directory Users and Groups

In Windows 2000, users are individuals who have an account that provides specific privileges to access information and resources. Granting permissions to users to develop, manage, and use workflow applications is dependent upon the integration of Windows 2000 domain accounts and SQL Server roles. If a number of users all have the same permissions, they can be treated as a single unit, called a group, which can be assigned permissions that apply to all members of the group. Individuals can be added to or removed from groups as desired.

There are two types of Windows groups: global and local.

Global groups contain user accounts from the Windows 2000 Server domain in which they are created. Global groups cannot contain groups or users from other domains and cannot be created on a computer running Windows 2000 Professional.

Local groups can contain user accounts and global groups from the domain in which they are created and any trusted domain. Local groups cannot contain other local groups.

In addition, Windows 2000 has predefined, built-in local groups, such as Administrators, Users, and Guests. By default, these built-in groups always are available on any Windows 2000 computer, unless they are removed explicitly.

To grant access to SQL Server to a Windows local or global group, specify the domain or computer name on which the group is defined, followed by a backslash, and then the group name. For example, to grant access to the Windows 2000 group SQL_Users, in the Windows 2000 domain LONDON, specify LONDON\SQL_Users as the group name.

However, to grant access to a Windows built-in local group, specify BUILTIN, instead of the domain or computer name. To grant access to the built-in Windows local group Administrators, specify BUILTIN\Administrators as the group name to add to SQL Server.

Note   You must have appropriate permissions on the server to create Windows groups or users or to create SQL Server users or roles.

For additional information about Windows accounts, see your Windows documentation.

SQL Server Logins

SQL Server logins are the account identifiers that control access to any SQL Server system. SQL Server will not complete a connection unless it has first verified that the login you specified is valid. This verification of the login is called authentication.

A member of the SQL Server sysadmin fixed-server role first must specify to SQL Server all the Windows accounts or groups that can connect to SQL Server. Your access to SQL Server is controlled by your Windows account or group, which is authenticated when you log on to the Windows operating system on the client.

When connecting, the SQL Server client software requests a Windows trusted connection to SQL Server. Windows will not open a trusted connection unless the client has logged on successfully using a valid Windows account. The properties of a trusted connection include the Windows group and user accounts of the client that opened the connection.

SQL Server gets the user account information from the trusted connection properties and matches them against the Windows accounts defined as valid SQL Server logins. If SQL Server finds a match, it accepts the connection. You are identified in SQL Server by your Windows group or user account.

Database Roles

Using database roles, you can collect users into a single unit to which you can apply permissions. Permissions granted to, denied to, or revoked from a role also apply to any members of the role.

SQL Server roles exist within a database and cannot span more than one database. Because roles are unique to each database, you can reuse a role name, such as "Reviewer" in each database that you create.

To assign users and groups to database roles, the users and groups must have valid Windows domain accounts and SQL Server logins.

Note   If you make any changes to the membership of database roles in your workflow application, you must synchronize the user directory for role permissions to work properly.

The advantages of using database roles include:

  • Users can belong to more than one database role at a time.
  • Roles can contain Windows accounts and other SQL Server users and roles.
  • A scalable model is provided for setting up the right level of security within a database.

It is easy to manage permissions in a database if you define a set of roles based on job functions and assign each role the permissions that apply to that job. Then, you can move users between roles rather than having to manage the permissions for each individual user.

**Note   **The owner of a role determines who can be added or removed from the role. The owner is either the user explicitly specified as the owner when the role is created or the user who created the role when no owner is specified. If you make any changes to the membership of database roles in your workflow application, you must synchronize the user directory for role permissions to work properly.

Database roles are created for a particular database. In SQL Server 7.0 and SQL Server 2000, users can belong to multiple roles. Because users can belong to more than one database role at a time, it is no longer required for users to assume temporarily the identity (and permissions) of other users through aliases.

Note   If you plan to make a template based on a workflow application, you should use role-based permissions for everything, because the set of database users will be different for each instance of a project based on the template.

Database User Accounts

While a SQL Server login makes it possible for a user to access SQL Server, a database user account is required for the user to access a specific database. Then, these user accounts can be associated with the roles defined in your workflow application.

A user account can be a member of any number of roles within the same workflow application. For example, a user can be a member of the admin role and the authors role for the same database, with each role granting different permissions.

The effective permissions on an object granted to a member of more than one role are the cumulative permissions of the roles, although denied permissions in one role has precedence over the same permissions granted in another role. For example, the admin role might grant access to a table while the authors role denies access to the same table. A member of both roles is denied access to the table, because denied access is the most restrictive.

See Also

Setting Up Accounts, Logins, Roles, and Users | Security Categories in Workflow Designer for SQL Server | Creating Database Roles | Creating Workflow Application Users | Assigning Users to Database Roles | Defining Permissions for Database Roles | Creating User and Group Accounts | Creating SQL Server Logins | The User Directory