• Skip to main content
  • Skip to primary sidebar

DallasDBAs.com

SQL Server Database Consulting

  • Services
  • Pocket DBA®
  • Blog
  • Testimonials
  • Contact
  • About

Security

SQL Server Logins vs. Users: Why You Really Need to Know the Difference

May 28, 2025 by Kevin3NF Leave a Comment

The Two-Layer Model Explained

“We added them to the database, but they still can’t connect.”

Sound familiar? That’s the kind of confusion SQL Server’s two-layer security model creates when people don’t understand the difference between a login and a user.

Let’s clear that up, because getting it wrong causes broken access, orphaned users, and frustrated help desk calls.

Two Layers: One to Get In, One to Do Anything

SQL Server controls access at two separate levels:

  1. Login (Server-Level)
  • Exists at the SQL Server instance level
  • Allows someone or something to connect to the server
  • Can be:
    • SQL login (username + password inside SQL)
    • Windows login (domain account)
    • Windows group (preferred for manageability)

But a login alone doesn’t grant access to databases.

  1. User (Database-Level)
  • Exists inside a specific database
  • Grants access to that database and defines roles/permissions
  • Must be mapped to a login, unless you’re using a contained database

So:
Login = authentication (Can you connect?)
User = authorization (What can you do inside the database?)

What Happens When They Don’t Match?

Here’s a common scenario:

You restore a database from Prod to Test. A user exists in the database, but the matching login doesn’t exist on the new server.

Result? You’ve got an orphaned user.

SQL Server sees the user in the database, but there’s no login at the instance level with the same SID. The connection fails—even though everything “looks right.”

How to Fix Orphaned Users

Run this at the database level to find all orphaned users:

EXEC sp_change_users_login 'Report';

(Deprecated, but still handy in older versions.)

For newer systems, use:

Use MyDatabase;
GO

SELECT dp.name AS UserName
FROM sys.database_principals dp
LEFT JOIN sys.server_principals sp ON dp.sid = sp.sid
WHERE dp.type IN ('S', 'U') AND sp.sid IS NULL;

Use this command to remap a user to an existing login:

ALTER USER [username] WITH LOGIN = [loginname];

Or…this is a process I’ve been using for 20 years:

Transfer logins and passwords between instances – SQL Server | Microsoft Learn

Why You Should Care

  • Granting a login access to the server doesn’t give them database access.
  • Creating a user in a database without a login won’t allow connections.
  • Orphaned users silently break apps after restores or migrations.
  • Unused users or logins clutter security and increase audit risk.

 

Best Practices

  • Use AD groups as logins whenever possible. Much easier to manage access for teams.
  • Don’t grant permissions to logins directly. Always go through database users and roles.
  • When dropping users or logins, clean up both sides: DROP USER and DROP LOGIN.
  • Document mappings so you know which logins tie to which databases.

 

Database Emergencies Cost Thousands Per Minute

When SQL Server fails, you need expert help fast.
For a limited time: First month of Pocket DBA® FREE with a 6-month plan.

• Senior DBAs available 24/7 • 1-hour SLA • Proactive problem prevention

Don’t wait for a disaster. Be prepared.
CLAIM FREE MONTH
Limited availability.


Thanks for reading!

–Kevin

Filed Under: EntryLevel, Security, SQL Tagged With: syndicated

SQL Server Login Types: Knowing the Difference Really Matters

May 21, 2025 by Kevin3NF Leave a Comment

If your SQL Server has both SQL and Windows logins and you’re not sure why, you’re not alone. Many IT teams without a dedicated DBA bump into this sooner or later. And sometimes…things break just because someone checked the wrong box during setup.

Let’s break it down.

Two Login Types, Two Worlds

SQL Server supports two authentication modes:

Windows Authentication

  • Uses your Active Directory account (individual or group) to connect.
  • Most secure and manageable in domain environments.
  • Passwords are handled by Windows, so no extra password to manage in SQL Server.

SQL Server Authentication

  • Uses a separate username and password stored inside SQL Server.
  • Necessary for non-domain users, apps, or services outside your network.
  • Password policies are optional (and often ignored… oof).

 

Mixed Mode vs Windows-Only Mode

Your SQL Server can be set to:

  • Windows-only Mode (no SQL logins allowed), or
  • Mixed Mode (allows both types)

Most installs use Mixed Mode, even when they don’t need to because “that’s how it was installed,” or “some app needed a SQL login once.”

Tip: Use Windows authentication whenever possible. It’s integrated, auditable, and far more secure.

Common Pitfalls

  • “sa” is enabled and still using the default password (Yes, we’ve seen this. More than once.)
  • SQL logins don’t expire or require password complexity, unless you set that option.
  • Orphaned users (exists in the database but not on the server).
  • Confusion about who can access what—especially after domain changes or user departures.

 

The Bottom Line:

  • Audit your logins and users regularly. Know what’s there.
  • Disable “sa” or rename it.
  • Use Windows logins/groups for real people whenever possible.
  • Use SQL logins only when Windows auth isn’t an option and treat them like sensitive credentials.

Security breaches often start with a misconfigured login. A little attention now saves a lot of pain later.


Database Emergencies Cost Thousands Per Minute

 

When SQL Server fails, you need expert help fast.
For a limited time: First month of Pocket DBA® FREE with a 6-month plan.

  • Senior DBAs available 24/7 • 1-hour SLA • Proactive problem prevention

Don’t wait for a disaster. Be prepared.
CLAIM FREE MONTH
Offer ends June 1st. Limited availability.


Thanks for reading!

–Kevin

Filed Under: Security, SQL Tagged With: syndicated

TIL: Security Stuff™

March 12, 2018 by SQLDork 3 Comments

Testing Grant and Deny permissions in SQL Server.

Here’s a rough breakdown of the steps we followed:

  • Create a windows group, and create 2 users in the group (We’ll call them test1 and test2).
  • Make a new database called SecurityTest.
  • Add the windows group from earlier as a SQL LOGIN.
  • Add a USER for the login in the SecurityTest database, with no permissions (yet).
  • Create a table and put some data™ in it (We’ll call it SuperSecretTable™).
  • Grant select permission to the USER (Group).
  • Verify that users test1 and test2 can see the data™ (You’ll have to logon to the machine as these users).
  • Test2 takes a screenshot of the SuperSecretTable™ and posts it on Twitter, gets 3 likes.
  • I login as myself and specifically DENY test2 rights to view the table because he cannot be trusted with the data in the SuperSecretTable™ (Or crayons).
  • Test2 logs in again, and finds himself unable to even see the SuperSecretTable™, much less the data™ in it, even though he’s in a group that has been GRANTed SELECT permissions.

In doing this, we’ve proved that DENY takes precedence over GRANT, because we’re cool like that

/*Please work*/,

The Apprentice

Filed Under: Apprentice, Security, TIL

SQL Saturday Minnesota 2017

August 31, 2017 by Kevin3NF Leave a Comment

Now that the official schedule has been published, I am pleased to announce that I will be doing a pre-con and a regular session at SQL Saturday Minnesota on October 6-7. 2017.

The Friday pre-conference will be “DBA Fundamentals for the Junior, Accidental and non-DBAs.”  This will be a wide ranging day-long discussion on the underlying fundamental pieces of SQL Server Administration that are beneficial to both new DBAs, and those that have to work with or fill in for them.  Please see the link for details, and feel free to contact me with any questions.  There is a $110 (plus service fees) charge for this, which is split between me and the local SQL User group organizing the event, after expenses.

The Saturday regular session will be “Backups for new/non-DBAs…the Why, not the How”, where we will cover Disaster recovery scenarios and the part SQL Server plays in the recovery.  We won’t spend much time on specifically defining backup types, etc.    Currently this is scheduled for the 1-2pm time slot, but that may change.

Edit: my Saturday session is now 10:15am – 11:15am 🙂

I’m very excited to be coming to the MSP area!  I even have a friend that lives in the area that I get to see for the first time in 3 years…plus all of the new friends I will make at the events!

Thanks,

Kevin3NF

Filed Under: Accidental DBA, backup, Beginner, EntryLevel, HADR, Install, Security, SQLSaturday

xp_CmdShell is not a security risk…people are!

March 22, 2017 by Kevin3NF 4 Comments

Quickie today….

Someone please tell me why this is considered insecure:

A stored procedure that, out of the box, is disabled and has no explicit rights granted (or denied) is locked down to everyone but those in the sysadmin server role.

If someone exploits your SQL Server via xp_cmdshell, its because you LET them, either by granting permissions or by putting someone in sysadmin that clearly should not have been there.

Told you it was a quickie 🙂

Thanks for reading!

Kevin3NF

Filed Under: Security

SQL 101: The SQL Server Security Model

February 1, 2017 by Kevin3NF 5 Comments

The SQL Server Security model has a lot of moving parts to it.  Some friends of mine are so expert in it that it is their primary function on the DBA team they work for.  My friend (and recently minted Data Platform MVP) Kenneth Fisher (b|t) regularly presents on it.

Fear not!  There are a couple of key concepts that you have to get down early in order to not drown in all the other stuff.

As with most of my other 101 posts, this occurred to me while riding my bike.  Road bike…the only thing that occurs to me when mountain biking is “don’t hit that tree.”

Imagine if you will, that you own a house.

Because you are the owner, you have a key to the house.   You can do anything you want in any room of the house.  You decide who else gets keys, and what they can do inside.  We’ll assume your significant other also has a key and equal rights to all rooms.

Now just for kicks, lets assume you have 2 kids.  Suzie and Little Bobby Tables (Bobby for short).  Since mom and dad work, Suzie and Bobby also both have keys so they can get inside after school.   However, they are not allowed into each other’s rooms, nor are they allowed to change the locks, paint the walls or use the stove.  Because reasons.

So, 4 people can enter the house.   2 can do whatever they want, 2 have some restrictions.   Simple enough?

In the SQL Server security model the above translates as follows:

  • House – an instance of SQL Server
  • Keys to the house – Logins
  • Mom and Dad – Logins with sysadmin role (God rights…)
  • Suzie and Bobby – Logins without sysadmin
  • Rooms – databases

You have to have a key/Login, to even open the door/connect to the instance.

You need specific permissions to access a database/room.   Bobby is not allowed in Suzie’s room and vice-versa.  Neither kid is allowed in dad’s man-cave, but mom is.

Suzie and Bobby need to be granted access to use a database/room, so they are created as USERS in the database.   Mom and Dad do not explicitly need to be granted as USERS in any database, because they own the place and can do whatever they want at the server/house or database/room level.

If Uncle Joe is coming for a visit, he would also get a key/LOGIN and access to the rooms/databases he will be using.   When he leaves, he gives his key back (USER/LOGIN removed).

If you want, imagine Bobby has a brother and they share a room.  In their room there are two beds.  They don’t share and they don’t switch.  In this example the beds are database objects such as tables.  You can grant or deny access down to that level as well.

So, LOGIN is created first (can’t go into a room until you get in the house). USER access for non sysadmins is created next at the database level.  Inside the database, you GRANT or DENY permissions to objects in the room (if you choose).   You can get very, very granular if your application/employer requires it.

This is high level overview, with a lot of specifics left out.  Such as, give LOGINs to Groups (preferred, instead of individuals), server and database roles, etc.   Just knowing the difference between LOGIN and USER puts you ahead of non-DBAs who use the terms interchangeably.  Login can be either Windows based or created within SQL Server depending on how the instance is configured and your security teams requirements.

Hope this makes your life easier!  Comments are welcomed.

Update:   My Apprentice astutely made the observation that a physical or virtual server with more than one SQL Server Instance installed would be a “neighborhood” in this example.   Would that make a Failover Cluster a duplex? 🙂   Would a multi-tenant database be an apartment building?

Thanks for reading,

Kevin3NF

 

 

Filed Under: Accidental DBA, Apprentice, Beginner, EntryLevel, Security

  • Go to page 1
  • Go to page 2
  • Go to Next Page »

Primary Sidebar

Search

Sign up for blogs, DBA availability and more!

Home Blog About Privacy Policy
  • Home-draft
  • Blog
  • About Us

Copyright © 2025 · WordPress · Log in

 

Loading Comments...