Database Security Foundations

Security in SQL Server is hierarchical. If you give your web app sysadmin permissions just because it is "easier," you have created a massive security hole. A professional architect follows the **Principle of Least Privilege**.

1. Logins vs Users

• Login: Handles Authentication at the **Server** level (e.g., Windows Login or SQL Login). Like the "Key to the Building."
• User: Handles Authorization at the **Database** level. Like the "Key to a specific Office." A Login must be mapped to a User inside a database to see any data.

2. Database Roles

Never grant permissions to individual users. Use **Roles**. Grant permissions to the WebAppRole, then add your application's Login to that role. This makes auditing and management 100x easier.

4. Interview Mastery

Q: "What is the danger of 'Contained Databases'?"

Architect Answer: "Contained Databases store the User information *inside* the .mdf file instead of the master database. This makes the database very portable (you can move the file to a new server and the users 'just work'). However, it makes it harder for a central DBA to monitor security across the entire server, as users are hidden inside individual files. Use them for Cloud/Azure migrations, but be careful in traditional on-premise environments."