How To Create a New Login Name in SQL Server?

To create a new login name, you can use the "CREATE LOGIN" statement in a simple syntax like this:

CREATE LOGIN login_name WITH PASSWORD = 'password'  

How To List All Login Names on the Server?

If you want to see a list of all login names defined on the server, you can use the system view, sys.server_principals as shown in this tutorial exercise:

-- Login with sa    SELECT name, sid, type, type_desc FROM sys.server_principals  WHERE type = 'S';  

How To Change the Password of a Login Name?

If a developer lost the password of his or her login name, you can reset the password with the "ALTER LOGIN" statement as shown in this tutorial example:

-- Login with sa    ALTER LOGIN my_DBA WITH PASSWORD = 'mrurli222'; 

How To Change a Login Name?

If you want to change a login name, you can use the "ALTER LOGIN" statement as shown in this tutorial example:

-- Login with "sa"    -- Change login name  ALTER LOGIN Fyi_Login WITH NAME = Dba_Login;  

How To Disable a Login Name?

If you want temporarily disable a login name, you can use the "ALTER LOGIN" statement with a DISABLE keyword. If you want to enable it later on, you can use the ENABLE keyword. The tutorial exercise below shows how to disable and enable login name "Dba_Login":

-- Login with "sa"    -- Disable a login  ALTER LOGIN Fyi_Login DISABLE;    
-- View login status  SELECT name, type, type_desc, is_disabled   FROM sys.server_principals   WHERE type = 'S';  

How To Delete a Login Name?

If you don't want to keep a login name any more, you should delete it by using the "DROP LOGIN" statement as shown in this tutorial example:

-- Login with "sa"    DROP LOGIN Dba_Login;  

How To Create a User Name in a Database?

User names are security principals at the database level. If you want to allow a login name to access a specific database, you need to create a user name in that database and link it to the login name.

Creating a user name can be done by using the "CREATE USER" statement as shown in this tutorial exercise:

-- Login with "sa"    -- Create a login  CREATE LOGIN Fyi_Login WITH PASSWORD = 'IYF'  GO    -- Select a database  USE FyiCenterData;  GO    -- Create a user and link it to a login  CREATE USER Fyi_User FOR LOGIN Fyi_Login;  GO  

Login name "Fyi_Login" should be able to access database "FyiCenterData" through user name "Fyi_User".

How To List All User Names in a Database?

If you want to see a list of all user names defined in a database, you can use the system view, sys.database_principals as shown in this tutorial exercise:

-- Login with sa    -- Select a database  USE FyiCenterData;  GO    -- List all user names  SELECT name, sid, type, type_desc      FROM sys.database_principals WHERE type = 'S';  

How To Delete an Existing Database User?

If you don't want to keep a database user any more, you should delete the user by using the "DROP USER" statement. This tutorial exercise shows how to delete "Dba_User":

-- Login with "sa"    USE FyiCenterData;  GO    DROP USER Dba_User;