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:

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!!



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


About Piyush Bajaj

I am very passionate about SQL Server. I also did certification on MCSA – SQL Server 2012, Querying and Administering; MCTS – SQL Server 2008, Database Development; and MCTS – SQL Server 2005, Implementation & Maintenance, which helped me to get more knowledge and interest on this field.Please feel free to drop me any question online or offline, I will try to give you the best possible answer from my side.Right now I am working as a SQL Server developer in TCS. I have an experience of just 2.6 years, well I can only say that “If you have an interest and passion, experience might become a very small thing”.

View all posts by Piyush Bajaj →

Leave a Reply

Your email address will not be published.