Chapter 2 user authentication, Authorization, and Security (8): Create a database user mapped to a login

Original source: http://blog.csdn.net/dba_huangzj/article/details/38944121, featured catalogue:http://blog.csdn.net/dba_huangzj/article/details/37906349

No person shall, without the consent of the author, be published in the form of "original" or used for commercial purposes, and I am not responsible for any legal liability.

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

Objective:

Logins are used to authorize and access server resources, and if you need to access the database, you need to map users inside 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.

Realize:

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

1. In SSMs, select the Databases 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 logins)	The user mapped to a SQL login.
SQL user without login (SQL users without logins)	Users who do not map to any server-level logins can use the EXECUTE as user command to simulate execution.
User mapped to a certificate/user mapped to an asymmetric key (users mapped to certificate/asymmetric key)	A user created from a signing key that allows mapping from a remote entity.
Windows user (Windows users)	The user that is mapped to the logon name for Windows authentication.

3. Enter a user name that can be the same as the login name, or use another allowed name, but you cannot use some keywords, reserved words, and so on. 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 implement the following T-sql:

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

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

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;

One of the 1~4 principal_id is the system preset user.

Principle:

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

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

This login on behalf of Mary has the right to administer Fred as the user. But don't give Mary too much permission.

To disable a user:

And the login name is disabled, for the user, there is no ALTER user XXXX disable command, in SSMS can also see no such tick option. In addition, some system users, such as guest, INFORMATION_SCHEMA, are disabled by default.

If you want to disable users, you must reclaim their connect permissions:

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, you can create a database user to map to the entire group, or you can create a user mapping 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 the domain\fred login is not added, you can create the user individually and grant permissions:

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

Who is the dbo?

The DBO represents the database owner, a special user who maps the database owner, and when the database is created, the login name used is granted to owner and can be queried using 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 for the entire library are granted, and can be modified with the following statement:

ALTER AUTHORIZATION on database::marketing to SA;

Change the owner of the database marketing to SA. This practice is useful when moving a database to a different server, and 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 treated as dbo users of the database, logically, as the database owner is mapped to the dbo, and the sysadmin role members do not need mappings. In fact, even when the sysadmin member is mapped to a database user, it is considered dbo.

What is a guest user?

In each database, you will see a user named Guest, which is a database fixed system user that cannot be removed, to allow anonymous access to the database without mapping to a user's login name. 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 Sys.databa Se_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 use the following statement to view the permissions of the guest:

SELECT Permission_name, State_desc, object_name (major_id) as securable from Sys.database_permissions WHERE grantee_ principal_id = user_id (' guest ');

Identify users and logins using System functions

You can use a number of system functions to obtain user and login information.

System_user and SUSER_SNAME (), returns the current logon information, and contains the SID. where SUSER_SNAME () returns a SID from the login 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 user name of 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 member of Windows, you can use the Is_member () function:

SELECT is_member (' domain\developers ');

Here are the functions you can use to describe both:

Name of 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 represents the current login to access the database
Is_member (' group ')	1 represents the current logon is a member of a Windows group

Next article:

Chapter 2 user authentication, Authorization, and Security (8): Create a database user mapped to a login