The tools provided and recommended for managing SQL Server security are SQL Security Manager and SQL Enterprise Manager. Each of these tools is started by choosing its icon from the Microsoft SQL Server 6.0 program group. You can also use a Windows NT tool, User Manager, to create Windows NT users and groups.
These are the SQL Server security tasks you will perform with each tool.
| To perform this task | Use this tool |
|---|---|
| Set Server Security Options | SQL Enterprise Manager or SQL Setup |
| Create Windows NT users and groups | User Manager |
| Authorize Windows NT groups and users to access SQL Server | SQL Security Manager |
| Manage SQL Server login IDs | SQL Enterprise Manager |
| Manage database users | SQL Enterprise Manager |
| Manage object permissions | SQL Enterprise Manager |
| Manage statement permissions | SQL Enterprise Manager |
Note that this is a summary table and that each tool offers capabilities beyond those listed here. For more detailed information on the capabilities of each tool, see its online Help. For an overview of SQL Enterprise Manager capabilities, see Chapter 2, Introducing the SQL Distributed Management Framework.
If you have been granted appropriate access, you can also use Windows NT User Manager to create the Windows NT groups and users authorized to access SQL Server. User Manager is an administration tool provided as part of the Windows NT operating system software. On computers running the Windows NT Workstation operating system, this tool is named User Manager and can manage security for the local computer. On computers running the Windows NT Server operating system, this tool is named User Manager for Domains and can manage both domain- and computer-level security. Throughout this documentation, we refer to both versions of this tool simply as "User Manager."
Start User Manager by choosing its icon from the Administrative Tools program group. For more information, see the User Manager online Help. If you have not been granted access to use User Manager, have a Windows NT Administrator use this tool for you.
You can also administer SQL Server security by using a number of system stored procedures and extended stored procedures. A summary of these stored procedures follows. For more information on using these stored procedures, see the Microsoft SQL Server Transact-SQL Reference. Note that you are not required to use these stored procedures to manage security; SQL Security Manager and SQL Enterprise Manager are the recommended tools.
| Stored procedure | Description |
|---|---|
| sp_addlogin | Authorizes a new SQL Server user by adding an entry to the syslogins table |
| sp_addalias | Maps one database user to another. |
| sp_addgroup | Adds a new group to a database |
| sp_addremotelogin | Authorizes a new remote SQL Server user by adding an entry to the sysremotelogins table. |
| sp_adduser | Adds a new user to a database. |
| sp_changegroup | Changes a user's database group. |
| sp_defaultdb | Changes a user's default database. |
| sp_defaultlanguage | Changes a user's default language. |
| sp_dropalias | Removes an alias login ID. |
| sp_dropgroup | Removes a group from a database. |
| sp_droplogin | Removes a SQL Server login ID. |
| sp_dropremotelogin | Removes a remote user login ID. |
| sp_dropuser | Removes a user from the current database. |
| sp_helpgroup | Reports information about one or all groups. |
| sp_helpremotelogin | Reports information about one or all remote server logins. |
| sp_helpprotect | Reports permissions by database object and, optionally, by user for that object. |
| sp_helpuser | Reports information about the users of a database. |
| sp_password | Adds or changes a password for a SQL Server login ID. |
| sp_remoteoption | Displays or changes remote login options. |
| xp_enumgroups | Provides a list of local Windows NT groups or groups defined in a specified Windows NT domain. |
| xp_grantlogin | Grants SQL Server access to a Windows NT - based group or user. |
| xp_loginconfig | Reports the login security configuration of the server. |
| xp_logininfo | Reports the type of account (group or user), the privilege level of the account, the map name of the account, and the permission path by which the account has access to SQL Server. |
| xp_revokelogin | Revokes SQL Server access from a Windows NT - based group or user. |