SQL Server 2008 server logins, roles, database users, roles, architecture knowledge points Summary

Source: Internet
Author: User
Tags bulk insert

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


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

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.

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

--Add or remove Windows NT 4.0 or Windows 2000 groups and users and SQL Server users in the database.

--View all data from all user tables in the database.

--Add, change, or delete data from all user tables in the database

--Add, modify, or drop objects in the database (Run all DDL)

--Manage roles and members of SQL Server 2000 database roles and manage statements and object permissions in the database

--have permission to back up the database

--Deny permission to select database data

--Deny permission to change database data

SQL Server 2008 server logins, roles, database users, roles, architecture knowledge points Summary

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.