SQL Server Database User Management

#DecodeITeS

Database Level Security Principals are to control and assign database-level permissions. Database Level Security Principals secure guard your data at database levels. Logins with super privileges can override database-level users & permissions. Database Level Principals has 2 components: -

• Database Users
• Database Roles

Database user

SQL Server protects your data at 2 layers. Layer one is Logins where the user needs to authenticate itself at the server level and layer two is Database user where user mapped to particular login needs permissions at the database level to access data and database configuration.

The database user is a database-level server principal. Database users are entities to which access can be granted to access database level configurations and objects.

Login v/s Users

1. Logins are Server Level Security Principals whereas Users are database-level security principals.

2. Logins created at the server level for server-level access and configurations. Database users created at the database level for database-level access and configuration.

3. Logins can be granted server-level permissions, Server Roles, and Users can be granted Database level permissions and database roles. Users can access server-level permissions inherited from alined login.

4. Logins need database mapping, mapped database users, and permissions to connect & access database. SQL Server provides the feature of a partially contained database, in which users can be created and access without logins. In this case, User login details save at the database level not in the master database.
5. Logins can be mapped to different databases with different users however only one mapped per database allowed. Other than Login, the user between 2 databases has no connection and relation.
6. Logins and mapped user can be of different names. The mapping between logins and users works on a security identifier (SID).

Create \ Alter \ Drop Users Using SSMS & TSQL

How does Database User authentication work?

We had discussed SQL Server Authentication and process to logins authorization. Once Login makes a successful connection to SQL Server, SQL Server matches the Login SID from sys.server_principals and Database mapped user SID from sys.database_principals. Once the DB engine found both SID are same, It allows login to access database objects and configurations. SQL Server works on SID matching because login can be mapped to different users in each database as well as login and user names can be different.

You cannot change the SID of login once created. For Windows Active Directory Login, SID will be the same across domain on all SQL instances. SID is stored in AD and SQL Server takes the reference of it while creating a login. No Matter how many times you drop or recreate Windows Active Directory user, SID will remain the same. For SQL Login, SID is instance-specific. Even if you recreate the user, SID will be changed.