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).

SQL Authentication - enabling in server properties

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'/' ;
Rate this post

Ing. Jan Zedníček - Data Engineer & Controlling

My name is Jan Zedníček and I have been working as a freelancer for many companies for more than 10 years. I used to work as a financial controller, analyst and manager at many different companies in field of banking and manufacturing. When I am not at work, I like playing volleyball, chess, doing a workout in the gym.

🔥 If you found this article helpful, please share it or mention me on your website

Leave a Reply

Your email address will not be published. Required fields are marked *