Domain

Computers sharing a common security database.
Four types of computers participate in domain security: primary domain controllers (PDCs), backup domain controllers (BDCs), servers, and workstations.

It is not recommended to install Microsoft SQL Server on a PDC or a BDC machine (because those computers perform the resource-intensive tasks of maintaining and replicating the domain's security accounts database and performing network login authentications).  It would be better to install SQL Server on a computer that is a member of a domain, rather than on the machine that is either a PDC or BDC.

Groups & Users

Permissions assigned to a Group are granted to user members of that Group.

Accounts

An operating system account is called a “UserName”.  An SQL Server account is called a “Login ID”.

Alias

An alias is a database Login ID that is shared by several database login IDs.  A database alias allows you to treat more than one person as the same user inside a database, giving all of them the same permissions.  Note: a database alias is analogous to a “group” at the operating system security level.

Login Security Types

There are three types of SQL Server security:  integrated, standard, and mixed.

Integrated

Only trusted (multi-protocol or named pipes) connections are allowed.

When a user of integrated security logs in to SQL Server over a trusted connection, the user is mapped to a SQL Server login ID, to a default SQL Server login ID (usually called guest), or¾if the user has administrative privileges¾to SA.

If the SQL Server is configured for integrated security, an (SQL Server) login ID for each user is not necessary.  Instead, use the SQL Security Manager utility to automatically map NT-based “UserNames” to SQL Server “Login Ids” by “Group” i.e. at the Group level.  IOW, you grant trusted connection privileges to all members in the desired Group at once; similarly, you revoke privileges for all Group members at once. If you want to deny SQL Server access to a member of a group with SQL Server privilege, use the Windows NT User Manager to remove the user from that operating system Group. The SQL Server Login ID and any database usernames associated with that Windows NT account will remain in SQL Server but will be inaccessible to that user. You can periodically clean up these login IDs and usernames by using the Find Orphan SQL Login IDs option in the Search for Account Information dialog box.

The recommended way to implement SQL Server integrated security is to create two or more groups, as follows:

• A SQLUsers group, to be assigned user privileges
• A SQLAdmins group, to be assigned system administrator privileges. (Note that when SQL Server is set up with integrated security, the Administrators group on the local computer is automatically mapped to the sa login ID. This can be disabled if you prefer.).  In other words, a member of the local Administrators group, inherits SQL Server SA privileges under trusted connections.
• Avoid placing Windows NT “UserNames” in more than one Windows NT group that can access SQL Server, because SQL Server does not allow overlapping group membership within databases. Keeping your Windows NT UserNames limited to one group will allow you to use SQL Security Manager to keep database groups consistent with Windows NT groups.

Granting Privileges

When you grant privileges to a Windows NT Group, you are granting Trusted Security SQL Server access to each member of that Group. You can grant either user privilege or system administrator privilege.

To grant user privilege

1.   From the View menu, choose User Privilege.

2.   From the Security menu, choose Grant New.
      The Grant User Privilege dialog box appears.

3.   In the Grant Privilege box, select the group (e.g. “Users” or “Power Users” etc.) that will have access to SQL Server.
      To show all local groups on the Windows NT-based computer, choose Local Groups.

To show all groups on the default domain, select All Groups on the Domain.

4.   The Add Login IDs for individual group members box is selected. This specifies that you want to create SQL Server login IDs for each user in the group.

If you do not want each user in the Group to have a separate SQL Server login ID, clear the check box. If you do not assign each user an individual Login ID, the only way the user can access SQL Server is through the default Login ID (usually "guest"). If you want to set up security like this, be sure you've added a guest account when you set security options with the SQL Server setup program.

5.   To add users in the group to a database, so that database is their default database when they log in to SQL Server, select the Add Users to Databases box, and then select the database. It is recommended that you assign users to another default database other than master, to discourage users from creating database objects in master. If you do not assign users to a default database, master becomes their default database.

6.   Choose the Grant button.

To grant system administrator (sa) privilege (used by SQL Server):

1.   From the View menu, choose SA Privilege.

2.   From the Security menu, choose Grant New.
      The Grant System Administrator Privilege dialog box appears.
3.   In the Grant Privilege box, select the group that will have access to SQL Server.

To show all local groups on the Windows NT-based computer, choose Local Groups. To show all groups on the default domain, select All Groups on the Domain.

Note that when you grant system administrator privilege, the Add Login IDs and Add Users to Database boxes are dimmed. This is because when you grant to a group system administrator privilege to SQL Server, the users in the group are automatically mapped to the SA login ID and master is the default database.

4.   Choose the Grant button.

NOTE: If you grant permission to the "Domain Users" group on the default domain, you will see, as Domain members, all of the Windows NT computer accounts (ending in a "$") in addition to all of the user accounts defined on the domain.  For example, when you “grant” database access to all in a group (e.g. “Users”), separate SQL Server Login ID’s are created in the form “Domain_Login ID” (e.g. Barrett_bid1).  Previously-created standard security Login ID’s remain as apparent duplicate Login ID’s, but without the DomainName_ prefix.  Thus, you’d see a Login ID for “bid1” in addition to one for “Barrett_bid1”.  Since all computer accounts do not need SQL Server login permissions, it is a better idea to create a separate group on the Domain, containing only the valid user accounts, then grant permission to this group.

If you remove an operating system UserName from a Group, when the SQL Server Security Manager is refreshed, the SQL Server Login ID will be removed from the Group showing in the SQL Server Security Manager, also.

Standard

User must provide a valid (SQL Server) login ID and password.

Mixed

Trusted connections (as used by integrated security) and non-trusted connections (as used by standard security) can be established.

Permissions

… can be granted only by the SA (database system administrator) or the database owner (dbo).

Object

Statement

Statement permissions apply to these statements: CREATE DATABASE, CREATE DEFAULT, CREATE PROCEDURE, CREATE RULE, CREATE TABLE, CREATE VIEW, DUMP DATABASE, and DUMP TRANSACTION.

Software Installations

If you store a password in the (MS Access) application, rather than using “Trusted Security” when linking the remote tables, the application will logon to the remote server as the UserName that created the application.  If a user attempts to use the software from another UserName account, they will get an “ODBC call failed” error.