認識SQL Server2000 Server Role 和 Database Role

          There are several fixed roles defined in SQL Server 2000 and SQL Server version 7.0 during setup. Users can be added to these roles to pick up the associated administration permissions. These are server-wide roles. 

Fixed server role	Description
sysadmin	Can perform. any activity in SQL Server.
serveradmin	Can set serverwide configuration options, shut down the server.
setupadmin	Can manage linked servers and startup procedures.
securityadmin	Can manage logins and CREATE DATABASE permissions, also read error logs and change passwords.
processadmin	Can manage processes running in SQL Server.
dbcreator	Can create, alter, and drop databases.
diskadmin	Can manage disk files.
bulkadmin	Can execute BULK INSERT statements.

You can get a list of the fixed server roles from sp_helpsrvrole, and get the specific permissions for each role from sp_srvrolepermission.

Each database has a set of fixed database roles. While roles with the same names exist in each database, the scope of an individual role is only within a specific database. For example, if Database1 and Database2 both have user IDs named UserX, adding UserX in Database1 to the db_owner fixed database role for Database1 has no effect on whether UserX in Database2 is a member of the db_owner role for Database2.

Fixed database role	Description
db_owner	Has all permissions in the database.
db_accessadmin	Can add or remove user IDs.
db_securityadmin	Can manage all permissions, object ownerships, roles and role memberships.
db_ddladmin	Can issue ALL DDL, but cannot issue GRANT, REVOKE, or DENY statements.
db_backupoperator	Can issue DBCC, CHECKPOINT, and BACKUP statements.
db_datareader	Can select all data from any user table in the database.
db_datawriter	Can modify any data in any user table in the database.
db_denydatareader	Cannot select any data from any user table in the database.
db_denydatawriter	Cannot modify any data in any user table in the database.

You can get a list of the fixed database roles from sp_helpdbfixedrole, and get the specific permissions for each role from sp_dbfixedrolepermission.

Every user in a database belongs to the public database role. If you want everyone in a database to be able to have a specific permission, assign the permission to the public role. If a user has not been specifically granted permissions on an object, they use the permissions assigned to public.