SQL Server – Server Security – Part 4

Hello Folks,

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:

EXEC sp_helplogins

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.

Keep Tuned!!

Hope you got it understood well 🙂

And also comments on this!!

Regards

Piyush Bajaj

Like us on FaceBook  |  Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on Twitter  |  Follow me on FaceBook