Database Access Permission Management Based on Windows Domain Accounts in SQL Server

Introduction

In enterprise-level database management environments, configuring security permissions in SQL Server is a frequent and critical task for system administrators. Traditional approaches involve creating separate SQL Server login accounts and passwords to manage user access, but this method presents several inconveniences in modern domain environments. This article delves into how to directly integrate Windows domain accounts into the SQL Server permission system to achieve more secure and convenient access control.

Limitations of Traditional Permission Configuration

In early SQL Server permission management practices, administrators typically used the following T-SQL statements to create new login accounts and database users:

use DBName;
create login a_2 with password='Aa123';
create user a_2 for login a_2;
grant insert to a_2;
grant select to a_2;

While this method achieves basic permission control, it has several notable issues: first, it requires maintaining additional password policies, increasing management complexity; second, users need to remember different credentials for multiple systems; most importantly, this approach cannot fully leverage existing enterprise Windows domain security policies and single sign-on advantages.

Integrated Authentication Based on Windows Domain Accounts

Modern SQL Server supports deep integration with Windows Active Directory, allowing direct authentication using domain accounts. The core advantages of this integrated authentication approach include:

• Unified identity management: Users employ the same domain account to access multiple systems
• Enhanced security: Inherits Windows domain security policies and password complexity requirements
• Simplified management: No need to maintain separate password policies and account lifecycles

Specific Implementation Steps for Permission Configuration

Step 1: Create a Login Based on Windows Account

To create a SQL Server login for a domain user, use the FROM WINDOWS clause in the CREATE LOGIN statement:

CREATE LOGIN [<domainName>\<loginName>] FROM WINDOWS;

Here, <domainName> represents the Windows domain name, and <loginName> is the specific username. For example, to create a login for user "john.doe" in the "COMPANY" domain, the statement should be:

CREATE LOGIN [COMPANY\john.doe] FROM WINDOWS;

Step 2: Create a User in the Target Database

After creating the login, you need to create a corresponding user account in the specific database:

USE (your database)
CREATE USER (username) FOR LOGIN (your login name)

Note that the username can be the same as or different from the login name. In practical applications, consistency is usually recommended for easier management.

Step 3: Assign Database Permissions

Once the user is created, permissions can be assigned in various ways. For read-only access requirements, the most convenient method is to use predefined database roles:

USE (your database)
EXEC sp_addrolemember 'db_datareader', '(your user name)'

The db_datareader role automatically grants the user SELECT permissions on all user tables, greatly simplifying the permission management process.

Permission Management in Multi-Database Environments

In real production environments, it is often necessary to configure access permissions for the same user across multiple databases. Manually repeating the above steps in each database is clearly inefficient. The methods mentioned in the reference article provide automated solutions.

Using the System Stored Procedure sp_MSforeachdb

Although sp_MSforeachdb is an undocumented system stored procedure, it is widely used in practice for batch database operations:

EXECUTE master.sys.sp_MSforeachdb
'
print ''?''
use [?];
if ''?'' not in (''master'',''tempdb'',''model'',''msdb'')
begin;
if not exists (select * from sys.sysusers where name = ''MYDOMAIN\MYUSER'')
create user [<MYDOMAIN\MYUSER>];
alter role db_datareader add member [<MYDOMAIN\MYUSER>];
end;
';

This method iterates through all databases (excluding system databases), creating accounts and assigning read-only permissions for the specified domain user.

Alternative Approach Using Cursors

Considering potential issues with sp_MSforeachdb, a more standard cursor-based approach can achieve the same functionality:

DECLARE @db_name sysname,
@sql NVARCHAR(MAX);
DECLARE @login_name sysname = N'someone',
@user_name sysname = N'someone';
DECLARE db_cursor CURSOR FAST_FORWARD READ_ONLY
FOR
SELECT name
FROM sys.databases
WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb');
OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @db_name;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = N'USE ' + QUOTENAME(@db_name) + ';';
SET @sql += N'IF NOT EXISTS ( SELECT * FROM sys.database_principals WHERE name = N''' + @user_name + ''' )';
SET @sql += N' CREATE USER ' + QUOTENAME(@user_name) + ' FROM LOGIN ' + QUOTENAME(@login_name) + ';';
SET @sql += N'ALTER ROLE db_datareader ADD MEMBER ' + QUOTENAME(@user_name) + ';';
EXEC sys.sp_executesql
@sql;
FETCH NEXT FROM db_cursor INTO @db_name;
END;
CLOSE db_cursor;
DEALLOCATE db_cursor;

Best Practices and Considerations

When implementing permission management based on domain accounts, the following points should be noted:

• Principle of least privilege: Grant users only the minimum permissions necessary to perform their work
• Regular auditing: Periodically review user permission configurations in the database
• Handling new databases: Newly created databases do not automatically inherit permission configurations and require manual or automated script processing
• Security considerations: Ensure that domain accounts themselves have appropriate security configurations

Conclusion

By utilizing Windows domain account integrated authentication, SQL Server administrators can achieve a more unified and secure permission management system. This approach not only simplifies user management processes but also enhances the overall security of the system. Combined with the use of automated scripts, it can significantly improve management efficiency in multi-database environments. As enterprise informatization continues to advance, this domain-integrated permission management model is set to become the mainstream trend in SQL Server security management.