Groups
There are no groups in Microsoft® SQL Server™ 2000 or SQL Server version 7.0. You can, however, manage SQL Server security at the level of an entire Microsoft Windows NT® or Microsoft Windows® 2000 group.
If you use sp_grantlogin and specify the name of a Windows NT or Windows 2000 group, all members of the group can then connect to SQL Server using Windows Authentication.
After the group has been authorized to connect, you can use sp_grantdbaccess to associate the group members with a user identifier (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 or Windows 2000 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 or Windows 2000 group NETDOMAIN\Managers with three members: NETDOMAIN\Sue, NETDOMAIN\Fred, and NETDOMAIN\Mary. The following Transact-SQL statements add the Windows NT or Windows 2000 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 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 or Windows 2000 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 or Windows 2000 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))