Changes the properties of a SQL Server login account.
-->
Click a product!
In the following row, click whichever product name you are interested in. The click displays different content here on this webpage, appropriate for whichever product you click.
SQL ServerSyntaxArguments
login_nameSpecifies the name of the SQL Server login that is being changed. Domain logins must be enclosed in brackets in the format [domainuser].
ENABLE | DISABLEEnables or disables this login. Disabling a login does not affect the behavior of logins that are already connected. (Use the
KILL statement to terminate an existing connections.) Disabled logins retain their permissions and can still be impersonated.
PASSWORD ='password'Applies only to SQL Server logins. Specifies the password for the login that is being changed. Passwords are case-sensitive.
PASSWORD =hashed_passwordApplies to the HASHED keyword only. Specifies the hashed value of the password for the login that is being created.
Important
When a login (or a contained database user) connects and is authenticated, the connection caches identity information about the login. For a Windows Authentication login, this includes information about membership in Windows groups. The identity of the login remains authenticated as long as the connection is maintained. To force changes in the identity, such as a password reset or change in Windows group membership, the login must logoff from the authentication authority (Windows or SQL Server), and log in again. A member of the sysadmin fixed server role or any login with the ALTER ANY CONNECTION permission can use the KILL command to end a connection and force a login to reconnect. SQL Server Management Studio can reuse connection information when opening multiple connections to Object Explorer and Query Editor windows. Close all connections to force reconnection.
HASHEDApplies to SQL Server logins only. Specifies that the password entered after the PASSWORD argument is already hashed. If this option is not selected, the password is hashed before being stored in the database. This option should only be used for login synchronization between two servers. Do not use the HASHED option to routinely change passwords.
OLD_PASSWORD ='oldpassword'Applies only to SQL Server logins. The current password of the login to which a new password will be assigned. Passwords are case-sensitive.
MUST_CHANGEApplies only to SQL Server logins. If this option is included, SQL Server will prompt for an updated password the first time the altered login is used.
DEFAULT_DATABASE =databaseSpecifies a default database to be assigned to the login.
DEFAULT_LANGUAGE =languageSpecifies a default language to be assigned to the login. The default language for all SQL Database logins is English and cannot be changed. The default language of the
sa login on SQL Server on Linux, is English but it can be changed.
NAME = login_nameThe new name of the login that is being renamed. If this is a Windows login, the SID of the Windows principal corresponding to the new name must match the SID associated with the login in SQL Server. The new name of a SQL Server login cannot contain a backslash character ().
CHECK_EXPIRATION = { ON | OFF }Applies only to SQL Server logins. Specifies whether password expiration policy should be enforced on this login. The default value is OFF.
CHECK_POLICY = { ON | OFF }Applies only to SQL Server logins. Specifies that the Windows password policies of the computer on which SQL Server is running should be enforced on this login. The default value is ON.
CREDENTIAL = credential_nameThe name of a credential to be mapped to a SQL Server login. The credential must already exist in the server. For more information, see Credentials. A credential cannot be mapped to the sa login.
NO CREDENTIALRemoves any existing mapping of the login to a server credential. For more information, see Credentials.
UNLOCKApplies only to SQL Server logins. Specifies that a login that is locked out should be unlocked.
ADD CREDENTIALAdds an Extensible Key Management (EKM) provider credential to the login. For more information, see Extensible Key Management (EKM).
DROP CREDENTIALRemoves an Extensible Key Management (EKM) provider credential from the login. For more information, see [Extensible Key Management (EKM)] (./. /relational-databases/security/encryption/extensible-key-management-ekm.md).
Remarks
When CHECK_POLICY is set to ON, the HASHED argument cannot be used.
When CHECK_POLICY is changed to ON, the following behavior occurs:
If MUST_CHANGE is specified, CHECK_EXPIRATION and CHECK_POLICY must be set to ON. Otherwise, the statement will fail.
If CHECK_POLICY is set to OFF, CHECK_EXPIRATION cannot be set to ON. An ALTER LOGIN statement that has this combination of options will fail.
You cannot use ALTER_LOGIN with the DISABLE argument to deny access to a Windows group. For example, ALTER_LOGIN [domaingroup] DISABLE will return the following error message:
In SQL Database, login data required to authenticate a connection and server-level firewall rules are temporarily cached in each database. This cache is periodically refreshed. To force a refresh of the authentication cache and make sure that a database has the latest version of the logins table, execute DBCC FLUSHAUTHCACHE.
Permissions
Requires ALTER ANY LOGIN permission.
If the CREDENTIAL option is used, also requires ALTER ANY CREDENTIAL permission.
If the login that is being changed is a member of the sysadmin fixed server role or a grantee of CONTROL SERVER permission, also requires CONTROL SERVER permission when making the following changes:
A principal can change the password, default language, and default database for its own login.
ExamplesA. Enabling a disabled login
The following example enables the login
Mary5 .
B. Changing the password of a login
The following example changes the password of login
Mary5 to a strong password.
C. Changing the password of a login when logged in as the login
If you are attempting to change the password of the login that you're currently logged in with and you do not have the
ALTER ANY LOGIN permission you must specify the OLD_PASSWORD option.
D. Changing the name of a login
The following example changes the name of login
Mary5 to John2 .
E. Mapping a login to a credential
The following example maps the login
John2 to the credential Custodian04 .
F. Mapping a login to an Extensible Key Management credential
The following example maps the login
Mary5 to the EKM credential EKMProvider1 .
F. Unlocking a login
To unlock a SQL Server login, execute the following statement, replacing **** with the desired account password.
To unlock a login without changing the password, turn the check policy off and then on again.
G. Changing the password of a login using HASHED
The following example changes the password of the
TestUser login to an already hashed value.
See Also
Azure SQL Database single database/elastic poolSQL ServerSyntaxArguments
login_nameSpecifies the name of the SQL Server login that is being changed. Domain logins must be enclosed in brackets in the format [domainuser].
ENABLE | DISABLEEnables or disables this login. Disabling a login does not affect the behavior of logins that are already connected. (Use the
KILL statement to terminate an existing connections.) Disabled logins retain their permissions and can still be impersonated.
PASSWORD ='password'Applies only to SQL Server logins. Specifies the password for the login that is being changed. Passwords are case-sensitive.
Continuously active connections to SQL Database require reauthorization (performed by the Database Engine) at least every 10 hours. The Database Engine attempts reauthorization using the originally submitted password and no user input is required. For performance reasons, when a password is reset in SQL Database, the connection will not be re-authenticated, even if the connection is reset due to connection pooling. This is different from the behavior of on-premises SQL Server. If the password has been changed since the connection was initially authorized, the connection must be terminated and a new connection made using the new password. A user with the KILL DATABASE CONNECTION permission can explicitly terminate a connection to SQL Database by using the KILL command. For more information, see KILL.
Important
When a login (or a contained database user) connects and is authenticated, the connection caches identity information about the login. For a Windows Authentication login, this includes information about membership in Windows groups. The identity of the login remains authenticated as long as the connection is maintained. To force changes in the identity, such as a password reset or change in Windows group membership, the login must logoff from the authentication authority (Windows or SQL Server), and log in again. A member of the sysadmin fixed server role or any login with the ALTER ANY CONNECTION permission can use the KILL command to end a connection and force a login to reconnect. SQL Server Management Studio can reuse connection information when opening multiple connections to Object Explorer and Query Editor windows. Close all connections to force reconnection.
OLD_PASSWORD ='oldpassword'Applies only to SQL Server logins. The current password of the login to which a new password will be assigned. Passwords are case-sensitive.
NAME = login_nameThe new name of the login that is being renamed. If this is a Windows login, the SID of the Windows principal corresponding to the new name must match the SID associated with the login in SQL Server. The new name of a SQL Server login cannot contain a backslash character ().
Remarks
In SQL Database, login data required to authenticate a connection and server-level firewall rules are temporarily cached in each database. This cache is periodically refreshed. To force a refresh of the authentication cache and make sure that a database has the latest version of the logins table, execute DBCC FLUSHAUTHCACHE.
Permissions
Requires ALTER ANY LOGIN permission.
If the login that is being changed is a member of the sysadmin fixed server role or a grantee of CONTROL SERVER permission, also requires CONTROL SERVER permission when making the following changes:
A principal can change the password for its own login.
Examples
These examples also include examples for using other SQL products. Please see which arguments are supported above.
A. Enabling a disabled login
The following example enables the login
Mary5 .
B. Changing the password of a login
The following example changes the password of login
Mary5 to a strong password.
C. Changing the name of a login
The following example changes the name of login
Mary5 to John2 .
D. Mapping a login to a credential
The following example maps the login
John2 to the credential Custodian04 .
E. Mapping a login to an Extensible Key Management credential
The following example maps the login
Mary5 to the EKM credential EKMProvider1 .
Applies to: SQL Server 2008 through SQL Server 2017.
F. Unlocking a login
To unlock a SQL Server login, execute the following statement, replacing **** with the desired account password.
To unlock a login without changing the password, turn the check policy off and then on again.
G. Changing the password of a login using HASHED
The following example changes the password of the
TestUser login to an already hashed value.
Applies to: SQL Server 2008 through SQL Server 2017.
See Also
Azure SQL Database managed instanceSyntax
Important
Azure AD logins for SQL Database managed instance is in public preview.
ArgumentsArguments applicable to SQL and Azure AD logins
login_nameSpecifies the name of the SQL Server login that is being changed. Azure AD logins must be specified as user@domain. For example, [email protected], or as the Azure AD group or application name. For Azure AD logins, the login_name must correspond to an existing Azure AD login created in the master database.
ENABLE | DISABLEEnables or disables this login. Disabling a login does not affect the behavior of logins that are already connected. (Use the
KILL statement to terminate an existing connection.) Disabled logins retain their permissions and can still be impersonated.
DEFAULT_DATABASE =databaseSpecifies a default database to be assigned to the login.
DEFAULT_LANGUAGE =languageSpecifies a default language to be assigned to the login. The default language for all SQL Database logins is English and cannot be changed. The default language of the
sa login on SQL Server on Linux, is English but it can be changed.
Arguments applicable only to SQL logins
PASSWORD ='password'Applies only to SQL Server logins. Specifies the password for the login that is being changed. Passwords are case-sensitive. Passwords also do not apply when used with external logins, like Azure AD logins.
Continuously active connections to SQL Database require reauthorization (performed by the Database Engine) at least every 10 hours. The Database Engine attempts reauthorization using the originally submitted password and no user input is required. For performance reasons, when a password is reset in SQL Database, the connection will not be re-authenticated, even if the connection is reset due to connection pooling. This is different from the behavior of on-premises SQL Server. If the password has been changed since the connection was initially authorized, the connection must be terminated and a new connection made using the new password. A user with the KILL DATABASE CONNECTION permission can explicitly terminate a connection to SQL Database by using the KILL command. For more information, see KILL.
PASSWORD =hashed_passwordApplies to the HASHED keyword only. Specifies the hashed value of the password for the login that is being created.
HASHEDApplies to SQL Server logins only. Specifies that the password entered after the PASSWORD argument is already hashed. If this option is not selected, the password is hashed before being stored in the database. This option should only be used for login synchronization between two servers. Do not use the HASHED option to routinely change passwords.
OLD_PASSWORD ='oldpassword'Applies only to SQL Server logins. The current password of the login to which a new password will be assigned. Passwords are case-sensitive.
MUST_CHANGE
Applies only to SQL Server logins. If this option is included, SQL Server will prompt for an updated password the first time the altered login is used.
NAME = login_nameThe new name of the login that is being renamed. If the login is a Windows login, the SID of the Windows principal corresponding to the new name must match the SID associated with the login in SQL Server. The new name of a SQL Server login cannot contain a backslash character ().
CHECK_EXPIRATION = { ON | OFF }Applies only to SQL Server logins. Specifies whether password expiration policy should be enforced on this login. The default value is OFF.
CHECK_POLICY = { ON | OFF }Applies only to SQL Server logins. Specifies that the Windows password policies of the computer on which SQL Server is running should be enforced on this login. The default value is ON.
CREDENTIAL = credential_nameThe name of a credential to be mapped to a SQL Server login. The credential must already exist in the server. For more information, see Credentials. A credential cannot be mapped to the sa login.
NO CREDENTIALRemoves any existing mapping of the login to a server credential. For more information, see Credentials.
UNLOCKApplies only to SQL Server logins. Specifies that a login that is locked out should be unlocked.
ADD CREDENTIALAdds an Extensible Key Management (EKM) provider credential to the login. For more information, see Extensible Key Management (EKM).
DROP CREDENTIALRemoves an Extensible Key Management (EKM) provider credential from the login. For more information, see Extensible Key Management (EKM).
Remarks
When CHECK_POLICY is set to ON, the HASHED argument cannot be used.
When CHECK_POLICY is changed to ON, the following behavior occurs:
If MUST_CHANGE is specified, CHECK_EXPIRATION and CHECK_POLICY must be set to ON. Otherwise, the statement will fail.
If CHECK_POLICY is set to OFF, CHECK_EXPIRATION cannot be set to ON. An ALTER LOGIN statement that has this combination of options will fail.
You cannot use ALTER_LOGIN with the DISABLE argument to deny access to a Windows group. This is by design. For example, ALTER_LOGIN [domaingroup] DISABLE will return the following error message:
In SQL Database, login data required to authenticate a connection and server-level firewall rules are temporarily cached in each database. This cache is periodically refreshed. To force a refresh of the authentication cache and make sure that a database has the latest version of the logins table, execute DBCC FLUSHAUTHCACHE.
Permissions
Requires ALTER ANY LOGIN permission.
If the CREDENTIAL option is used, also requires ALTER ANY CREDENTIAL permission.
If the login that is being changed is a member of the sysadmin fixed server role or a grantee of CONTROL SERVER permission, also requires CONTROL SERVER permission when making the following changes:
A principal can change the password, default language, and default database for its own login.
Only a SQL principal with
sysadmin privileges can execute an ALTER LOGIN command against an Azure AD login.
Examples
These examples also include examples for using other SQL products. Please see which arguments are supported above.
A. Enabling a disabled login
The following example enables the login
Mary5 .
B. Changing the password of a login
The following example changes the password of login
Mary5 to a strong password.
C. Changing the name of a login
The following example changes the name of login
Mary5 to John2 .
D. Mapping a login to a credential
The following example maps the login
John2 to the credential Custodian04 .
E. Mapping a login to an Extensible Key Management credential
The following example maps the login
Mary5 to the EKM credential EKMProvider1 .
Applies to: SQL Server 2008 through SQL Server 2017, and Azure SQL Database managed instance.
F. Unlocking a login
To unlock a SQL Server login, execute the following statement, replacing **** with the desired account password.
To unlock a login without changing the password, turn the check policy off and then on again.
G. Changing the password of a login using HASHED
The following example changes the password of the
TestUser login to an already hashed value.
Applies to: SQL Server 2008 through SQL Server 2017, and Azure SQL Database managed instance.
H. Disabling the login of an Azure AD user
The following example disables the login of an Azure AD user, [email protected].
See Also
Azure SQL Data WarehouseSyntaxArguments
login_nameSpecifies the name of the SQL Server login that is being changed. Domain logins must be enclosed in brackets in the format [domainuser].
ENABLE | DISABLEEnables or disables this login. Disabling a login does not affect the behavior of logins that are already connected. (Use the
KILL statement to terminate an existing connections.) Disabled logins retain their permissions and can still be impersonated.
PASSWORD ='password'Applies only to SQL Server logins. Specifies the password for the login that is being changed. Passwords are case-sensitive.
Continuously active connections to SQL Database require reauthorization (performed by the Database Engine) at least every 10 hours. The Database Engine attempts reauthorization using the originally submitted password and no user input is required. For performance reasons, when a password is reset in SQL Database, the connection will not be re-authenticated, even if the connection is reset due to connection pooling. This is different from the behavior of on-premises SQL Server. If the password has been changed since the connection was initially authorized, the connection must be terminated and a new connection made using the new password. A user with the KILL DATABASE CONNECTION permission can explicitly terminate a connection to SQL Database by using the KILL command. For more information, see KILL.
Important
When a login (or a contained database user) connects and is authenticated, the connection caches identity information about the login. For a Windows Authentication login, this includes information about membership in Windows groups. The identity of the login remains authenticated as long as the connection is maintained. To force changes in the identity, such as a password reset or change in Windows group membership, the login must logoff from the authentication authority (Windows or SQL Server), and log in again. A member of the sysadmin fixed server role or any login with the ALTER ANY CONNECTION permission can use the KILL command to end a connection and force a login to reconnect. SQL Server Management Studio can reuse connection information when opening multiple connections to Object Explorer and Query Editor windows. Close all connections to force reconnection.
OLD_PASSWORD ='oldpassword'Applies only to SQL Server logins. The current password of the login to which a new password will be assigned. Passwords are case-sensitive.
NAME = login_nameThe new name of the login that is being renamed. If this is a Windows login, the SID of the Windows principal corresponding to the new name must match the SID associated with the login in SQL Server. The new name of a SQL Server login cannot contain a backslash character ().
Remarks
In SQL Database, login data required to authenticate a connection and server-level firewall rules are temporarily cached in each database. This cache is periodically refreshed. To force a refresh of the authentication cache and make sure that a database has the latest version of the logins table, execute DBCC FLUSHAUTHCACHE.
Permissions
Requires ALTER ANY LOGIN permission.
If the login that is being changed is a member of the sysadmin fixed server role or a grantee of CONTROL SERVER permission, also requires CONTROL SERVER permission when making the following changes:
A principal can change the password for its own login.
Examples
These examples also include examples for using other SQL products. Please see which arguments are supported above.
A. Enabling a disabled login
The following example enables the login
Mary5 .
B. Changing the password of a login
The following example changes the password of login
Mary5 to a strong password.
C. Changing the name of a login
The following example changes the name of login
Mary5 to John2 .
D. Mapping a login to a credential
How to disable update orchestrator. The following example maps the login
John2 to the credential Custodian04 .
E. Mapping a login to an Extensible Key Management credentialSql Server Rename Login Page
The following example maps the login
Mary5 to the EKM credential EKMProvider1 .
Applies to: SQL Server 2008 through SQL Server 2017.
F. Unlocking a login
To unlock a SQL Server login, execute the following statement, replacing **** with the desired account password.
To unlock a login without changing the password, turn the check policy off and then on again.
G. Changing the password of a login using HASHED
The following example changes the password of the
TestUser login to an already hashed value.
Applies to: SQL Server 2008 through SQL Server 2017.
See Also
Analytics Platform SystemSyntaxArguments
login_nameSpecifies the name of the SQL Server login that is being changed. Domain logins must be enclosed in brackets in the format [domainuser].
ENABLE | DISABLEEnables or disables this login. Disabling a login does not affect the behavior of logins that are already connected. (Use the
KILL statement to terminate an existing connection.) Disabled logins retain their permissions and can still be impersonated.
PASSWORD ='password'Applies only to SQL Server logins. Specifies the password for the login that is being changed. Passwords are case-sensitive.
Important
When a login (or a contained database user) connects and is authenticated, the connection caches identity information about the login. For a Windows Authentication login, this includes information about membership in Windows groups. The identity of the login remains authenticated as long as the connection is maintained. To force changes in the identity, such as a password reset or change in Windows group membership, the login must logoff from the authentication authority (Windows or SQL Server), and log in again. A member of the sysadmin fixed server role or any login with the ALTER ANY CONNECTION permission can use the KILL command to end a connection and force a login to reconnect. SQL Server Management Studio can reuse connection information when opening multiple connections to Object Explorer and Query Editor windows. Close all connections to force reconnection.
OLD_PASSWORD ='oldpassword'Applies only to SQL Server logins. The current password of the login to which a new password will be assigned. Passwords are case-sensitive.
MUST_CHANGEApplies only to SQL Server logins. If this option is included, SQL Server will prompt for an updated password the first time the altered login is used.
NAME = login_nameThe new name of the login that is being renamed. If the login is a Windows login, the SID of the Windows principal corresponding to the new name must match the SID associated with the login in SQL Server. The new name of a SQL Server login cannot contain a backslash character ().
CHECK_EXPIRATION = { ON | OFF }Applies only to SQL Server logins. Specifies whether password expiration policy should be enforced on this login. The default value is OFF.
CHECK_POLICY = { ON | OFF }Applies only to SQL Server logins. Specifies that the Windows password policies of the computer on which SQL Server is running should be enforced on this login. The default value is ON.
UNLOCKApplies only to SQL Server logins. Specifies that a login that is locked out should be unlocked.
Remarks
When CHECK_POLICY is set to ON, the HASHED argument cannot be used.
When CHECK_POLICY is changed to ON, the following behavior occurs:
If MUST_CHANGE is specified, CHECK_EXPIRATION and CHECK_POLICY must be set to ON. Otherwise, the statement will fail.
If CHECK_POLICY is set to OFF, CHECK_EXPIRATION cannot be set to ON. An ALTER LOGIN statement that has this combination of options will fail.
You cannot use ALTER_LOGIN with the DISABLE argument to deny access to a Windows group. This is by design. For example, ALTER_LOGIN [domaingroup] DISABLE will return the following error message:
In SQL Database, login data required to authenticate a connection and server-level firewall rules are temporarily cached in each database. This cache is periodically refreshed. To force a refresh of the authentication cache and make sure that a database has the latest version of the logins table, execute DBCC FLUSHAUTHCACHE.
Permissions
Requires ALTER ANY LOGIN permission.
If the CREDENTIAL option is used, also requires ALTER ANY CREDENTIAL permission.
If the login that is being changed is a member of the sysadmin fixed server role or a grantee of CONTROL SERVER permission, also requires CONTROL SERVER permission when making the following changes:
A principal can change the password, default language, and default database for its own login.
Examples
These examples also include examples for using other SQL products. Please see which arguments are supported above.
A. Enabling a disabled login
The following example enables the login
Mary5 .
B. Changing the password of a login
The following example changes the password of login
Mary5 to a strong password.
C. Changing the name of a login
The following example changes the name of login
Mary5 to John2 .
D. Mapping a login to a credential
The following example maps the login
John2 to the credential Custodian04 .
E. Mapping a login to an Extensible Key Management credential
The following example maps the login
Mary5 to the EKM credential EKMProvider1 .
Applies to: SQL Server 2008 through SQL Server 2017.
F. Unlocking a login
To unlock a SQL Server login, execute the following statement, replacing **** with the desired account password.
To unlock a login without changing the password, turn the check policy off and then on again.
G. Changing the password of a login using HASHED
The following example changes the password of the
TestUser login to an already hashed value.
Applies to: SQL Server 2008 through SQL Server 2017.
See Also
I have a database with user 'dbo' that has a login name 'domainxzy'. How do I change it from 'domainxzy' to 'domainabc'.
Jason Kanaris
Jason KanarisJason Kanaris
2,13566 gold badges2828 silver badges3535 bronze badges
4 Answers
I figured it out. Within SQL Management Studio you have to right-click on the database -> Properties -> Files -> Owner field. Change this field to the login name/account that you want associated with the 'dbo' username for that database. Please keep in mind that the login name/account you choose must already be setup in the sql server under Security -> Logins
Jason KanarisJason Kanaris
2,13566 gold badges2828 silver badges3535 bronze badges
If you are trying to remap a login to a db user you can use sp_change_user_login
exec sp_change_user_login 'Update_One', 'user', 'login'
ThadThad
1,00022 gold badges1414 silver badges3535 bronze badges
PantomTypist gives a good answer using the GUI. For achieving the same result with TSQL, you can use this code:
MikeMike
This is a Windows login, not a SQL Server login, so you cannot 'change' the login name since it is linked to the user account in Active Directory.
Create a new Server Login (Windows) mapped to the new windows user (and remove the old one if necessary). Then in login's Security > User Mapping, permission that login to the appropriate database as user 'dbo' (or assign to the db_owner role)
user21576user21576
Not the answer you're looking for? Browse other questions tagged sql-serversql-server-2005securityactive-directory or ask your own question.
Example:
Lets say I have a group named
group_01 , the group is mapped to a SQL Server an given some rights on some stuff.
When I rename the group in Active Directory to any value, lets say
group_01_OLD .The group name wont change in SQL Server, it's still group_01
Is this normal behavior? Can I force SQL to rename the group when renamed in AD?
SmeerpijpSmeerpijp
17811 gold badge22 silver badges1414 bronze badges
2 Answers
An full answer is buried in the comments here: https://dba.stackexchange.com/questions/13766/user-windows-login-name-has-been-changed-in-ad-yet-session-in-sql-2008-profiler
Basically, a reboot of the whole server should pick up the change (assuming replication to all the DCs has already happened).
If you can't do that, you could try manually updating the name of the login:
Community♦
Gabriel LuciGabriel Luci
12.1k11 gold badge1616 silver badges2727 bronze badges
To complement Gabriel’s answer. Given your scenario (you have granted permissions to the group
group_01 ), you must change the name in SQL using the ALTER LOGIN command
The reason for this is that SQL Server looks for a matching login catalog views (i.e.
sys.server_principals ) within SQL itself before asking AD. NOTE: When you rename a Windows login, SQL Server will verify that the new name matches the SID to verify that the login renaming is valid.
-Raul Garcia
Raul GRaul G
Not the answer you're looking for? Browse other questions tagged sql-serveractive-directory or ask your own question.
I have recently been running into many different areas of SQL Server that I normally don't mess with. One of them that has me confused is the area of Logins and Users. Seems like it should be a pretty simple topic..
It appears that each login can only have 1 user and each user can only have 1 login.
A login can be associated to multiple tables thus associating that user to many tables.
So my question is why even have a login and a user? they seem to be pretty much one in the same. What are the differences, or what is it that I seem to be missing?
Martin Smith
358k6161 gold badges601601 silver badges710710 bronze badges
corymathewscorymathews
7,4611212 gold badges5151 silver badges7171 bronze badges
5 Answers
A 'Login' grants the principal entry into the SERVER.
A 'User' grants a login entry into a single DATABASE.
One 'Login' can be associated with many users (one per database).
Each of the above objects can have permissions granted to it at its own level. See the following articles for an explanation of each
AminM
1,13922 gold badges2323 silver badges3838 bronze badges
Scott IveyScott Ivey
32.9k1717 gold badges7272 silver badges113113 bronze badges
One reason to have both is so that authentication can be done by the database server, but authorization can be scoped to the database. That way, if you move your database to another server, you can always remap the user-login relationship on the database server, but your database doesn't have to change.
Tom ResingTom Resing
1,74711 gold badge1616 silver badges2222 bronze badges
I think there is a really good MSDN blog post about this topic by Laurentiu Cristofor:
The first important thing that needs to be understood about SQL Server security is that there are two security realms involved - the server and the database. The server realm encompasses multiple database realms. All work is done in the context of some database, but to get to do the work, one needs to first have access to the server and then to have access to the database.
Access to the server is granted via logins. There are two main categories of logins: SQL Server authenticated logins and Windows authenticated logins. I will usually refer to these using the shorter names of SQL logins and Windows logins. Windows authenticated logins can either be logins mapped to Windows users or logins mapped to Windows groups. So, to be able to connect to the server, one must have access via one of these types or logins - logins provide access to the server realm.
But logins are not enough, because work is usually done in a database and databases are separate realms. Access to databases is granted via users.
Users are mapped to logins and the mapping is expressed by the SID property of logins and users. A login maps to a user in a database if their SID values are identical. Depending on the type of login, we can therefore have a categorization of users that mimics the above categorization for logins; so, we have SQL users and Windows users and the latter category consists of users mapped to Windows user logins and of users mapped to Windows group logins.
Let's take a step back for a quick overview: a login provides accessto the server and to further get access to a database, a user mappedto the login must exist in the database.
that's the link to the full post.
David LeitnerDavid Leitner
In Short,
Logins will have the access of the server.
and
Users will have the access of the database.
Rajesh
6,7661414 gold badges3434 silver badges5454 bronze badges
Vikrant KedariVikrant Kedari
I think this is a very useful question with good answer. Just to add my two cents from the MSDN Create a Login page:
A login is a security principal, or an entity that can be authenticated by a secure system. Users need a login to connect to SQL Server. You can create a login based on a Windows principal (such as a domain user or a Windows domain group) or you can create a login that is not based on a Windows principal (such as an SQL Server login).
Note:
To use SQL Server Authentication, the Database Engine must use mixed mode authentication. For more information, see Choose an Authentication Mode.
As a security principal, permissions can be granted to logins. The scope of a login is the whole Database Engine. To connect to a specific database on the instance of SQL Server, a login must be mapped to a database user. Permissions inside the database are granted and denied to the database user, not the login. Permissions that have the scope of the whole instance of SQL Server (for example, the CREATE ENDPOINT permission) can be granted to a login.
ilmatteilmatte
Not the answer you're looking for? Browse other questions tagged sqlsql-serversql-server-2005 or ask your own question.
My question is very similar to this question Changing Windows Domain UserName but the original question doesn't explicitly answer my slight variation.
If I change a windows users username (if they get married for instance) will my Sql Server login which I created for the old username be updated to reflect the change or will I have to create a new login for the updated windows user?
ThanksBen
Community♦
BenCrBenCr
4 Answers
If the login is tied to the domain account, it should be fine because at the basic level it depends on the SID of the user and not the actual username.
If the new username doesn't work, you may have to reset the token cache with this command:
HyppyHyppy
14.5k11 gold badge3030 silver badges5757 bronze badges
The permissions should continue to work properly, but the username displayed in SQL Server will be the old name in some places until you manually update it.
While the drop/create method will work, and apparently creating a duplicate sql login with same SID appears to work for some here, I recommend just updating the existing login record in SQL Server (tested in SQL 2012).
This will cleanly change the existing system login record to reflect the correct new username for that domain user (SID).
Mister_TomMister_Tom
It actually matches on SID, not name. So you'll be OK.
Notes:
5,76911 gold badge1414 silver badges2121 bronze badges
You can easily check they have the same SID after rename by using the below script and check if you get the same SID before and after. Assuming you renamed
DomainLoginA to DomainLoginB :
and
should give you same SID which proves @Hyppy's point.
DaniSQLDaniSQL
Not the answer you're looking for? Browse other questions tagged sql-serveruser-managementwindows-authentication or ask your own question.Comments are closed.
|
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |