MSSQL Logins

Check

SELECT name

FROM sys.syslogins

Create Login

SSMS

Security - Logins - right-click - New Login...

TODO

Clone Login Definition

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.

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

Bibliography & References