Users
In Microsoft SQL Server
Sai Reddy 1/30/25 MS SQL
Users in MS SQL Server
In MS SQL Server, the Users under the Security folder represent database-level users. These
users are associated with logins and have permissions to access objects within a specific database.
By default, when you check the Users folder inside a database,
you will see the following system users:
1️. dbo (Database Owner)
2️. guest
3️. Information_Schema
4️. sys
Let's break them down in detail.
System Users in SQL Server
1️. dbo (Database Owner)
• The dbo (Database Owner) user has full control over the database.
• Any sysadmin role member automatically maps to dbo in every database.
• The dbo owns all database objects unless assigned otherwise.
🛠 Example - Checking dbo User in a Database
SELECT name, type_desc FROM sys.database_principals WHERE name = 'dbo';
2️. guest User
• The guest user allows logins without a specific user mapping to access a database.
• It is disabled by default for security reasons.
🛠 Disable guest User Access for a Database
REVOKE CONNECT TO guest;
🛠 Enable guest User (Not recommended)
GRANT CONNECT TO guest;
Use Case: Only required when external logins need temporary access to a database.
3️. Information_Schema User
• This user provides metadata about database objects.
• It contains views that describe tables, columns, constraints, and indexes.
🛠 Example - Retrieving Table Information
SELECT * FROM INFORMATION_SCHEMA.TABLES;
Use Case: Developers use INFORMATION_SCHEMA views to dynamically generate queries and
scripts.
Sai Reddy
saireddy-dotnetfs
4️. sys User
• The sys user owns system objects stored in the sys schema.
• It contains DMVs (Dynamic Management Views) and system catalog views.
🛠 Example - Checking System Tables
SELECT * FROM [Link];
Use Case: Used for querying database internals, performance tuning, and monitoring.
Types of Users in SQL Server
SQL Server supports different types of database users for security and access control.
User Type Description
SQL Server Authentication
A user mapped to a SQL Server login.
User
Windows Authentication User A user mapped to a Windows domain login.
Certificate-Based User A user associated with a security certificate.
Asymmetric Key-Based User A user linked to an asymmetric key.
External User (Azure AD User) A user from Azure Active Directory.
A user created for application-level access instead of individual
Application Role
logins.
Creating, Updating, and Deleting Users in SQL Server
🛠 Create a New SQL User
CREATE USER [SaiReddy] FOR LOGIN [SaiReddyLogin] WITH
DEFAULT_SCHEMA = dbo;
🛠 Grant Permissions to a User
GRANT SELECT, INSERT, UPDATE ON [Link] TO SaiReddy;
🛠 Update User (Changing Default Schema)
ALTER USER [SaiReddy] WITH DEFAULT_SCHEMA = sales;
🛠 Delete a User
DROP USER [SaiReddy];
Sai Reddy
saireddy-dotnetfs
Advantages & Disadvantages of Users in SQL Server
Advantages
Provides security control over database access.
Enables role-based access for managing users efficiently.
Allows integration with Windows Authentication and Active Directory.
Helps in separating permissions between different applications.
Disadvantages
Incorrect user management can lead to unauthorized access.
Excessive permissions may increase security risks.
Misconfigured users can impact performance if multiple connections are not optimized.
Real-Time Use Cases of SQL Server Users
❖ Multi-Tenant Applications: Different users for different clients.
❖ Finance and Banking: Role-based access control for customers, auditors, and
administrators.
❖ Healthcare Systems: Doctors, nurses, and receptionists have different database privileges.
❖ E-Commerce: Admin, vendor, and customer roles with controlled access to orders and
products.
Sai Reddy
saireddy-dotnetfs
