If you are creating a SQL Server login using a script then it is advisable to check if the login you are creating already exists. If you do not do this then SQL Server will throw an error. For example if I execute this statement when a login with name test_user already exists, I will get the following error.

CREATE LOGIN test_user WITH PASSWORD = 'Password123'

Msg 15025, Level 16, State 1, Line 1
The server principal 'test_user' already exists.

To avoid this a check can be used before creating the login. One way to do this is to write a statement like this.

IF NOT EXISTS(SELECT name FROM master.dbo.syslogins WHERE name = 'test_user')
BEGIN
    CREATE LOGIN test_user WITH PASSWORD = 'Password123'
END

The above statement will check for the login and it will only create it if the login does not already exist. This works but it is not the best way to do it in SQL Server 2008. The problem is that we are querying master.dbo.syslogins which is available only for backward compatibility and as per Microsoft it will be removed in future versions. So the best way to do a check before creating a login is to use sys.sql_logins or sys.server_principals system view. Our statement can be re-written in following two ways.

IF NOT EXISTS(SELECT name FROM sys.server_principals WHERE name = 'test_user')
BEGIN
    CREATE LOGIN test_user WITH PASSWORD = 'Password123'
END

OR

IF NOT EXISTS(SELECT name FROM sys.sql_logins WHERE name = 'test_user')
BEGIN
    CREATE LOGIN test_user WITH PASSWORD = 'Password123'
END

This approach will ensure that your script will work in future versions of SQL Server when Microsoft decides to remove master.dbo.syslogins.