ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 认识SQL Server2000 Server Role 和 Database Role

认识SQL Server2000 Server Role 和 Database Role

原创 Linux操作系统 作者:tolywang 时间:2008-12-18 16:28:06 0 删除 编辑

          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博客 ” ,链接:http://blog.itpub.net/35489/viewspace-515967/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
Oracle , MySQL, SAP IQ, SAP HANA, PostgreSQL, Tableau 技术讨论,希望在这里一起分享知识,讨论技术,畅谈人生 。

注册时间:2007-12-10

  • 博文量
    5595
  • 访问量
    13471563