认识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.
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/35489/viewspace-515967/ ，如需转载，请注明出处，否则将追究法律责任。
