You would have seen my previous article-post, which had a discussion on how to deal with Windows Login using a Windows Authentication Mode. If you want to see it, just browse the link;
Well in this article, I would specially like you to know how the SQL Server Logins work.
SQL Server Logins:
- SQL Server Logins are useful only when Windows authentication is inappropriate or unavailable.
- It’s mainly being provided in the case of backward compatibility and for the legacy applications that are hard-coded to a SQL Server login.
- As you would have seen during the installation of SQL Server, that if you will opt for mixed mode; then automatically the “sa” user will be created who will be a member of the sysadmin fixed server role and have all the rights to the server.
- If an sa user is being created without any password protection, then there might be a big risk of attack because every hacker tries it when detecting a SQL Server.
- Therefore, the best option would be to disable the sa user and instead of assigning fixed server role, we should assign different users with different roles.
- So, to create SQL Server Logins, there are again two methods and we can opt any of them to create the logins:
- With the Management Studio
- With the T-SQL
Creating a SQL Server Login using Management Studio:
Follow these simple steps to create SQL Server Login:
1. Click the New Login.
2. In the Login-New window, do the following changes and then click Ok.
SQL Server Login is being created.
Creating a SQL Server Login using T-SQL:
The following code adds “SQLServerGeeks” as a SQL Server user and sets the default database to the TEST sample database:
EXEC sp_addlogin 'SQLServerGeeks', 'piyush', 'Test'
If you want to know more about logins, then there is a stored procedure:
SQL Server can identify the user only by the means of server user ID, or SID, which is a 85-bit binary value. If the same user is being set up on two or more servers, then the SIDs of the user need to determined. Therefore, the query for sys.server_principals catalog view to find the user’s SID is:
SELECT Name, SID FROM sys.server_principals WHERE Name = 'sql'
The result can be seen as:
How to Change a Password:
If now, I am willing to change my password then used the stored procedure:
EXEC sp_password 'piyush', 'bajaj', 'sqlservergeeks'
Here ‘piyush’ is an old password, ‘bajaj’ is a new password, and ‘sqlservergeeks’ is a login-id.
In the case of blank password, use NULL instead of quotes (‘ ’):
EXEC sp_password NULL, 'bajaj', 'sqlservergeeks'
How to Remove a Login:
Well, you can also delete this in two ways:
1. See this:
2. Using a stored procedure:
EXEC sp_droplogin 'SQL'
How to set a Default Database:
The default can also be set either of the two ways:
1. With Management Studio:
Double-click the login account which you want to change. Then you can change the default database as well as language:
2. With T-SQL: Use the stored procedure to set a default database.
EXEC sp_defaultdb 'SQLServerGeeks', 'test'
Here, SQLServerGeeks is a login account, and TEST is the default database to which we want to set.
Well this was enough for this article-post. In the next article-post, i.e. , Part 5 , I would like to deal with the different server roles used in SQL Server Logins.
Hope you got it understood well 🙂
And also comments on this!!