Embedded Files
Logins are created at the server level, while Users are created at the database level. In other words, a login allows you to connect to the SQL Server service (also called an instance), and permissions inside the database are granted to the database users, not the logins. The logins will be assigned to server Roles (for example, serveradmin) and the database Users will be assigned to Roles within that database (eg. db_datareader, db_bckupoperator).
Logins must be mapped to a database User to connect to a database. If your login is not mapped to any database user, you can still connect to SQL Server instance using SQL Server Management Studio (SSMS), but you’re not allowed to access any objects in the database. To access any objects in the database, you must have a login that’s mapped to a user in the database, and that user must be granted appropriate rights in the database.
The database user does not have credentials of its own and relies on the server login for authentication. This means that there is no password associated with a database user.
https://www.top-password.com/blog/difference-between-sql-server-logins-and-database-users/ (emphasis is mine)
Principals
Users, Logins and Roles are collectively known as "Principals". Logins are SQL Server-level principals. Database Users and Roles are Database-level principals.Principals
SQL Server-level Principals
SQL Server-level Principals
SQL Server authentication Login
Windows authentication login for a Windows user
Windows authentication login for a Windows group
Azure Active Directory authentication login for a AD user
Azure Active Directory authentication login for a AD group
Server Role (e.g. sysadmin)
Database-level Principals
Database-level Principals
Database User
Database Role
Application Role
Report
Report
Using the output of Script 1 with Script 2 we can reverse engineer the SQL for LOGINS, USERS, GRANTS, and ROLES
Script 1
Script 1
Run this query...
SET nocount on
SELECT 'set nocount on'
SELECT 'use ' + name + '; exec ##SP_LOGINUSER'
FROM sys.databases
WHERE name NOT IN('model', 'msdb')
ORDER BY database_id
Paste the results of the above query into the script below, after the final GO (as indicated), before running it...
Script 2
Script 2
Run this script...
CREATE OR ALTER PROCEDURE #SP_LOGINUSER
AS
DECLARE @UserName sysname
SELECT t.p,
t.obj_name
FROM (SELECT 'use [' + DB_NAME() +'];' AS p,
NULL AS obj_name,
1 AS sort
UNION ALL
SELECT 'CREATE LOGIN [' + name +'] FROM WINDOWS WITH DEFAULT_DATABASE=[' + dbname + '];',
NULL AS obj_name,
11 AS sort
FROM sys.syslogins l
WHERE db_name() = 'master'
AND l.[name] NOT LIKE '##%'
UNION ALL
SELECT 'CREATE USER [' + l.name + '] FOR LOGIN [' + l.name + '];' ,
NULL AS obj_name,
20 AS sort
FROM sys.sysusers U
JOIN sys.syslogins l ON u.sid = l.sid
WHERE islogin=1
AND l.[name] NOT IN ('dbo','guest','INFORMATION_SCHEMA','sys')
AND l.[name] LIKE ISNULL(@UserName,'%')
AND l.[name] NOT LIKE '##%'
UNION ALL
SELECT CASE dperms.state_desc
WHEN 'GRANT_WITH_GRANT_OPTION' THEN 'GRANT'
ELSE state_desc
END +
' ' + permission_name +
CASE
WHEN dperms.class = 0 THEN ''
ELSE ' ON '
END +
CASE dperms.class
WHEN 0 THEN ''--'[' + DB_NAME() + ']'
WHEN 1 THEN CASE dperms.minor_id
WHEN 0 THEN '[' + sch.[name] + '].[' + obj.[name] + ']'
ELSE '[' + sch.[name] + '].[' + obj.[name] + '] ([' + col.[name] + '])'
END
WHEN 3 THEN 'SCHEMA::[' + SCHEMA_NAME(major_id) + ']'
WHEN 4 THEN '[' + USER_NAME(major_id) + ']'
WHEN 24 THEN '[' + symm.[name] + ']'
WHEN 25 THEN '[' + certs.[name] + ']'
WHEN 26 THEN '[' + asymm.[name] +']'
END +
' TO [' + dprins.[name] + ']' +
CASE dperms.state_desc
WHEN 'GRANT_WITH_GRANT_OPTION' THEN ' WITH GRANT OPTION;'
ELSE ';'
END COLLATE database_default AS P,
'[' + sch.[name] + '].[' + obj.[name] + ']' AS obj_name,
30 AS sort
FROM sys.database_permissions dperms
INNER JOIN sys.database_principals dprins ON dperms.grantee_principal_id = dprins.principal_id
INNER JOIN sys.sysusers u ON u.name = dprins.name
INNER JOIN sys.syslogins l ON u.sid=l.sid
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'
AND dprins.[name] NOT LIKE '##%'
AND l.[name] LIKE ISNULL(@UserName,'%')
UNION ALL
SELECT 'EXEC sp_addrolemember '''+p.name+''','+'['+l.name+']'+' ;' AS p,
NULL AS obj_name,
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
JOIN sys.sysusers u ON u.name = m.name
JOIN sys.syslogins l ON u.sid=l.sid
WHERE m.[name] NOT LIKE 'dbo'
AND l.[name] LIKE ISNULL(@UserName,'%')
UNION ALL
SELECT 'USE [master]; GRANT ' + pe.permission_name + ' TO [' + pr.name + '];' AS p,
NULL AS obj_name,
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] NOT LIKE '##%'
AND pr.[name] LIKE ISNULL(@UserName,'%')
AND pr.[name] NOT IN ('dbo', 'public', 'NT AUTHORITY\SYSTEM')
AND pe.[permission_name] <> 'CONNECT SQL'
AND db_name() = 'master'
UNION ALL
SELECT 'USE [master]; EXECUTE master..sp_addsrvrolemember N'+''''+P.name+''''+',N'+''''+R.name+''''+';',
NULL AS obj_name,
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 ASC
IF @@trancount > 0 rollback
RETURN 0
GO
*** PASTE THE RESULTS OF Script 1 HERE Before running Script 2 ***
Alternate Script
Alternate Script
This script is simpler, but doesn't show such complete information as the pair above...
It is included here as I would like to create a new script combining the best features of both approaches in an effort to make a script that is easier to use.DECLARE @dbname VARCHAR(50)
DECLARE @statement NVARCHAR(MAX)
DECLARE db_cursor CURSOR LOCAL FAST_FORWARD FOR
SELECT name
FROM MASTER.sys.databases
WHERE name NOT IN ('master','msdb','model','tempdb')
AND state_desc='online'
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @statement = 'USE '+@dbname +';'+ 'SELECT ServerName=@@servername, dbname=db_name(db_id()),p.name AS UserName, p.type_desc AS TypeOfLogin, pp.name AS PermissionLevel, pp.type_desc AS TypeOfRole
FROM sys.database_role_members roles
JOIN sys.database_principals p ON roles.member_principal_id = p.principal_id
JOIN sys.database_principals pp ON roles.role_principal_id = pp.principal_id'
EXEC sp_executesql @statement
FETCH NEXT FROM db_cursor INTO @dbname
END
CLOSE db_cursor
DEALLOCATE db_cursor
SQL Service Account
SQL Service Account
See the Install instructions for further detail.
The MSSQL Service Account name should own the MS-SQL Services
The service account (in case of a local or AD account) and service SID should not be members of the Windows Administrators group.
The SA Login
The SA Login
PCI DSS Requirements v3.2Requirement 2: Do not use vendor-supplied defaults for system passwords and other security parameters2.1 Always change vendor-supplied defaults and remove or disable unnecessary default accounts before installing a system on the network. This applies to ALL default passwords, including but not limited to those used by operating systems, software that provides security services, application and system accounts, point-of-sale (POS) terminals, payment applications, Simple Network Management Protocol (SNMP) community strings, etc.).
The 'sa' account is a widely known account with sysadmin privileges.
To reduce the possibility of an attacker being able to use a brute force attack against this account, you should disable it or rename it
NOTE: By default, the sa login account is disabled at install time when Windows Authentication Mode is selected. If mixed mode (SQL Server and Windows Authentication) is selected at install, the default for the sa login is enabled.
Disable
Disable
Check
Check
SELECT name, is_disabled
FROM sys.server_principals
WHERE sid = 0x01 ;
A value of 0 indicates the login account is currently enabled.A value of 1 indicates the login account is currently disabledDisable
Disable
USE [master]
GO
DECLARE @tsql nvarchar(max)
SET @tsql = 'ALTER LOGIN ' + SUSER_NAME(0x01) + ' DISABLE'
EXEC (@tsql)
GO
Rename
Rename
NOTE: Renaming the 'sa' account might cause issues during Upgrades and Service Pack installs. It may be necessary to temporarily backout this change in that situation.
Check
Check
SELECT name
FROM sys.server_principals
WHERE sid = 0x01;
Rename
Rename
ALTER LOGIN sa WITH NAME = <different_user>;
Reset Password
Reset Password
From a command prompt (run as Administrator) on the SQL Server...
net stop MSSQLSERVER
net start MSSQLSERVER /m
sqlcmd
CREATE LOGIN tempsa WITH PASSWORD = "asecurepassword"
GO
SP_ADDSRVROLEMEMBER tempsa,'SYSADMIN'
GO
exit
net stop MSSQLSERVER
net start MSSQLSERVER
Login to SSMS/sqlcmd/ADS with the new tempsa user. Reset the SA (or other sysadmin) password, and drop the tempsa user
The "/m" flag causes SQL Server to start in Single-User mode.
The above gives the officially documented approach to doing this. However testing suggests this less disruptive approach also works...
From a command prompt (run as Administrator) on the SQL Server...
sqlcmd
CREATE LOGIN tempsa WITH PASSWORD = "asecurepassword"
GO
SP_ADDSRVROLEMEMBER tempsa,'SYSADMIN'
GO
exit
If MSSQL is configured for Windows Authentication only (i.e. you cannot login as SQL SA account), you will need to update the registry e.g...
HKEY_LOCAL_MACHINE - SOFTWARE - Microsoft - Microsoft SQL Server - MSSQL13.SQLEXPRESS - MSSQLServer
Update LoginMode (1=Windows Authentication, 2=Mixed Mode)
SQL Server Active Sessions
SQL Server Active Sessions
EXEC sp_who
EXEC sp_who 'login'
EXEC sp_who @loginame = 'login'
EXEC sp_who sessionID
EXEC sp_who 'ACTIVE'
EXEC sp_who2
Bibliography
Bibliography
https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-login-transact-sql?view=sql-server-ver15https://docs.microsoft.com/en-us/sql/t-sql/statements/create-login-transact-sql?view=sql-server-ver15https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-who-transact-sql?view=sql-server-ver15
https://www.top-password.com/blog/difference-between-sql-server-logins-and-database-users/ (TODO)https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2005/ms191465(v=sql.90)
https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms161959(v=sql.105)?redirectedfrom=MSDNhttps://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008/ms161959(v=sql.100)?redirectedfrom=MSDNhttps://www.sqlserver-dba.com/2016/03/how-to-check-sql-server-password-policy.html
https://www.sqlserver-dba.com/2020/06/how-to-find-list-of-ad-groups-of-a-user-from-sql-server.htmlhttps://www.mssqltips.com/sqlservertip/4679/clone-a-sql-server-login-and-password-to-a-new-server/ https://www.sqlservercentral.com/scripts/script-all-logins-users-and-roles (TODO)https://docs.microsoft.com/en-us/troubleshoot/sql/security/transfer-logins-passwords-between-instanceshttps://www.brentozar.com/blitz/jobs-owned-by-user-accounts/https://docs.microsoft.com/en-us/sql/relational-databases/security/choose-an-authentication-mode
http://whoisactive.com/
Service Accountshttps://learn.microsoft.com/en-us/sql/database-engine/configure-windows/configure-windows-service-accounts-and-permissionshttps://www.sqlservercentral.com/articles/configuring-service-account-privileges-for-sql-serverhttps://dba.stackexchange.com/questions/315980/sql-service-account-group-policy-permissions
Principalshttps://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/principals-database-engine?view=sql-server-ver15
Service Security Principalshttps://learn.microsoft.com/en-us/sql/relational-databases/security/using-service-sids-to-grant-permissions-to-services-in-sql-server
SAhttps://msdn.microsoft.com/en-us/library/ms188786(v=sql.100).aspxhttps://msdn.microsoft.com/en-us/library/ms188786(v=sql.105).aspxhttps://msdn.microsoft.com/en-us/library/ms189828(v=sql.100).aspxhttps://msdn.microsoft.com/en-us/library/ms189828(v=sql.105).aspxhttps://www.wikihow.com/Reset-SA-Password-in-SQL-Serverhttps://www.brentozar.com/archive/2016/01/how-to-talk-people-out-of-the-sa-account/https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/connect-to-sql-server-when-system-administrators-are-locked-out
Page updated
Google Sites
Report abuse