Script Generates A script to Create all Logins, Server Roles in SQL Server

This script is very useful if you are building the new server and want to replicate SQL Server security from another server.

This script originally authors by Greg Ryan, I have dome modification to only capture online databases.


/******************************************************************************/
SET NOCOUNT ON
DECLARE
        @sql nvarchar(max)
,       @Line int = 1
,       @max int = 0
,       @@CurDB nvarchar(100) = ''

CREATE TABLE #SQL
       (
        Idx int IDENTITY
       ,xSQL nvarchar(max)
       )

INSERT INTO #SQL
        ( xSQL
        )
        SELECT
                'IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'''
                + QUOTENAME(name) + ''')
' + '                CREATE LOGIN ' + QUOTENAME(name) + ' WITH PASSWORD='
                + sys.fn_varbintohexstr(password_hash) + ' HASHED, SID='
                + sys.fn_varbintohexstr(sid) + ', ' + 'DEFAULT_DATABASE='
                + QUOTENAME(COALESCE(default_database_name , 'master'))
                + ', DEFAULT_LANGUAGE='
                + QUOTENAME(COALESCE(default_language_name , 'us_english'))
                + ', CHECK_EXPIRATION=' + CASE is_expiration_checked
                                            WHEN 1 THEN 'ON'
                                            ELSE 'OFF'
                                          END + ', CHECK_POLICY='
                + CASE is_policy_checked
                    WHEN 1 THEN 'ON'
                    ELSE 'OFF'
                  END + '
Go

'
            FROM
                sys.sql_logins
            WHERE
                name <> 'sa'

INSERT INTO #SQL
        ( xSQL
        )
        SELECT
                'IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'''
                + QUOTENAME(name) + ''')
' + '                CREATE LOGIN ' + QUOTENAME(name) + ' FROM WINDOWS WITH '
                + 'DEFAULT_DATABASE='
                + QUOTENAME(COALESCE(default_database_name , 'master'))
                + ', DEFAULT_LANGUAGE='
                + QUOTENAME(COALESCE(default_language_name , 'us_english'))
                + ';
Go

'
            FROM
                sys.server_principals
            WHERE
                type IN ( 'U' , 'G' )
                AND name NOT IN ( 'BUILTIN\Administrators' ,
                                  'NT AUTHORITY\SYSTEM' );
                                 
PRINT '/*****************************************************************************************/'
PRINT '/*************************************** Create Logins ***********************************/'
PRINT '/*****************************************************************************************/'
SELECT
        @Max = MAX(idx)
    FROM
        #SQL
WHILE @Line <= @max
      BEGIN

            SELECT
                    @sql = xSql
                FROM
                    #SQL AS s
                WHERE
                    idx = @Line
            PRINT @sql

            SET @line = @line + 1
       
      END
DROP TABLE #SQL

CREATE TABLE #SQL2
       (
        Idx int IDENTITY
       ,xSQL nvarchar(max)
       )

INSERT INTO #SQL2
        ( xSQL
        )
        SELECT
                'EXEC sp_addsrvrolemember ' + QUOTENAME(L.name) + ', '
                + QUOTENAME(R.name) + ';
GO

'
            FROM
                sys.server_principals L
            JOIN sys.server_role_members RM
            ON  L.principal_id = RM.member_principal_id
            JOIN sys.server_principals R
            ON  RM.role_principal_id = R.principal_id
            WHERE
                L.type IN ( 'U' , 'G' , 'S' )
                AND L.name NOT IN ( 'BUILTIN\Administrators' ,
                                    'NT AUTHORITY\SYSTEM' , 'sa' );


PRINT '/*****************************************************************************************/'
PRINT '/******************************Add Server Role Members     *******************************/'
PRINT '/*****************************************************************************************/'
SELECT
        @Max = MAX(idx)
    FROM
        #SQL2
SET @line = 1
WHILE @Line <= @max
      BEGIN

            SELECT
                    @sql = xSql
                FROM
                    #SQL2 AS s
                WHERE
                    idx = @Line
            PRINT @sql

            SET @line = @line + 1
       
      END
DROP TABLE #SQL2

PRINT '/*****************************************************************************************/'
PRINT '/*****************Add User and Roles membership to Indivdual Databases********************/'
PRINT '/*****************************************************************************************/'


--Drop Table #Db
CREATE TABLE #Db
       (
        idx int IDENTITY
       ,DBName nvarchar(100)
       );

INSERT INTO #Db
        SELECT
                name
            FROM
                master.sys.databases
                                                                                                where state_desc = 'ONLINE'
                                                                                                and name NOT IN ( 'Master' , 'Model' , 'msdb' , 'tempdb' )
            ORDER BY
                name;
SELECT
        @Max = MAX(idx)
    FROM
        #Db
SET @line = 1
--Select * from #Db
--Exec sp_executesql @SQL

WHILE @line <= @Max
      BEGIN
            SELECT
                    @@CurDB = DBName
                FROM
                    #Db
                WHERE
                    idx = @line

            SET @SQL = 'Use ' + @@CurDB + '

Declare  @@Script NVarChar(4000) = ''''
DECLARE cur CURSOR FOR

Select  ''Use ' + @@CurDB + ';
Go
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'''''' +
                mp.[name] + '''''')
CREATE USER ['' + mp.[name] + ''] FOR LOGIN ['' +mp.[name] + ''] WITH DEFAULT_SCHEMA=[dbo]; ''+ CHAR(13)+CHAR(10) +
''GO'' + CHAR(13)+CHAR(10) +

''EXEC sp_addrolemember N'''''' + rp.name + '''''', N''''['' + mp.[name] + '']'''';
Go'' 
FROM sys.database_role_members a
INNER JOIN sys.database_principals rp ON rp.principal_id = a.role_principal_id
INNER JOIN sys.database_principals AS mp ON mp.principal_id = a.member_principal_id


OPEN cur

FETCH NEXT FROM cur INTO @@Script;
WHILE @@FETCH_STATUS = 0
BEGIN  
PRINT @@Script
FETCH NEXT FROM cur INTO @@Script;
END

CLOSE cur;
DEALLOCATE cur;';                                                                                                                                                                                                                  
--Print @SQL
Exec sp_executesql @SQL;
--Set @@Script = ''
            SET @Line = @Line + 1

      END

DROP TABLE #Db
/******************************************************************************/

Query to find IP, port , connection details of SQL server

SELECT  @@servername as ServerName,
   CONNECTIONPROPERTY('net_transport') AS net_transport,
   CONNECTIONPROPERTY('protocol_type') AS protocol_type,
   CONNECTIONPROPERTY('auth_scheme') AS auth_scheme,
   CONNECTIONPROPERTY('local_net_address') AS local_net_address,
   CONNECTIONPROPERTY('local_tcp_port') AS local_tcp_port,
   CONNECTIONPROPERTY('client_net_address') AS client_net_address

List all the foreign keys referencing a database in SQL Server

SELECT  obj.name AS FK_NAME,   sch.name AS [schema_name],
    tab1.name AS [table],  col1.name AS [column],
    tab2.name AS [referenced_table],col2.name AS [referenced_column]
FROM sys.foreign_key_columns fkc
INNER JOIN sys.objects obj     ON obj.object_id = fkc.constraint_object_id
INNER JOIN sys.tables tab1     ON tab1.object_id = fkc.parent_object_id
INNER JOIN sys.schemas sch     ON tab1.schema_id = sch.schema_id
INNER JOIN sys.columns col1     ON col1.column_id = parent_column_id AND col1.object_id = tab1.object_id
INNER JOIN sys.tables tab2     ON tab2.object_id = fkc.referenced_object_id
INNER JOIN sys.columns col2     ON col2.column_id = referenced_column_id AND col2.object_id = tab2.object_id

T-SQL Script to know current sessions status in SQL Server

Use the below query to get the current SQL Server status with blocking and high CPU ordered, Use the host-name and database name to drill down.

SELECT s.session_id
    ,r.STATUS
    ,r.blocking_session_id 'blocked by'
    ,r.wait_type
    ,wait_resource
    ,r.wait_time / (1000.0) 'Wait Time (in Sec)'
    ,r.cpu_time
    ,r.logical_reads
    ,r.reads
    ,r.writes
    ,r.total_elapsed_time / (1000.0) 'Elapsed Time (in Sec)'
    ,Substring(st.TEXT, (r.statement_start_offset / 2) + 1, (
            (
                CASE r.statement_end_offset
                    WHEN - 1
                        THEN Datalength(st.TEXT)
                    ELSE r.statement_end_offset
                    END - r.statement_start_offset
                ) / 2
            ) + 1) AS statement_text
    ,Coalesce(Quotename(Db_name(st.dbid)) + N'.' + Quotename(Object_schema_name(st.objectid, st.dbid)) + N'.' +
     Quotename(Object_name(st.objectid, st.dbid)), '') AS command_text
    ,r.command
    ,s.login_name
    ,s.host_name
    ,s.program_name
    ,s.host_process_id
    ,s.last_request_end_time
    ,s.login_time
    ,r.open_transaction_count
FROM sys.dm_exec_sessions AS s
INNER JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id
cross APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
WHERE r.session_id != @@SPID 
--and s.host_name in ('SUPERMAN','BATMAN') 
--and dbid=DB_ID('DBAAdmin')
--and       r.cpu_time > 1000000
ORDER BY r.cpu_time DESC
    ,r.STATUS
    ,s.session_id

How to display execution plans present in procedure cache in SQL Server

SELECT cp.objtype AS PlanType,
       OBJECT_NAME(st.objectid,st.dbid) AS ObjectName,
       cp.refcounts AS ReferenceCounts,cp.usecounts AS UseCounts,
       st.text AS SQLBatch,qp.query_plan AS QueryPlan
    FROM sys.dm_exec_cached_plans AS cp
    CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
    CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st;

    GO