Security is not just a DBA’s job.
If you write stored procedures, manage databases, or deploy code, you are responsible for security too.
This guide explains SQL Server security clearly, practically, and production-ready.
1️⃣ Understanding SQL Server Security Architecture
SQL Server security works in two levels:
🔹 Server Level
- Logins
- Server Roles
🔹 Database Level
- Users
- Database Roles
- Permissions
📌 Login = who can enter SQL Server
📌 User = what they can do inside a database
2️⃣ Creating Logins
Windows Login
CREATE LOGIN [DOMAIN\UserName] FROM WINDOWS;
SQL Login
CREATE LOGIN AppUser
WITH PASSWORD = 'StrongPassword123';
⚠ Never use weak passwords in production.
3️⃣ Creating Database Users
USE MyDatabase;
CREATE USER AppUser
FOR LOGIN AppUser;
📌 Login exists at server level.
User exists at database level.
4️⃣ Assigning Roles (Best Practice)
Instead of giving direct permissions, use roles.
Built-in Database Roles
- db_datareader
- db_datawriter
- db_owner
ALTER ROLE db_datareader ADD MEMBER AppUser;
📌 Always prefer role-based security.
5️⃣ Grant, Deny, Revoke Explained
GRANT
Gives permission.
GRANT SELECT ON Orders TO AppUser;
DENY
Explicitly blocks permission.
DENY DELETE ON Orders TO AppUser;
REVOKE
Removes granted permission.
REVOKE SELECT ON Orders FROM AppUser;
📌 DENY overrides GRANT.
6️⃣ Giving Permission to Execute Stored Procedures
Best practice: allow execution, not table access.
GRANT EXECUTE ON dbo.GetUserOrders TO AppUser;
✔ Application accesses only procedures
✔ Tables stay protected
7️⃣ Avoid Giving db_owner to Applications
❌ Many beginners do this:
ALTER ROLE db_owner ADD MEMBER AppUser;
This gives full control — very risky.
📌 Principle of Least Privilege:
Give only what is required.
8️⃣ Schema-Based Security
Instead of table-by-table permissions:
GRANT SELECT ON SCHEMA::Sales TO AppUser;
Cleaner. Easier to manage.
9️⃣ Protecting Sensitive Data
Use Column-Level Permission
GRANT SELECT (OrderId, Amount)
ON Orders
TO AppUser;
Or Use Views
Create a view exposing only safe columns.
🔟 Understanding Ownership Chaining
If stored procedure and tables have same owner:
✔ User needs EXECUTE permission only
✔ No direct table permission required
📌 Very powerful security model.
1️⃣1️⃣ Check Existing Permissions
SELECT *
FROM fn_my_permissions(NULL, 'DATABASE');
Or check user permissions:
EXEC sp_helprotect;
1️⃣2️⃣ Auditing Security
Track who has access:
SELECT
dp.name AS UserName,
dp.type_desc,
rp.name AS RoleName
FROM sys.database_role_members drm
JOIN sys.database_principals dp
ON drm.member_principal_id = dp.principal_id
JOIN sys.database_principals rp
ON drm.role_principal_id = rp.principal_id;
1️⃣3️⃣ Real Production Mistake Example
❌ Issue
Application accidentally deleted production data.
🔍 Root Cause
App user had:
db_owner role
✅ Correct Setup
- Remove db_owner
- Grant EXECUTE only
- Restrict DELETE permission
1️⃣4️⃣ Secure Stored Procedure Pattern
CREATE PROCEDURE dbo.InsertOrder
@UserId INT,
@Amount DECIMAL(10,2)
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO Orders(UserId, Amount)
VALUES (@UserId, @Amount);
END
Then:
GRANT EXECUTE ON dbo.InsertOrder TO AppUser;
✔ No direct table access
✔ Controlled insert
1️⃣5️⃣ SQL Server Security Checklist
✔ Use strong passwords
✔ Avoid db_owner for apps
✔ Use roles, not direct permissions
✔ Grant EXECUTE instead of table access
✔ Audit permissions regularly
✔ Follow least privilege principle
✍️ Conclusion
Security is not optional.
A single wrong permission can destroy production data.
As a SQL Server developer with serious experience, mastering security makes you:
✔ More trusted
✔ Production-ready
✔ Architect-level professional