SQL Server 2008 server logins, roles, database users, roles, schema relationships

SQL Server logins, server roles, database users, database roles, schema differences contact

1. A database user can correspond to multiple schemas (schemas are table containers). The schema contains a database table.

2. A database role may involve multiple schemas. The database role corresponds to permissions.

3. One user corresponds to a database role.

4. Logins and database users are one-to-many at the server level, at the database level.

Server login name: Refers to the user who has permission to log on to a server;

Server role: Refers to a fixed set of server users, the default is 9 groups;

• Logins must belong to some roles, default to public
• Server roles are not allowed to change
• Does not necessarily have permission to manipulate the database after logging on

Database User: Refers to the user who has permission to operate the database;

Database role: Refers to a fixed set of database roles with certain permissions;

Database schema: Refers to the container of database objects;

• The database user corresponds to the server login so that the logged-on person can manipulate the database
• Database roles can be added and different permissions can be customized
• Database schemas, similar to the namespace of database objects, where users Access database objects through schemas

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

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

First say the database level settings in SQL Server:

Schema level-> Data Object level, database level, server-level, for example: Server.DataBase1.dbo.Table1; This means that the Table1 is the DBO schema.

, the DBO schema belongs to the DATABASE1 database, DataBase1 This database belongs to server. The structure inside is actually a container, as if it were an object-oriented

namespace, a user can have multiple schemas, but cannot operate on schemas that are not owned. A database role is a permission organization for data objects within different schemas, and may also involve

Multiple schemas, when a user is converted to a database role, if the user does not own a schema and the database role owns it, it will make an error when it operates on that schema.

Role, a role means an identity, in the database server is an organization of a series of permissions.

server logins , which are users who have permission to log on to a server, can create new logins with permissions, and the Super Administrator's login is SA

server Role , a fixed set of server users, the default is 9 groups;

• Logins must belong to some roles, default to public
• Server roles are not allowed to change
• Does not necessarily have permission to manipulate the database after logging on

database user , refers to the user who has permission to operate the database;

A database role , which is a fixed set of database roles with certain permissions;

database schema , which refers to the container of database objects;

• The database user corresponds to the server login so that the logged-on person can manipulate the database
• Database roles can be added and different permissions can be customized
• Database schemas, similar to the namespace of database objects, where users Access database objects through schemas

Logins and users at the server level are one-to-many, and in the database is one-to-two. For example server this server has 4 databases, DB1,DB2,DB3,DB4, each database has a user user1,user2,user3,user, in the creation of a login my name can be through the user mapping operation, Specify the user for this login in each specific database, for example, you can specify that my user in DB1 is USER1, it is unique when using the database, and I can not switch users in DB1 unless you reassign it to the DB1 database user mapping.

The user is generally rights-managed, when creating a new user is this:

You need to specify its login name, which is also part of the mapping operation, and you can specify its default schema, either by not specifying the DBO, or by specifying other schema and role members it owns, although there is no default database role.

Here's a look at creating a new role:

You can specify this role to have those schemas when you create a new one, but these structures must be in this database, the schema that the default schema is for the current user, such as the schema of the current user is dbo, the default schema used when the permissions are defined in the corner is dbo, and of course you can specify other schemas.

The new database login name, after login can not modify their server role to make their own permissions to become larger (unless the sysadmin role because it is the largest), also can not modify the role of users mapped to the database is its permissions become larger (itself is the Dbowner database role can be modified Because the permissions of the Dbowner role are already the largest)

Bottom line: (login name, login account) You cannot modify the server role of your login name and the role of the database user name mapped by your own login to make its permissions larger, allowing only changing roles to make permissions smaller (PS: no one is willing to do so)

Add a database User step:

Step1: Add Server login account (choose security = login name + Right click Add login name)

Step2: Add this account to the target database user name (default and login account name is the same) configure the user database role (note: You can also go to the target database in the future = + Security and right-click the target user name = + Property + Modify its corresponding permissions Equivalent synchronization when the login mappings are modified there)

Deleting a database user step is just the opposite:

Step1: First delete the user name of the corresponding target database (SELECT database = + Target DATABASE = + Security + Right click Target User select Delete)

Step2: Delete the server login name (choose security = login name and right click the target login name to select Delete)

SQL Server 2008 server logins, roles, database users, roles, schema relationships