MS SQL Basics Tutorial: Managing SQL Server Logins

14.2.1 SQL Server logins using SQL Enterprise Manager

In SQL Server, perform the following steps through SQL Server Enterprise Manager to manage SQL Server logins:

(1) Start SQL Server Enterprise Manager and click the ' + ' flag next to the login server.

(2) Click the ' + ' flag next to the Security folder.

(3) Right-click the logins icon and select the New Login option from the pop-up menu, SQL Server will eject the SQL Server properties–new Login dialog box, as shown in Figure 14-2.

(4) Enter the login name in the name Single-line edit box, as shown in Figure 14-2.

(5) Select the authentication mode in the options bar under authentication if you are using SQL Server authentication mode, then you must enter a password in password after you select the SQL Server Authentication radio button. If you are using NT Authentication mode, then after you select the Windows NT Authentication radio button, you must enter a domain name in domain.

(6) In the two options box under Default, the default database and the default language for the user at logon are indicated.

(7) Click OK button to create login.

Note: If Windows Authentication mode is selected, the account entered at the name option must be a login or group already established in NT, in the form of the NT network name user name or the NT host name User name.

If the Windwos authentication mode is selected and the NT network is used, enter the domain where the login account or group belongs in domain; If you do not use an NT network, enter the NT host that the login account belongs to in domain.

If Windows Authentication mode is selected and the login account is a built-in user group in NT, such as administators, you must enter "Builting" instead of an NT hostname or NT network domain in domain.

14.2.2 use transact_sql to manage SQL Server logins

In SQL Server, some system procedures provide the ability to manage SQL Server logins, mainly including:

Sp_granlogin sp_revokelogin sp_denylogin

sp_addlogin sp_droplogin Sp_helplogins

The following is a description of how these system procedures manage logins.

(1) sp_addlogin

Create a new login account with SQL Server authentication mode, with a syntax format of;

Note: SQL Server has a maximum logon name and password length of 128 characters, which can be English letters, characters, and numbers. However, the following three types of cases are considered invalid.

Note: You cannot delete the System Manager SA and the logins that are currently connected to SQL Server.

If the user that matches the login still exists in the Database sysusers table, the login account cannot be deleted.

Sp_addlogin and sp_droplogin can only be used in SQL Server authentication mode.

Note:Sp_granlogin and sp_revokelogin are only allowed to set the NT user or user group account in NT Authentication mode, but not the login account maintained by SQL Server.

(6) Sp_helplogins

Note: These commands are available to members of the sysadmin and securityadmin server roles in the system procedures described above.

If you use the NT Authentication mode, if you have set an NT user or user group as a login for SQL Server, then if you remove the NT user or user group from the NT domain, the corresponding SQL Server login will be orphaned. and its SQL Server logon information is still stored in the system table syslogins.

A soliton login is a login that exists in the SQL Server system table but is rejected by SQL Server, even if an attempt to create a login with the same name would fail, because it already exists in the system table syslogins.

Even if the orphan login is deleted, and with the same NT user or group of users set to SQL Server connection, although you can successfully log on to SQL Server, but you have the right to the previous login of the permissions, unless the permissions of the reconfiguration, because the logon information includes security identifiers (SID), The deletion is not the same as the SID that was added before it was deleted.

See the full set of "MS SQL Basics Tutorials"