MSSQL Logins

The example below duplicates a login associated with a domain group so that members of the domain group on either of two different domains can login.For further examples see: https://www.mssqltips.com/sqlservertip/4679/clone-a-sql-server-login-and-password-to-a-new-server/

SSMS

Right click on the Login to duplicate...

TSQL

Run the script included below in all databases (except model and tempdb) starting from master. The script declares a mask to identifiy which users/logins to process. The example is set to look for all users/logins in MYDOMAIN\. Copy and paste output to the new window (for simplicity paste each output into the same window) and update as necessary.

SET implicit_transactions OFFIF @@trancount > 0 ROLLBACKGODECLARE @UserName sysname = 'MYDOMAIN\%' SELECT t.p, t.obj_name FROM (SELECT 'use [' + DB_NAME() +'];' AS p, NULL AS obj_name, 1 AS sort UNION ALL --create login, returns ony if excuted in context of master SELECT 'CREATE LOGIN [' + name + '] FROM WINDOWS WITH DEFAULT_DATABASE=[' + dbname + ']', NULL, 11 AS sort FROM sys.syslogins WHERE name LIKE ISNULL(@UserName, '%') AND db_name() = 'master' UNION ALL SELECT 'CREATE USER [' + name + '] FOR LOGIN [' + name + '];', NULL, 20 AS sort FROM sys.sysusers WHERE islogin=1 AND name NOT IN ('dbo','guest','INFORMATION_SCHEMA','sys') AND name LIKE ISNULL(@UserName, '%') UNION ALL SELECT CASE dperms.state_desc WHEN 'GRANT_WITH_GRANT_OPTION' THEN 'GRANT' ELSE state_desc END + ' ' + permission_name + --cast(dperms.class as sysname) + CASE WHEN dperms.class = 0 THEN '' ELSE ' ON ' END + CASE dperms.class WHEN 0 THEN ''--'[' + DB_NAME() + ']' /*DATABASE::*/ WHEN 1 THEN CASE dperms.minor_id WHEN 0 THEN '[' + sch.[name] + '].[' + obj.[name] + ']' /*OBJECT::*/ ELSE '[' + sch.[name] + '].[' + obj.[name] + '] ([' + col.[name] + '])'/*OBJECT::*/ END WHEN 3 THEN 'SCHEMA::[' + SCHEMA_NAME(major_id) + ']'/*SCHEMA::*/ WHEN 4 THEN '[' + USER_NAME(major_id) + ']'/*USER::*/ WHEN 24 THEN '[' + symm.[name] + ']'/*SYMMETRIC KEY::*/ WHEN 25 THEN '[' + certs.[name] + ']'/*CERTIFICATE::*/ WHEN 26 THEN '[' + asymm.[name] +']'/*ASYMMETRIC KEY::*/ END + ' TO [' + dprins.[name] + ']' + CASE dperms.state_desc WHEN 'GRANT_WITH_GRANT_OPTION' THEN ' WITH GRANT OPTION;' ELSE ';' END COLLATE database_default AS 'Permissions', '[' + sch.[name] + '].[' + obj.[name] + ']', 30 AS sort FROM sys.database_permissions dperms INNER JOIN sys.database_principals dprins ON dperms.grantee_principal_id = dprins.principal_id LEFT JOIN sys.columns col ON dperms.major_id = col.object_id AND dperms.minor_id = col.column_id LEFT JOIN sys.objects obj ON dperms.major_id = obj.object_id LEFT JOIN sys.schemas sch ON obj.schema_id = sch.schema_id LEFT JOIN sys.asymmetric_keys asymm ON dperms.major_id = asymm.asymmetric_key_id LEFT JOIN sys.symmetric_keys symm ON dperms.major_id = symm.symmetric_key_id LEFT JOIN sys.certificates certs ON dperms.major_id = certs.certificate_id WHERE dperms.type <> 'CO' /* that corresponds to having the ability to connect to the database. Normally this is handled with the CREATE USER statement */ --dperms.major_id > 0 /* < 0 - would be altering permissions against system objects */ AND dprins.[name] LIKE ISNULL(@UserName, '%') --and (dperms.class = 0 /* DATABASE */ or (dperms.class > 0 and dperms.major_id > 0)) /* leave GRANT CONNECT privilege */ --and obj.is_ms_shipped=0 UNION ALL /* GROUPS */ SELECT 'exec sp_addrolemember ''' + p.NAME+ ''','+ '['+ m.NAME+']'+ ' ;', NULL, 40 AS sort FROM sys.database_role_members rm JOIN sys.database_principals p ON rm.role_principal_id = p.principal_id JOIN sys.database_principals m ON rm.member_principal_id = m.principal_id WHERE m.name NOT LIKE 'dbo' AND m.name LIKE ISNULL(@UserName, '%') UNION ALL --system privielges, return only in context of master SELECT 'use [master]; GRANT ' + pe.permission_name + ' TO [' + pr.name + '];', NULL, 2000 AS sort FROM sys.server_principals AS pr JOIN sys.server_permissions AS pe ON pe.grantee_principal_id = pr.principal_id WHERE pr.name LIKE ISNULL(@UserName, '%') AND pr.name NOT LIKE '##%' AND pr.name NOT IN ('dbo', 'public', 'NT AUTHORITY\SYSTEM') AND pe.permission_name <> 'CONNECT SQL' AND db_name() = 'master' UNION ALL --server roles, return only in context of master SELECT 'use [master]; execute master..sp_addsrvrolemember N' + '''' + P.name + '''' + ', N' + '''' + R.name + '''' + ';', NULL, 3000 AS sort FROM sys.server_role_members RM INNER JOIN sys.server_principals P ON RM.member_principal_id = P.principal_id INNER JOIN (SELECT principal_id, name FROM sys.server_principals WHERE type_desc = 'SERVER_ROLE') R ON RM.role_principal_id = R.principal_id WHERE P.name NOT LIKE '#%' AND P.name NOT LIKE 'NT %' AND P.type_desc <> 'SERVER_ROLE' AND P.name NOT IN ('sa') AND p.name LIKE ISNULL(@UserName, '%') AND db_name() = 'master' ) AS t WHERE t.p IS NOT NULL ORDER BY sort asc, p ascGOIF @@trancount > 0 ROLLBACK

Certificate-based SQL Server Logins

Server principals with names enclosed by double hash marks (##) are for internal system use only. The following principals are created from certificates when SQL Server is installed, and should not be deleted.

##MS_SQLResourceSigningCertificate##
##MS_SQLReplicationSigningCertificate##
##MS_SQLAuthenticatorCertificate##
##MS_AgentSigningCertificate##
##MS_PolicyEventProcessingLogin##
##MS_PolicySigningCertificate##
##MS_PolicyTsqlExecutionLogin##

These principal accounts do not have passwords that can be changed by administrators as they are based on certificates issued to Microsoft. (1)

MSSQL Pen.Test Remediation

Default Logins

NT Authority\SYSTEM

In earlier versions of SQL Server, NT AUTHORITY\SYSTEM was granted the sysadmin role. This is no longer recommended (2) as it is considered a shared account.

For AlwaysOn, the NT Authority\SYSTEM login account should have the following permssions.

(these are granted by default but may have been revoked)

Alter Any Availability Group
Connect SQL
View server state

MSSQL Availability

NT SERVICE\MSSQLSERVER

This is a "service" rather than an "account". It has a security identifier (SID) in Windows, but Windows knows it isn't a real user. Windows can authenticate it, but it doesn't have a password that any human can use.

Must have the "sysadmin" role.

NT SERVICE\SQLSERVERAGENT

This is a "service" rather than an "account". It has a security identifier (SID) in Windows, but Windows knows it isn't a real user. Windows can authenticate it, but it doesn't have a password that any human can use.

Must have the "sysadmin" role.

MSSQL Server Agent

NT SERVICE\Winmgmt (3)

WMF

This is a "service" rather than an "account". It has a security identifier (SID) in Windows, but Windows knows it isn't a real user. Windows can authenticate it, but it doesn't have a password that any human can use.

SQL Server setup adds this to sysadmin role but, theoretically, it only needs:

Membership of db_ddladmin or db_owner roles in msdb
CREATE DDL EVENT NOTIFICATION permission in the server
CREATE TRACE EVENT NOTIFICATION permission in the Database Engine
VIEW ANY DATABASE server-level permission

SQL Server setup creates a SQL WMI namespace and grants read permission to the SQL Server Agent Service-SID.

NT SERVICE\SQLWriter (4)

VSS

This is a "service" rather than an "account". It has a security identifier (SID) in Windows, but Windows knows it isn't a real user. Windows can authenticate it, but it doesn't have a password that any human can use.

Theoretically, allows the SQL Writer process to run at a lower privilege level in an account designated as no login, which limits vulnerability. But, the SQL Writer service needs sysadmin permissions in SQL Server because in the course of its activities it briefly freezes all I/O for the instance of SQL Server. (5)

Bibliography & References

(1) https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/principals-database-engine?view=sql-server-ver15https://solutioncenter.apexsql.com/how-to-script-sql-server-logins-and-permissions/(2) https://www.stigviewer.com/stig/ms_sql_server_2016_instance/2018-03-09/finding/V-79129https://stackoverflow.com/questions/20998/cannot-add-a-sql-server-login(3) https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/configure-windows-service-accounts-and-permissions?view=sql-server-2017#WMI(4) https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/configure-windows-service-accounts-and-permissions?view=sql-server-2017#Writer(5) https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/sql-writer-service?view=sql-server-2017#permissions