Secure Orphan UserS AutoFix

When you restore a database on a different SQL Server, all SQL users become a "orphaned user" because the SQL logins have a different SID then on the source server.
In SQL Server 2005 you can fix this orphaned user with the stored procedure "sys.sp_change_users_login", but since version 2008 this SP is deprecated.
This Transact-SQL statement also links orphaned user to existing principals with same name.
In regard of security this script don't add a new principal if none with same name was found; as the SP with option AUTO_FIX do.
Works with SQL Server 2005 and higher versions in all editions.
Requires ALTER ANY USER permissions.
Links:
sys.sp_change_users_login: http://msdn.microsoft.com/en-us/library/ms174378.aspx
ALTER USER: http://msdn.microsoft.com/en-us/library/ms176060.aspx

 

 

 

 

SQL

Skript bearbeiten|Remove

-- Secure Orphaned User AutoFix 
DECLARE @autoFix bit; 
SET @autoFix = 'FALSE';  -- FALSE = Report only those user who could be auto fixed. 
                         -- TRUE  = Report and fix !!! 
 
DECLARE @user sysname, @principal sysname, @sql nvarchar(500), @found int, @fixed int; 
 
DECLARE orphans CURSOR LOCAL FOR 
    SELECT QUOTENAME(SU.[name]) AS UserName 
          ,QUOTENAME(SP.[name]) AS PrincipalName 
    FROM sys.sysusers AS SU 
         LEFT JOIN sys.server_principals AS SP 
             ON SU.[name] = SP.[name] 
                AND SP.[type] = 'S' 
    WHERE SU.issqluser = 1          -- Only SQL logins 
          AND NOT SU.[sid] IS NULL  -- Exclude system user 
          AND SU.[sid] <> 0x0       -- Exclude guest account 
          AND LEN(SU.[sid]) <= 16   -- Exclude Windows accounts & roles 
          AND SUSER_SNAME(SU.[sid]) IS NULL  -- Login for SID is null 
    ORDER BY SU.[name]; 
 
SET @found = 0; 
SET @fixed = 0; 
OPEN orphans; 
FETCH NEXT FROM orphans 
    INTO @user, @principal; 
WHILE @@FETCH_STATUS = 0 
BEGIN 
    IF @principal IS NULL 
        PRINT N'Orphan: ' + @user; 
    ELSE 
    BEGIN 
        PRINT N'Orphan: ' + @user + N' => Autofix possible, principal with same name found!'; 
        IF @autoFix = 'TRUE' 
        BEGIN 
            -- Build the DDL statement dynamically. 
            SET @sql = N'ALTER USER ' + @user + N' WITH LOGIN = ' + @principal + N';'; 
            EXEC sp_executesql @sql; 
            PRINT N'        ' + @user + N' is auto fixed.'; 
            SET @fixed = @fixed + 1; 
        END 
    END 
    SET @found = @found + 1; 
     
    FETCH NEXT FROM orphans 
        INTO @user, @principal; 
END; 
 
CLOSE orphans; 
DEALLOCATE orphans; 
 
PRINT ''; 
PRINT CONVERT(nvarchar(15), @found) + N' orphan(s) found, ' 
    + CONVERT(nvarchar(15), @fixed) + N' orphan(s) fixed.';

Send an Alert thru email on number of User Connections

IF (SELECT cntr_value FROM sys.dm_os_performance_counters WHERE counter_name='User Connections')>0
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'MyProfile',
@recipients = 'Mymai@MyMailcorp.com',
--@body = @bodyText,
@body_format = 'TEXT',
@subject = 'Testing Email' ;

Query to find total number of logical reads/physical reads done, when a given stored procedure is executed

SELECT ss.sum_execution_count

,t.TEXT

,ss.sum_total_elapsed_time

,ss.sum_total_worker_time

,ss.sum_total_logical_reads

,ss.sum_total_logical_writes

FROM (SELECT s.plan_handle

,SUM(s.execution_count) sum_execution_count

,SUM(s.total_elapsed_time) sum_total_elapsed_time

,SUM(s.total_worker_time) sum_total_worker_time

,SUM(s.total_logical_reads) sum_total_logical_reads

,SUM(s.total_logical_writes) sum_total_logical_writes

FROM sys.dm_exec_query_stats s

GROUP BY s.plan_handle

) AS ss

CROSS APPLY sys.dm_exec_sql_text(ss.plan_handle) t

WHERE t.TEXT LIKE '%PROCEDURE NAME HEREt%'

ORDER BY sum_total_logical_reads DESC



If you want to collect this data for a specific execution, you need to save the data before execution into a table, and then after execution read the DMV again to compute the delta. A presumptions is that there are no other executions of the procedure at the same time.

Rather than using dm.sys_exec_query_stats, you can use dm.sys_exec_procedure_stats, so that you get values on procedure level instead rather than on query level.

Changing the font color of a row dynamically using sp_send_dbmail

declare @tableHTML nvarchar(max)
SET @tableHTML =
N'<H1>Test Data</H1>' +
N'<H4>TEST_sub_header</H4>' +
N'<H4>TEST_header_1</H4>' +
N'<table border="1">' +
N'<tr><th>place</th><th>Temperature</th>' +
N'</tr>' +
CAST ( ( select case when Temperature>40 then 'red' else 'black' end as "font/@color",
place as "font/td",'',
case when Temperature>40 then 'red' else 'black' end as "font/@color",
Temperature as "font/td"
from test_fontcolor
order by place
for xml path('tr')
) AS NVARCHAR(MAX) ) +
N'</table>' ;

EXEC msdb.dbo.sp_send_dbmail
@profile_name='test',
@recipients='test@microsoft.com',
@from_address = 'storenote@microsoft.com',
@subject = 'Test Font Color',
@body = @tableHTML,
@body_format = 'HTML' ;

Enable or Disable Logins with Stored Procedure

create procedure toggle_login (@login_name nvarchar(125), @set_enable bit)
as
begin
declare @sql_command nvarchar(500)
set @sql_command = 'ALTER LOGIN [' + @login_name + '] ' + case when @set_enable = 1 then 'ENABLE' else 'DISABLE' end
print @sql_command
exec sp_executesql @sql_command
end

 
--usage of above stored procedure

exec toggle_login 'domain\loginname,0