Summary of relationship between SQL Server user name and login name

Used to be confused by the user name and login name in SQL Server, because the SA (login name) to take care of everything, of course, there are some security risks. The article on the Internet seems to speak very well, but still do not understand. Today is determined to make the problem clear. Mashallah, read n more articles and experiment finally figured out (learn the most important thing or to try it yourself), now will learn to share, see if it is helpful to you.

Login Name: A server-side entity that uses a single login to enter the server only, but does not allow users to access database resources on the server. The definition of each login is stored in the syslogins table in the master database

User name: The mapping of one or more login objects in the database, which allows user objects to be authorized to provide access to the database for the logon object. User-defined information is stored in the sysusers table for each database.

SQL Server calls the relationship between the login name and the user name as a mapping. After logging in to SQL Server with a login, SQL Server automatically queries the database for the presence of the user name associated with this login, and if there is access to the database using this user's permissions, If it does not exist, use the Guest user to access the database (guest is a special user name, which is described later).

A login can be authorized to access multiple databases, but a login can only be mapped once per database. That is, one login can correspond to multiple users, and one user can be used by multiple logins. Like SQL Server is a building, every room in it is a database. The login name is the key to the building, and the user name is the key to the room. A login can have multiple room keys, but a login name in a room can only have a key for this room.

link or log on to SQL Server The server is logged in with the login name instead of the user name, and the user name in the link string inside the program also refers to the login name.

There are several special logins and usernames in SQL Server:

Our common dbo (user name) is the user logged in as SA (login) or Windows Administration (Windows Integrated authentication login), which means that the database administrator's user name in SQL Server is called dbo, not SA, This seems a bit odd, because usually the user name is the same as the login name (not mandatory, but for the sake of a glance usually when the user name is created with the same name as the login name), for example, create a login name for me, you can add a user with the same name in the specified database for the login me. Enables the login me to access data in the database. When a user me is added to the database, all objects (tables, functions, stored procedures, etc.) created in the database after the login with the Me login are me, such as me.table1,me.fn_test (), Rather than dbo.table1,dbo.fn_test ().

There is also a special database role public in SQL Server, which exists in every database, including system databases such as master, msdb, model, and user databases, and all users of the database belong to the public role and cannot be removed from the public role.

In SQL Server database, the Guest account is a special user account. If the user accesses the database using the USE DATABASE statement, the user is associated with the guest user without an account associated with the user.

In addition, SQL Server takes login-user name security rules, and Oracle inside the schema a bit like. SQL Server uses the owner to qualify (similar to schema in Oracle) because different users may create objects of the same name, such as login me and login you have created the user name me and you in the pubs database, These two users have created a table with the same name TestTable, and these two tables, although the same name but the structure or data may be completely different, in order to avoid calling the error, you must use the owner name to qualify.

How do I invoke an object created by another user? For example, the ME user accesses a table created by a user or accesses a table created by the dbo. In this case, two conditions must be met:

1. Set the database role of the me user to db_owner, or you will not be able to access objects created by other users, including the dbo user. (Enterprise Manager, user, right-click menu < properties > Settings):

2. Use the owner to qualify.
For example me to access the testtable you created:
SELECT * FROM You.testtable
In addition, the DBO user, as an administrator, gives all of its permissions and can invoke any user-created object.
If a database exists with 2 or more than 2 user names, if a user with the db_owner role omits the owner when accessing the object, the system first looks for the user's object and, if it cannot find it, whether the dbo user has the same name. For example:
SELECT * FROM TestTable or
SELECT * FROM pubs. TestTable

Experiment:

L Create Login name Login_ibrahim

Log in to Query Analyzer with this login, and discover that you can only view the database that comes with your system, such as Master, Northwind, pubs, and more

L Set the server role of login Login_ibrahim to database Creator (if you do not set this key, you cannot create databases)

L Enter the CREATE database test in Query Analyzer, and then execute, which will create a DB named Test

L View the user name that is owned by the test database and discover that the system automatically creates the two usernames of dbo and guest. Where the dbo user name corresponds to the login name Login_ibrahim, you cannot change the user name of the test database login Login_ibrahim dbo, at this time the dbo user name of the test database, its corresponding login name is two, one is Login_ Ibrahim, the other is SA (unexpectedly ^_^, because members of the sysadmin fixed server role are automatically mapped to dbo. )

L Create the login name login_ibrahim2, do not set any server roles, set the accessible database to test

Opening the list of user names for the test database, you will find that the system automatically creates a username login_ibrahim2 for the login name login_ibrahim2, and then creates a datasheet named T_ibrahim

L logged into SQL Server with LOGIN_IBRAHIM2 and found that the table could not be created in the test database

• Log on SQL Server with Login_ibrahim to grant db_ddladmin permissions to the test data for the LOGIN_IBRAHIM2 user name

l log in to SQL Server with LOGIN_IBRAHIM2 and find that you can create a data table (creating a data table named T_ibrahim) and insert the data

Summary of relationship between SQL Server user name and login name