SQL Server – Login vs User

Essentially Logins are on the Server level, whereas Users are on the Database level; Logins allow access to the server and Users allow access to databases. Logins are assigned server roles like serveradmin or securityadmin and Users are assigned database roles like db_datareader and db_datawriter. Where the two meet is that Logins are mapped to a User, and the User relies on the Logins’ credentials.

Here is a list of server and database roles and a quick description as well:

Database Roles:

db_owner – Full access

db_accessadmin – Manage Windows groups/SQL Server Logins

db_datareader – Read data

db_datawriter – Alter data

db_ddladmin – Run DDL statements

db_securityadmin – Modify Roles and manage permissions

db_bckupoperator – Back up databases

db_denydatareader – Not allowed to view data

db_denydatawriter – Not alowered to alter data

(more info)

Server Roles:

sysadmin – Do anything on server

serveradmin – Alter server configurations and shut down server

securityadmin – Manage Logins and their properties, reset SQL server Login passwords, and GRANT/DENY/REVOKE server/database permissions

processadmin – End processes running in SQL Server

setupadmin – Add/remove linked servers by use of T-SQL

bulkadmin – Able to run BULK INSERT statement

diskadmin – Manage disk files

dbcreator – Create/Alter/Drop/Restart databases

public – Role inherited by users when server principals are not granted or denied

(more info)