SQL Server 2008 database management system uses SQL statements to create logged-in user detailed steps

--Server role:
Fixed server roles have a fixed set of permissions and apply to the entire server scope. They are dedicated to managing SQL Server and cannot change the permissions assigned to them.
--You can assign logins to fixed server roles without the presence of a user account 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 Bulkinsert statements. This statement allows you to import data from a text file into a SQL Server 2008 database.
--Designed for domain accounts that need to perform bulk inserts into the database.

--2, Dbcreator: Members of this server role can create, change, delete, and restore any database. This is not just a role for the Assistant DBA,
-It may also be a role for developers.

--3, diskadmin: This server role is used to manage disk files, such as mirror databases and add backup devices. It is suitable for assistant DBAs.

--4, Processadmin:sqlserver 2008 can multitask, which means that multiple events can be done by executing multiple processes.
--for example, SQL Server 2008 can generate a process to write data to the cache while generating another process to read the data from the cache.
--members of this role can end (called delete) processes in SQL Server 2008.

--5, securityadmin: Members of this server role will manage logins and their properties. They can authorize, deny, and REVOKE server-level permissions.
--You can also authorize, deny, and revoke database-level permissions. Additionally, they can reset the password for the SQL Server 2008 login.

--6, Serveradmin: Members of this server role can change server-wide configuration options and shut down the server.
This role can alleviate some administrative burden on administrators, such as how much memory SQL Server 2008 can use or how much information is being sent over the network, or shutting down the server.

--7, Setupadmin: Designed for users who need to manage linked servers and control which stored procedures are started.
--members of this role can be added to the setupadmin to add, remove, and configure linked servers and to control the startup process.

--8, Sysadmin: Members of this server role have permission to perform any task in SQL 2008.

--9, public: There are two major characteristics, first, the initial state without permission; second, all database users are members of it.

--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 9 built-in roles to grant users a special set of permissions at the database level

--db_owner: The user of this role can perform any action in the database.

--db_accessadmin: Members of this role can add or remove users from the database.

--db_backupopperator: Members of this role allow the database to be backed up.

--db_datareader: Members of this role allow any data to be read from any table.

--db_datawriter: Members of this role allow data to be written to any table.

--db_ddladmin: Members of this role allow any object to be added, modified, or deleted in the database (that is, any DDL statements can be executed).

--Create a login user
Create Login Diviner
With
Password= ' 825991 ',
Default_database=wangping

--Use stored procedures to grant the diskadmin role to the newly signed-in user
EXEC sp_addsrvrolemember ' diviner ', ' diskadmin '

--Create a database account for the login account, complete the login user's and Database account mapping, the general recommendation is to keep the database user and login user name consistent
Create User Diviner for login Diviner with default_schema=dbo

--use stored procedures to authorize new database users with Execute DDL permissions
EXEC sp_addrolemember ' db_ddladmin ', ' Diviner '

--Empower us to create new users
Grant Select,update,delete,insert
On t_mystudents
To Diviner

--Reclaim 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

----Assigning roles to User Divinier (not available)
--grant Manager
--to Diviner

----Assign to User Divinier Manager role assigned to user (not available)
--revoke Manager
--from Diviner Cascade

--Remove roles
Drop Role Manager

--Give role authorization
Grant Select,insert,delete,update
On t_mystudents
To Manager

--the ability to reclaim roles
Revoke Select,insert,delete,update
On t_mystudents
From manager

--Delete database user
Drop User Diviner

--Delete logged-in user
Drop Login Diviner

SQL Server 2008 database management system uses SQL statements to create logged-in user detailed steps