Windows NT and SQL Server Security
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 Windows NT accounts (users or groups) must be granted permissions to connect to SQL Server before they can access a database. If all members of a Windows NT group will be connecting to SQL Server, you can grant login permissions for the group as a whole.
Managing group permissions is much easier than redundantly managing permissions for individual users. If a Windows NT group should not be granted permissions collectively, you can grant permissions to connect to SQL Server for each individual Windows NT user.
To grant users access to team solutions
Create Windows NT domain accounts. For details, see Creating Windows NT User and Group Accounts.
Create SQL Server login accounts. For details, see Creating SQL Server Logins.
Implement database roles in the team solution. For details, see Creating Database Roles.
Create team solution users. For details, see Creating Team Solution Database Users.
Windows NT Users and Groups
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 team solutions is dependent upon the integration of Windows NT domain accounts and SQL Server roles.
There are two types of Windows NT groups: local and global.
Global groups contain user accounts from the Windows NT 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 NT Workstation.
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 NT has predefined, built-in local groups — for example Administrators, Users, and Guests. By default, these built-in groups are always available on any Windows NT computer, unless they are explicitly removed.
To grant a Windows NT local or global group access to SQL Server, 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 NT group SQL_Users, in the Windows NT domain LONDON, specify LONDON\SQL_Users as the group name.
However, to grant access to a Windows NT built-in local group, specify BUILTIN instead of the domain or computer name. To grant access to the built-in Windows NT 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 NT groups or users or create SQL Server users or roles.
For details, see Creating Windows NT User and Group Accounts.
For additional information about Windows NT accounts, see your Windows NT documentation.
SQL Server Logins
SQL Server Login IDs 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 ID you specified is valid. This verification of the login is called authentication.
A member of the SQL Server sysadmin fixed server role must first specify to SQL Server all the Windows NT accounts or groups that can connect to SQL Server. Your access to SQL Server is controlled by your Windows NT 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 NT trusted connection to SQL Server. Windows NT will not open a trusted connection unless the client has logged on successfully using a valid Windows NT account. The properties of a trusted connection include the Windows NT 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 NT 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 NT group or user account.
For details, see Creating SQL Server Logins.
For additional information, see "Logins" in the SQL Server Books Online.
Database Roles
Database roles are powerful tools that make it possible for you to collect users into a single unit against which you can apply permissions. Permissions granted to, denied to, or revoked from a role also apply to any members of the role.
The advantages of using database roles include:
Users can belong to more than one database role at a time.
Roles can contain Windows NT 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.
Database roles are created for a particular database. In SQL Server version 7.0, users can belong to multiple roles. Because users can belong to more than one database role at a time, it is no longer necessary for users to temporarily assume the identity (and permissions) of other users through aliases.
Note If you plan to make a template based on a team solution, you should use role-based permissions for everything, because the set of database users will be different for each instance of a solution based on the template.
For details, see Creating Database Roles.
For additional information about database roles, see "Roles" in the SQL Server Books Online.
Note If you make any changes to the membership of database roles in your team solution, you need to synchronize the user directory in order for role permissions to work properly.
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 team solution.
A user account can be a member of any number of roles within the same team solution. 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 a denied permissions in one role has precedence over the same permissions granted in another role. For example, the admin role may 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.
For details, see Creating Team Solution Database Users.