SQL Server 2008のデータベース管理システムは、SQLステートメントを使用して、ログインユーザの手順を作成するために説明した

-- Server Roles.
-- Fixed server roles have a fixed set of permissions and apply server-wide. They are used exclusively to administer SQL Server and the permissions assigned to them cannot be changed. 
--Logins can be assigned to fixed server roles without a user account existing in the database.
-- Described in order from the lowest level role (bulkadmin) to the highest level role (sysadmin).
-1, Bulkadmin: Members of this server role can run the BULKINSERT statement. This statement allows the import of data from a text file into a SQLServer 2008 database.
-- Designed for domain accounts that need to perform high-volume inserts into the database.
-2. Dbcreator: Members of this server role can create, change, delete and restore any database. This is not only a role suitable for assistant DBAs.
-- it may also be a role suitable for developers.
-3, Diskadmin: This server role is used to manage disk files, such as mirroring databases and adding backup devices. It is suitable for assistant DBAs.
-4. Processadmin: SQLServer 2008 is capable of multitasking, meaning it can do multiple events by executing multiple processes.
-- For example, SQLServer 2008 can spawn a process for writing data to the cache and another process for reading data from the cache.
-- Members of this role can end (called delete in SQLServer 2008) processes.
--5. Securityadmin: Members of this server role will manage login names and their attributes. They can authorize, deny, and revoke server-level privileges.
-- can also authorize, deny, and revoke database-level permissions. In addition, they can reset the password of the SQLServer 2008 login name.
--6. Serveradmin: Members of this server role can change server-wide configuration options and shut down the server.
-- For example, how much memory SQLServer 2008 can use or monitor how much information is sent over the network, or shut down the server, this role can relieve the administrator of some of the administrative burden.
--7. Setupadmin: Designed for users who need to manage linked servers and control the stored procedures that are started.
-- Members of this role can be added to setupadmin, can add, delete and configure linked servers, and can control the startup process.
--8. Sysadmin: Members of this server role are authorized to perform any task in SQLServer 2008.
--9、Public:There are two major features, first, there are no privileges in the initial state; second, all database users are its members.
--Database Roles
-- Fixed database roles have a set of predefined permissions that are designed to allow you to easily manage permission groups. Members of the db_owner role can perform all configuration and maintenance activities on the database.
-- Microsoft provides nine built-in roles to facilitate granting users special sets of permissions at the database level
--db_owner:The user of this role can perform any operation in the database.
--db_accessadmin:Members of this role can add or remove users from the database.
--db_backupopperator:Members of this role are allowed to backup the database.
--db_datareader:Members of this role are allowed to read any data from any table.
--db_datawriter:Members of this role are allowed to write data to any table.
--db_ddladmin: Members of this role are allowed to add, modify, or delete any object in the database (i.e., they can execute any DDL statement).
--create a login user
create login Diviner
with
password='825991',
default_database=WangPing
--Use the procedure to grant the role of Diskadmin to the new login user
exec sp_addsrvrolemember 'Diviner','Diskadmin'
-- Create a database account for the login account, and complete the mapping between the login user and the database account, it is generally recommended to keep the database user and the login user name the same
create user Diviner for login Diviner with default_schema=dbo
-- Use the stored procedure to grant the new database user permission to execute ddl
exec sp_addrolemember 'db_ddladmin','Diviner'
--grant privileges to our newly created user
Grant select,update,delete,insert
ON T_Mystudents 
to Diviner
--Revoke permissions
revoke select,insert,update,delete
on T_Mystudents
from Diviner
--create a role (and automatically assign the role to the user)
create role manager authorization Diviner
---- assigns the role to the user Divinier (not available)
--grant manager
--to Diviner
---- assigns the role manager to the user Diviner (not available)
--revoke manager
--from Diviner cascade
--delete role
drop role manager 
--authorize the role
grant select,insert,delete,update
on T_Mystudents
to manager
--Revoke the privileges of the role
revoke select,insert,delete,update
on T_Mystudents
from manager
--delete database users
drop user Diviner
--delete login user
drop login Diviner