MS-SQL Users, Logins, Schemas & Roles

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.

SQL Server-level Principals

Database-level Principals

Report

Using the output of Script 1 with Script 2 we can reverse engineer the SQL for LOGINS, USERS, GRANTS, and ROLES

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

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

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

See the Install instructions for further detail.

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.).

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

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 disabled

Disable

USE [master] 

GO 

DECLARE @tsql nvarchar(max) 

SET @tsql = 'ALTER LOGIN ' + SUSER_NAME(0x01) + ' DISABLE' 

EXEC (@tsql) 

GO

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

SELECT name 

FROM sys.server_principals 

WHERE sid = 0x01;

Rename

ALTER LOGIN sa WITH NAME = <different_user>; 

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.

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


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/
Principalshttps://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/principals-database-engine?view=sql-server-ver15
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