Chapter 2 user authentication, Authorization, and Security (8): Create a database user that maps to the login name __ Database

原文出处：http://blog.csdn.net/dba_huangzj/article/details/38944121，专题目录：http://blog.csdn.net/dba_huangzj/article/details/37906349

No person shall, without the consent of the author, publish in the form of "original" or be used for commercial purposes without any liability.

Last article: http://blog.csdn.net/dba_huangzj/article/details/38895357

Preface:

Logins are used to authorize and access server resources, and you need to map users inside the database if you need to access the database. The user is a database-level security principal, and access to the database resource is granted to the user, not the login name.

implementation:

There are two ways to create database users, the first of which is the property page for the login at the server level, and the other is implemented in the security → users node of the database, which is demonstrated using the second way:

1. In SSMs, select the database node and expand the corresponding database, select Security, right-click User, select New user

2. On the General page, you can select the following user types:

Type brief Description:

User type	Describe
SQL user with login (SQL users with login name)	A user mapped to a SQL login.
SQL user without login (SQL users with no login name)	Users who do not have a login mapped to any server level can simulate execution using the EXECUTE AS user command.
User mapped to a certificate/user mapped to a asymmetric key (users mapped to certificate/asymmetric key)	A user created with a signature key allows mapping from a remote entity.
Windows user (Windows users)	The user who is mapped to a Windows Authentication logon name.

3. Enter the user name, can be the same as the login name, or use other permitted names, but can not use some keywords, reserved words. Select a login name (or enter the mapped certificate, key). Then select the default schema, and if no schema is selected, SQL Server uses dbo as the default schema. In addition to graphical operations, you can also use the following T-SQL implementation:

Use marketing; 
CREATE USER [Fred] for LOGIN [Fred];

4. If you want to query the users you have created, you can use the Sys.database_pr\incipals catalog view to:

SELECT Dp.name as UserName, sp.name as LoginName, Dp.default_language_name, Dp.default_schema_name, Dp.type_desc, Dp.create_date from 
sys.database_principals dp 
JOIN sys.server_principals SP on dp.sid = Sp.sid 
WHERE Dp.type in (' S ', ' U ') and 
dp.principal_id > 4;

Where the 1~4 principal_id is a system preset user.

principle:

You can create a user with no login name, most of which is used in the test process, or only for security context simulations (described in chapter three). To create a user, you need to have ALTER any user permissions at the database level, equal to the fixed database role db_accessadmin, which can be implemented with the grant ALTER on user::[username] to [the login name that is required for administration.] Like what:

GRANT ALTER on user::[fred] to [Mary];

On behalf of Mary, this login has the right to administer Fred, the user. But don't give Mary too much permission.

Disable User:

Unlike the logon name disabled, there is no ALTER user XXXX disable command for the user, and there is no such check option in the SSMs. In addition, some system users, such as guest, INFORMATION_SCHEMA, are disabled by default.

If you want to disable a user, you must recycle the Connect permission:

Use marketing; 
REVOKE CONNECT to [Fred];

MORE:

If a Windows account belongs to a Windows group, and the group is added as a login to SQL Server, not only can you create a database user to map to the entire group, or you can create a user map to a separate Windows account. For example, if Domain\fred is a member of the Domain\developers group and the group is already defined as a SQL Server login, but domain\fred logins are not added, you can create the user individually and grant permissions:

CREATE USER [domain\fred] from LOGIN [domain\fred];

who is dbo.

The dbo represents database owner, a special database user that maps the owner of the databases, and when the database is created, the login name is granted to Owner and can be queried with the following statement:

SELECT SUSER_SNAME (OWNER_SID), name from sys.databases; 
--or: 
SELECT suser_sname (SID) from 
sys.database_principals 
WHERE principal_id = user_id (' dbo ');

At this point, the login is automatically mapped to the dbo and all permissions are granted to the entire library, which can be modified with the following statement:

ALTER AUTHORIZATION on database::marketing to SA;

Change owner of database marketing to SA. This practice is useful when moving a database to another server, while the owner's SID does not exist.

The dbo user cannot be renamed or deleted or removed from the db_owner role, and the database owner and members of the sysadmin server role are considered dbo users of the database, logically, as the database owner is mapped to the dbo, and the sysadmin role member does not need to be mapped. In fact, even when the sysadmin member is mapped to a database, it is treated as dbo.

What is the guest user.

In each database, you will see a user named Guest, a database fixed system user that cannot be removed, in order to allow anonymous access to the database for logins that are not mapped to a user. However, the default is disabled in case you do not have to access it. From a security standpoint, it's a good way to keep it disabled, and if you want to enable it, you can grant connect permissions:

Use marketing; 
GRANT CONNECT to Guest;

You can use the following code to check whether the guest user is enabled:

SELECT CAST (IIF (dp.state in (' G ', ' W '), 1, 0) as bit) as [hasdbaccess] from 
sys.database_principals U-left 
JOIN sy S.database_permissions dp on 
    dp.grantee_principal_id = u.principal_id and Dp.type = ' CO ' 
WHERE u.name = ' GUEST ';

Return 1 is enabled,-return 0 is disabled. You can also view the permissions for guest with the following statement:

SELECT Permission_name, State_desc, object_name (major_id) as securable from 
sys.database_permissions 
WHERE grantee_principal_id = user_id (' guest ');

using System functions to identify users and logins

You can use many system functions to get information about users and logins.

System_user and SUSER_SNAME (), returns the current login information and includes the SID. where SUSER_SNAME () returns a SID from the login name or the current login, and if there are no parameters, the impersonation login is returned when the security context changes (EXECUTE as).

Current_User and Session_user return the username for the current context of the current database, user_id () or database_principal_id () returns the principal_id of the database, and SUSER_SID () Returns the SID.

You can use the following statement to list all the database names that you can access:

SELECT [name] from 
sys.databases 
WHERE has_dbaccess ([name]) = 1 and 
  database_id > 4 ORDER by 
[name];

If you need to query whether a user belongs to a group of Windows, you can use the Is_member () function:

SELECT is_member (' domain\developers ');

The following functions can be used to describe both:

The name of the function	Describe
System_user	Returns the current server login name
SUSER_SNAME ()	Returns the current server login name
SUSER_ID ()	Returns the current server Sid
Current_User	Returns the current database user
Session_user	Returns the current database user
USER_ID ()	Returns the current database principal_id
DATABASE_PRINCIPAL_ID ()	Returns the current database principal_id
Original_login ()	Returns the original login name in the context switch money
Has_dbaccess (' database ')	1 access to the database on behalf of the current login
Is_member (' group ')	1 indicates that the current login is a member of the Windows group

Next: http://blog.csdn.net/dba_huangzj/article/details/39003679