Monday, April 6, 2020

How to find login information in SQL Server using T-SQL

 

Generating Scripts Using T-SQL Statements

When working with SQL Server, generating scripts to recreate logins, roles, and permissions can be a crucial task. Below are some useful T-SQL scripts that can help automate this process.

Script to Generate Logins

The following script generates the creation scripts for SQL Server logins. Note that the output can return up to 8192 characters per batch.


SELECT 
  'IF (SUSER_ID(' + QUOTENAME(SP.name, '''') + ') IS NULL) BEGIN CREATE LOGIN ' + QUOTENAME(SP.name) +
  CASE 
    WHEN SP.type_desc = 'SQL_LOGIN' THEN 
      ' WITH PASSWORD = ' + CONVERT(NVARCHAR(MAX), SL.password_hash, 1) + ' HASHED, CHECK_EXPIRATION = ' + 
      CASE WHEN SL.is_expiration_checked = 1 THEN 'ON' ELSE 'OFF' END + 
      ', CHECK_POLICY = ' + CASE WHEN SL.is_policy_checked = 1 THEN 'ON,' ELSE 'OFF,' END
    ELSE ' FROM WINDOWS WITH'
  END + 
  ' DEFAULT_DATABASE=[' + SP.default_database_name + '], DEFAULT_LANGUAGE=[' + SP.default_language_name + '] END;' 
  COLLATE SQL_Latin1_General_CP1_CI_AS -- Logins To Be Created --
FROM sys.server_principals AS SP
LEFT JOIN sys.sql_logins AS SL ON SP.principal_id = SL.principal_id
WHERE SP.type IN ('S', 'G', 'U')
  AND SP.name NOT LIKE '##%##'
  AND SP.name NOT LIKE 'NT AUTHORITY%'
  AND SP.name NOT LIKE 'NT SERVICE%'
  AND SP.name <> 'sa';

This script checks for existing logins and creates them if they do not exist, including their password hash and policy settings.

Script to Generate Role Memberships

To recreate server role memberships, use the following script. It generates commands to add logins to their respective server roles.


-- Scripting Out the Role Membership to Be Added
SELECT 
  'EXEC master..sp_addsrvrolemember @loginame = N''' + SL.name + ''', @rolename = N''' + SR.name + ''''
  AS -- Server Roles the Logins Need to be Added --
FROM master.sys.server_role_members SRM
JOIN master.sys.server_principals SR ON SR.principal_id = SRM.role_principal_id
JOIN master.sys.server_principals SL ON SL.principal_id = SRM.member_principal_id
WHERE SL.type IN ('S', 'G', 'U')
  AND SL.name NOT LIKE '##%##'
  AND SL.name NOT LIKE 'NT AUTHORITY%'
  AND SL.name NOT LIKE 'NT SERVICE%'
  AND SL.name <> 'sa';

This script will generate the necessary commands to restore server role memberships for the users.

Script to Generate Permissions

Finally, to script out the permissions granted at the server level, you can use the following:


-- Scripting out the Permissions to Be Granted
SELECT 
  CASE WHEN SrvPerm.state_desc <> 'GRANT_WITH_GRANT_OPTION' THEN SrvPerm.state_desc 
       ELSE 'GRANT' 
  END + ' ' + SrvPerm.permission_name + ' TO [' + SP.name + ']'
  + CASE WHEN SrvPerm.state_desc <> 'GRANT_WITH_GRANT_OPTION' THEN '' 
         ELSE ' WITH GRANT OPTION' END 
  COLLATE database_default AS -- Server Level Permissions to Be Granted -- 
FROM sys.server_permissions AS SrvPerm 
JOIN sys.server_principals AS SP ON SrvPerm.grantee_principal_id = SP.principal_id 
WHERE SP.type IN ('S', 'U', 'G') 
  AND SP.name NOT LIKE '##%##' 
  AND SP.name NOT LIKE 'NT AUTHORITY%' 
  AND SP.name NOT LIKE 'NT SERVICE%' 
  AND SP.name <> 'sa';

This query lists all server-level permissions, including those granted with the grant option, for the relevant principals.

By using these scripts, you can effectively generate T-SQL scripts to migrate or backup SQL Server security configurations such as logins, role memberships, and permissions.

at

Popular Posts