The relationships of SQL Server 2008 server logins, roles, database users, roles, and schemas can be represented in one of the following graphs:
The use case process is as follows:
New Server logon name:
Login Role selected:
New Database DB1:
Then create a new database schema SCHEMA1
Re-create database user name User1
The server logon name belongs to a group of server roles;
Server logins require user mapping of the database before they have permission to manipulate the database
Database user belongs to a group of database roles to get permissions on the operational database
Database roles have a corresponding database schema, and database users can directly own the schema through roles
Server role
Public
sysadmin--any activity in SQL Server. The permissions for this role span all other fixed server roles.
ServerAdmin-Configures server-wide settings.
Setupadmin--Add and remove linked servers and perform some system stored procedures (such as sp_serveroption).
Securityadmin--Management Server login.
Processadmin--manages processes running in an instance of SQL Server.
DBCreator--Create and change databases.
Diskadmin--Manage disk files.
Bulkadmin-Executes the BULK INSERT statement.
Database roles
Public
The--public role is a special database role that is owned by each database user. Public role:
--Captures all default permissions for users in the database.
--You cannot assign users, groups, or roles to it because they are part of the role by default.
-Included in each database, including master, msdb, tempdb, model, and all user databases.
--cannot be removed.
db_owner
-Activities for all database roles, as well as other maintenance and configuration activities in the database.
--The role's permissions span all other fixed database roles.
Db_accessadmin
--Add or remove Windows NT 4.0 or Windows 2000 groups and users and SQL Server users in the database.
Db_datareader
--View all data from all user tables in the database.
Db_datawriter
--Add, change, or delete data from all user tables in the database
db_ddladmin
--Add, modify, or drop objects in the database (Run all DDL)
Db_securityadmin
--Manage roles and members of SQL Server 2000 database roles and manage statements and object permissions in the database
Db_backupoperator
--have permission to back up the database
Db_denydatareader
--Deny permission to select database data
Db_denydatawriter
--Deny permission to change database data
SQL Server 2008 server logins, roles, database users, roles, architecture knowledge points Summary
