Analysis on the Relationship between user names and login names in SQL Server

In the past, I was often confused by the user name and login name in SQL Server, because I used the sa Login Name to fix everything. Of course, this would pose some security risks. The online articles seem to be very good, but still do not understand. I am determined to clarify this issue today. Mashallah, after reading more than N Articles and adding experiments, I finally figured out that the most important thing to learn is to try it myself.) I will share what I learned to see if it is helpful to you.

Login Name: An entity of the server. A login name can only enter the server, but cannot allow users to access database resources on the server. The definitions of each login name are stored in the syslogins table of the master database.

User name: ing of one or more login objects in the database. You can authorize the user object to grant the login object access permissions to the database. User-Defined information is stored in the sysusers table of each database.

SQLSERVER maps the relationship between login names and user names. After you log on to SQLSERVER with the login name, SQLSERVER will automatically query whether the user name associated with this login name exists in the Database when accessing each database. If so, you can use this user's permission to access this database, if the user does not exist, it is a special user name to access the database guest using the guest user, which will be discussed later ).

One login name can be authorized to access multiple databases, but one login name can only be mapped once in each database. That is, one logon can correspond to multiple users, and one user can also be used by multiple logon users. For example, SQLSERVER is like a building where every room is a database. the login name is only the key to enter the building, while the user name is the key to enter the room. A login name can have keys for multiple rooms, but a login name can only have one key for this room in one room.

When you connect to or log on to the SQL Server, you use the login name instead of the user name to log on. the user name in the Link string in the program also refers to the login name.

SQL SERVER has several special login names and user names:

Our common dbo username refers to the user logging on with sa (login name) or windows administration (Windows integrated authentication login method). That is to say, the username of the database administrator in SQLSERVER is called dbo, rather than sa, this seems a bit strange, because the user name is usually the same as the login name (not forced to be the same, but in order to be clear, the user name is usually used with the same name as the login name ), for example, if you have created a login name called me, you can add a user with the same name to the specified database for the login name me to access the data in the database. after a user me is added to the database, the owner of all objects (tables, functions, stored procedures, and so on) created in the database when I log on with the me login name is me, such as me. table1, me. fn_test () instead of dbo. table1, dbo. fn_test ().

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

In the SQL Server database, the guest account is a special user account. If no account is associated with the user in the database accessed using the USE database statement, the user is associated with the guest user.

In addition, SQLSERVER adopts the logon name-User Name security rules, which is similar to the schema in Oracle. SQLSERVER uses the owner to restrict objects similar to the schema in Oracle), because different users may create objects with the same name, such as the login name me and login name you have created the username me and you in the pubs database respectively, both users have created the same table named testtable. Although the two tables have the same name, their structures or data may be completely different. To avoid call errors, you must use the owner name to limit them.

How can we call objects created by other users?

For example, if a me user accesses a table created by you or a table created by dbo, the following conditions must be met:

1. set the me user's database role to db_owner. Otherwise, you cannot access the objects created by other users (including dbo users. (Enterprise Manager-> User, right-click the menu and choose <Properties> set ):

2. Use the owner for limitation.

For example, me accesses the testtable created by you:

 
 

  
  
select   *   from   you.testtable  
 
 

In addition, the dbo user, as the administrator, can call any object created by the user with all the permissions granted by the system.

If a database has two or more 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, if not, check whether the dbo user has an object with the same name. for example:
 

 
 

  
  
Select * from testtable or
  
  
Select * from pubs .. testtable
 
 

Lab:

Create the login name login_ibrahim

Log on to the "query analyzer" using this login name and find that you can only view the database that comes with the system, such as master, Northwind, And pubs.

Set the server role of the login name login_ibrahim to Database Creator. If this item is not set, the Database cannot be created)

Enter create database test in "query analyzer" and execute it. This will create a database named test.

View the User Name of the test database. The system automatically creates the dbo and guest user names. The logon name corresponding to the dbo username is login_ibrahim. You cannot change the dbo username corresponding to the login_ibrahim username of the test database. In this case, the dbo username of the test database has two logon names: login_ibrahim, the other is sa (I didn't expect ^ _ ^, because members of the sysadmin fixed server role will be automatically mapped to dbo .)

Create the login name login_ibrahim2. If no server role is set, set the accessible database to test.

Open the user name list of the test database and you will find that the system will automatically create a login_ibrahim2 username with the same name for the login name login_ibrahim2, and then create a data table named T_ibrahim

Log on to SQLSERVER with login_ibrahim2 and find that the table cannot be created in the test database.

Log on to SQLSERVER with login_ibrahim and grant the db_ddladmin permission on the test data to the login_ibrahim2 user name.

Log on to SQLSERVER with login_ibrahim2 and find that you can create a data table named T_ibrahim and insert the data.

Original article title: summary of the relationship between the SQL Server user name and the login name

Link: http://www.cnblogs.com/ibrahim/archive/2010/06/21/sqlserver_loginname_username.html