Groups

There are no groups in Microsoft® SQL Server™ version 7.0. The groups from earlier versions of SQL Server have been replaced with roles, which are more powerful. You can, however, manage SQL Server security at the level of an entire Microsoft Windows NT® group.

If you use sp_grantlogin and specify the name of a Windows NT group, all members of the group can then connect to SQL Server using Windows NT Authentication.

After the group has been authorized to connect, you can use sp_grantdbaccess to associate the group members with a user ID in each database they need to access. You can use two methods:

• Associate the group with a user ID in the database.

  In this case, all members of the group will be associated with that user ID when they reference the database.

• Associate an individual user account in the Windows NT group with a user ID in the database.

  This individual will be associated with the user ID when they reference the database. None of the other individuals in the group will be associated with the user ID. They will be assigned the user ID associated with the group login.

Consider a Windows NT group NETDOMAIN\Managers with three members: NETDOMAIN\Sue, NETDOMAIN\Fred, and NETDOMAIN\Mary. The following Transact-SQL statements add the Windows NT group as both a login and a user in the sales database, and then associate NETDOMAIN\Sue with a specific user ID:

USE master

GO

-- Authorize all members of NETDOMAIN\Managers to connect

-- using Windows NT Authentication.
sp_grantlogin ‘NETDOMAIN\Managers'

GO

-- Make sales the default database for all members.

sp_dbdefault 'NETDOMAIN\Managers', 'sales'

USE sales

GO

-- Grant all members of the group access to sales

-- No user ID is specified, so SQL Server creates

-- one named 'NETDOMAIN\Managers'
sp_grantdbaccess 'NETDOMAIN\Managers'

GO

-- Grant a specific member of the group access to

-- sales with a specific user.

sp_grantdbaccess 'NETDOMAIN\Sue', 'Sue'

Permissions can now be granted to either user NETDOMAIN\Managers or user Sue:

USE sales

GO
GRANT SELECT ON SalesTable TO NETDOMAIN\Managers

GO

GRANT UPDATE ON SalesTable to NETDOMAIN\Sue

The permissions applied to NETDOMAIN\Sue are the union of the permissions granted, revoked, or denied to both the NETDOMAIN\Managers or Sue users. Any DENY permission overrides any corresponding GRANT permissions.

Unless their Windows NT account has been associated with a specific user, members of a group are subject to the permissions assigned to the user associated with the group. If a member of the group creates an object, however, the owner name of the object is their Windows NT account name, not the group name. Consider the NETDOMAIN\Manager account. If NETDOMAIN\Fred connects to the sales database, he can see all tables for which NETDOMAIN\Managers has been granted SELECT permission. If NETDOMAIN\Fred executes the following statement, the table is created as sales.NETDOMAIN\Fred.TableX, not sales.NETDOMAIN\Managers.TableX:

CREATE TableX (cola INT PRIMARY KEY, colb CHARACTER(200))

See Also