In SQL Server administration, there are two terms that can easily be confused: SQL Login and SQL User. While SQL Login allows you to manage permissions for server access, SQL User defines permissions for database access. In this article, we’ll take a closer look at SQL Login.

SQL Login

It provides access to SQL Server. There are two alternatives for connecting:

• Windows Authentication
• SQL Authentication

The first method is based on a domain account in Active Directory (Windows account). The second method involves creating a SQL Login and password directly on the server. With the second method, you may encounter connection issues with the message “Login failed for user.” This is because this login method must be enabled in the server properties – it is not enabled by default (see this article – Login failed for user).

Server roles in SQL Server

1. sysadmin – Highest permission, can perform any activity on the server
2. serveradmin – Access to server configuration, can shut down the server
3. setupadmin – Can link servers
4. securityadmin – Can manage user permissions for database and server access. Can also reset passwords
5. processadmin – Can kill (terminate) processes on the SQL server
6. dbcreator – Can create, modify, or remove any database
7. diskadmin – Has the right to manage database file disks
8. bulkadmin – Permissions for BULK INSERT
9. public – Lowest permission. If no higher permission is granted for a user, they inherit public

SQL Script for Creating a Login (Create Login):

1) Example of creating a login with sysadmin role, default database, and language

DECLARE @userid sysname = 'Login', @password sysname = 'Heslo'
EXEC sp_addlogin 
@loginame = @userid,
@passwd = @password,
@defdb = 'Databáze',
@deflanguage = [British],
@sid = null,
@encryptopt= null;
EXEC sys.sp_addsrvrolemember @userid, @rolename = N'sysadmin';

2) Creating a regular login without additional settings

CREATE LOGIN Login WITH PASSWORD = 'Zp?werwefvw8mt$D'/' ;