SQL Server user name and login name

Summary of the relationship between the SQL Server user name and the login name

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 allows you to authorize user objects so that the login objects can access the database. User-Defined information is stored in the sysusers table of each database.

SQL Server maps the relationship between login names and user names. After you log on to SQL Server with a login name, when you access each database, SQL Server automatically queries whether there are usernames associated with this login name in this database. If so, you can use this user's permissions to access this database, if the user does not exist, access the database using the Guest user (guest is a special user name, 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 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.

Sqlserver has several special login names and user names:

Our common DBO (User Name) refers to the user logging on with SA (login name) or Windows Administration (Windows integrated authentication login method). That is to say, the user name 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 sqlserver 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 limit (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 user names 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 option 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 (create a data table named t_ibrahim) and insert data.