SQL Talk

Table Partitioning in SQL Server

<div><div dir="ltr" style="text-align: left;" trbidi="on">
* It is a new feature introduced in SQL Server 2005.<br />
* While working with large tables, generally we face the following problems<br />
             * Less performance.<br />
             * Pressure increases on the hardware where the table is created.<br />
             * Index size also increases.<br />
             * We have to rebuild or reorganize the complete index always.<br />
<div closure_uid_j94rvt="104">
             * To overcome all these problems we can divide the table horizontally into more than on  part.       </div>
<div closure_uid_j94rvt="104">
                This process is called table partitioning.<br />
* Dividing the table into segments horizontally is table partitioning.<br />
* Each part is called partition. All the partitions consists of same structure.<br />
* Each partition can be stored in different files/filegroups.<br />
* One of a performance improvement technique.<br />
* We can split the table into parts depending on some column values.</div>
<div closure_uid_j94rvt="104">
<br /></div>
<div closure_uid_j94rvt="104">
Steps</div>
<div closure_uid_j94rvt="104">
1) Define a partition function<br />
 * Here we will mention the ranges of partitions.<br />
 syn:<br />
 CREATE PARTITION FUNCTION <function_Name> (data type)<br />
 AS RANGE LEFT/RIGHT FOR VALUES (V1,V2,....)  </div>
<div closure_uid_j94rvt="104">
 * Here, data type is the column's type which is used to implement partitions.<br />
  i.e. partition key data type.</div>
<div closure_uid_j94rvt="104">
 LEFT:<br />
 Specifies that partition values will be less than or equal to the values defined in the Partition Function.</div>
<div closure_uid_j94rvt="104">
 RIGHT:<br />
 Specifies that partition values will be less than the values defined in the Partition Function.</div>
<div closure_uid_j94rvt="104">
 EX:</div>
<div closure_uid_j94rvt="104">
 CREATE PARTITION FUNCTION MyPartitionRange (INT)<br />
 AS RANGE LEFT FOR VALUES (1,2)  </div>
<div closure_uid_j94rvt="104">
 This functions allows to create 3 partitions.<br />
 First partition consists of values  <=1<br />
 Sec        >1 and <=2<br />
 Third        >2</div>
<div closure_uid_j94rvt="104">
2) Define a partition scheme from the partition function<br />
<br />
 * It defines the locations for partitions. i.e. which partition should be maintain in which file group.<br />
 * We can maintain each partition in a seperate filegroup or all partitions in a single file group.</div>
<div closure_uid_j94rvt="104">
 syn:<br />
<br />
 CREATE PARTITION SCHEME <name> AS<br />
 PARTITION <partition_functionname><br />
 ALL TO (fg1,fg2,.........)</div>
<div closure_uid_j94rvt="104">
3) Create the partition Table</div>
<div closure_uid_j94rvt="104">
 CREATE TABLE <tname><br />
        (<br />
        colname type,<br />
               PartCol type<br />
        )<br />
 ON<br />
 <partition_schemename> (PartCol)<br />
EX:<br />
Step1: Creating partition Function.</div>
<div closure_uid_j94rvt="104">
CREATE PARTITION FUNCTION MyPartitionRange (INT)<br />
AS RANGE LEFT FOR VALUES (1,2)  </div>
<div closure_uid_j94rvt="104">
Step2: Creating partition scheme.</div>
<div closure_uid_j94rvt="104">
CREATE PARTITION SCHEME MyPartitionScheme AS<br />
PARTITION MyPartitionRange<br />
ALL TO ([PRIMARY])</div>
<div closure_uid_j94rvt="104">
Step3: Creating partition table</div>
<div closure_uid_j94rvt="104">
CREATE TABLE MyPartitionedTable<br />
       (<br />
       i INT ,<br />
       s CHAR(8000) ,<br />
       PartCol INT<br />
       )<br />
 ON<br />
 MyPartitionScheme (PartCol)</div>
<div closure_uid_j94rvt="104">
Step4: To display partition details</div>
<div closure_uid_j94rvt="104">
SELECT * FROM sys.partitions<br />
WHERE OBJECT_ID = OBJECT_ID('MyPartitionedTable')</div>
<div closure_uid_j94rvt="104">
Step5: Inserting sample rows</div>
<div closure_uid_j94rvt="104">
INSERT MyPartitionedTable (i, s, PartCol) SELECT 1, 'a', 1<br />
INSERT MyPartitionedTable (i, s, PartCol) SELECT 2, 'a', 2<br />
INSERT MyPartitionedTable (i, s, PartCol) SELECT 3, 'a', 2<br />
INSERT MyPartitionedTable (i, s, PartCol) SELECT 4, 'a', 3<br />
INSERT MyPartitionedTable (i, s, PartCol) SELECT 5, 'a', 3<br />
INSERT MyPartitionedTable (i, s, PartCol) SELECT 6, 'a', 3<br />
INSERT MyPartitionedTable (i, s, PartCol) SELECT 7, 'a', 4</div>
<div closure_uid_j94rvt="104">
Step6: Verifying the partitions</div>
<div closure_uid_j94rvt="104">
SELECT *<br />
FROM sys.partitions<br />
WHERE OBJECT_ID = OBJECT_ID('MyPartitionedTable')</div>
<div closure_uid_j94rvt="104">
Step7: To find the given value falls in which partition</div>
<div closure_uid_j94rvt="104">
SELECT PartitionNo = $partition.MyPartitionRange(6)</div>
<div closure_uid_j94rvt="104">
<br /></div>
<div closure_uid_j94rvt="104">
What Operations Can Be Performed on Partitioned Data<br />
---------------------------------------------------------------------<br />
1. SPLIT: Insert a boundary in an existing partition to create a new partition.</div>
<div closure_uid_j94rvt="104">
 * If the size of a partition becomes very large then we can split it into two           partitions.<br />
 syn:<br />
 ALTER PARTITION FUNCTION <function_name>() split RANGE (value)<br />
 Ex:<br />
 ALTER PARTITION FUNCTION MyPartitionRange () split RANGE (3)</div>
<div closure_uid_j94rvt="104">
 select *<br />
 FROM sys.partitions<br />
 WHERE OBJECT_ID = OBJECT_ID('MyPartitionedTable')</div>
<div closure_uid_j94rvt="104">
<br /></div>
<div closure_uid_j94rvt="104">
2. SWITCH: Swap a populated table or partition with an empty table or partition.<br />
--------<br />
Step1:</div>
<div closure_uid_j94rvt="104">
select * into MyNewPartition from MyPartitionedTable where 1=3</div>
<div closure_uid_j94rvt="104">
--copying rows present in 3rd partition into MyNewPartition</div>
<div closure_uid_j94rvt="104">
ALTER TABLE MyPartitionedTable switch PARTITION 3 TO MyNewPartition</div>
<div closure_uid_j94rvt="104">
--STEP3:<br />
select * from MyNewPartition</div>
<div closure_uid_j94rvt="104">
--step4: Now 3rd partition becomes empty.</div>
<div closure_uid_j94rvt="104">
SELECT *<br />
FROM sys.partitions<br />
WHERE OBJECT_ID = OBJECT_ID('MyPartitionedTable')</div>
<div closure_uid_j94rvt="104">
<br /></div>
<div closure_uid_j94rvt="104">
--Copying Rows present in a table into required partion</div>
<div closure_uid_j94rvt="104">
Step1:</div>
<div closure_uid_j94rvt="104">
select * into MyNewPartition from MyPartitionedTable where 1=3<br />
<br />
Step2: Copy rows present in non-partitioned table into 4th partition</div>
<div closure_uid_j94rvt="104">
ALTER TABLE MyNewPartition switch TO MyPartitionedTable PARTITION 4</div>
<div closure_uid_j94rvt="104">
Note1:<br />
 * MyNewPartition should consists of check constraint which allows the respective       partition range.<br />
 * It should be there on same filegroup where the respective partition is present.</div>
<div closure_uid_j94rvt="104">
<br />
select * from mynewpartition</div>
<div closure_uid_j94rvt="104">
<br />
ALTER TABLE OrdersOctober2004 SWITCH TO Orders PARTITION 24<br />
GO</div>
<div closure_uid_j94rvt="104">
<br />
3. MERGE: Combine two adjacent partitions into a single partition.<br />
---------------------------------------------------------------</div>
<div closure_uid_j94rvt="104">
 * If the no of rows in the partitions are less then if we want then we can merge   into single partition.</div>
<div closure_uid_j94rvt="104">
CREATE PARTITION FUNCTION myRangePF1 (int)<br />
AS RANGE LEFT FOR VALUES ( 1, 100, 1000 );<br />
GO</div>
<div closure_uid_j94rvt="104">
ALTER PARTITION FUNCTION myRangePF1 ()<br />
MERGE RANGE (100);</div>
</div>
</div>

SQL Server Errorlogs

<div><div dir="ltr" style="text-align: left;" trbidi="on">
<div class="MsoNoSpacing" style="background-color: white; font-family: Roboto; font-size: 16px;">
* Error Logs maintains events raised by SQL Server database engine or Agent.</div>
<div class="MsoNoSpacing" style="background-color: white; font-family: Roboto; font-size: 16px;">
* Error Logs are main source for troubleshooting SQL Server problems.</div>
<div class="MsoNoSpacing" style="background-color: white; font-family: Roboto; font-size: 16px;">
* SQL Server supports 2 types of error logs</div>
<div class="MsoNoSpacing" style="background-color: white; font-family: Roboto; font-size: 16px;">
<span style="font-weight: 700;">                * SQL Server Logs<o:p></o:p></span></div>
<div class="MsoNoSpacing" style="background-color: white; font-family: Roboto; font-size: 16px;">
<span style="font-weight: 700;">                * SQL Agent Logs<o:p></o:p></span></div>
<div class="MsoNoSpacing" style="background-color: white; font-family: Roboto; font-size: 16px;">
<br /></div>
<div class="MsoNoSpacing" style="background-color: white; font-family: Roboto; font-size: 16px;">
<span style="font-weight: 700;"><u>FAQ : - What is recorded in error logs?<o:p></o:p></u></span></div>
<div class="MsoNoSpacing" style="background-color: white; font-family: Roboto; font-size: 16px;">
<br /></div>
<div class="MsoNoSpacing" style="background-color: white; font-family: Roboto; font-size: 16px; margin-left: 0.5in; text-indent: -0.25in;">
<span style="font-weight: 700;">1.<span style="font-size: 7pt; font-weight: normal;">       </span></span><span style="font-weight: 700;">SQL Server start up events including database recovery.<o:p></o:p></span></div>
<div class="MsoNoSpacing" style="background-color: white; font-family: Roboto; font-size: 16px; margin-left: 0.5in; text-indent: -0.25in;">
<span style="font-weight: 700;">2.<span style="font-size: 7pt; font-weight: normal;">       </span></span><span style="font-weight: 700;">Backup and restore details.<o:p></o:p></span></div>
<div class="MsoNoSpacing" style="background-color: white; font-family: Roboto; font-size: 16px; margin-left: 0.5in; text-indent: -0.25in;">
<span style="font-weight: 700;">3.<span style="font-size: 7pt; font-weight: normal;">       </span></span><span style="font-weight: 700;">Any failed SQL Server jobs<o:p></o:p></span></div>
<div class="MsoNoSpacing" style="background-color: white; font-family: Roboto; font-size: 16px; margin-left: 0.5in; text-indent: -0.25in;">
<span style="font-weight: 700;">4.<span style="font-size: 7pt; font-weight: normal;">       </span></span><span style="font-weight: 700;">User defined error message which has WITH LOG clause.<o:p></o:p></span></div>
<div class="MsoNoSpacing" style="background-color: white; font-family: Roboto; font-size: 16px; margin-left: 0.5in; text-indent: -0.25in;">
<span style="font-weight: 700;">5.<span style="font-size: 7pt; font-weight: normal;">       </span></span><span style="font-weight: 700;">Maintenance related DBCC statements, such as DBCC CHECKDB and DBCC CHECKALLOC.<o:p></o:p></span></div>
<div class="MsoNoSpacing" style="background-color: white; font-family: Roboto; font-size: 16px; margin-left: 0.5in; text-indent: -0.25in;">
<span style="font-weight: 700;">6.<span style="font-size: 7pt; font-weight: normal;">       </span></span><span style="font-weight: 700;">Turning trace flags on or off.<o:p></o:p></span></div>
<div class="MsoNoSpacing" style="background-color: white; font-family: Roboto; font-size: 16px; margin-left: 0.5in; text-indent: -0.25in;">
<span style="font-weight: 700;">7.<span style="font-size: 7pt; font-weight: normal;">       </span></span><span style="font-weight: 700;">SQL Servers usage of a particular session for a long period of time.<o:p></o:p></span></div>
<div class="MsoNoSpacing" style="background-color: white; font-family: Roboto; font-size: 16px; margin-left: 0.5in; text-indent: -0.25in;">
<span style="font-weight: 700;">8.<span style="font-size: 7pt; font-weight: normal;">       </span></span><span style="font-weight: 700;">Starting and stopping Profiler traces<o:p></o:p></span></div>
<div class="MsoNoSpacing" style="background-color: white; font-family: Roboto; font-size: 16px;">
<br /></div>
<div class="MsoNoSpacing" style="background-color: white; font-family: Roboto; font-size: 16px;">
* By default SQL Server supports</div>
<div class="MsoNoSpacing" style="background-color: white; font-family: Roboto; font-size: 16px;">
                1 - Current Log</div>
<div class="MsoNoSpacing" style="background-color: white; font-family: Roboto; font-size: 16px;">
                6 - Archieve Logs</div>
<div class="MsoNoSpacing" style="background-color: white; font-family: Roboto; font-size: 16px;">
* Error logs are present in LOG folder of respective instance.</div>
<div class="MsoNoSpacing" style="background-color: white; font-family: Roboto; font-size: 16px;">
* We can read error logs using</div>
<div class="MsoNoSpacing" style="background-color: white; font-family: Roboto; font-size: 16px;">
<span style="font-weight: 700;">                sp_readerrorlog<o:p></o:p></span></div>
<div class="MsoNoSpacing" style="background-color: white; font-family: Roboto; font-size: 16px;">
<span style="font-weight: 700;">                xp_readerrorlog<o:p></o:p></span></div>
<div class="MsoNoSpacing" style="background-color: white; font-family: Roboto; font-size: 16px;">
* By default when the server was restarted the error logs are recycled automatically. We can recycle error logs using</div>
<div class="MsoNoSpacing" style="background-color: white; font-family: Roboto; font-size: 16px;">
<span style="font-weight: 700;">                sp_cycle_errorlog<o:p></o:p></span></div>
<div class="MsoNoSpacing" style="background-color: white; font-family: Roboto; font-size: 16px;">
* We can configure up to 99 error logs</div>
<div class="MsoNoSpacing" style="background-color: white; font-family: Roboto; font-size: 16px;">
<br /></div>
<div class="MsoNoSpacing" style="background-color: white; font-family: Roboto; font-size: 16px;">
<span style="font-weight: 700;">* How to Configure?<o:p></o:p></span></div>
<div class="MsoNoSpacing" style="background-color: white; font-family: Roboto; font-size: 16px;">
                * Go to Object Explorer</div>
<div class="MsoNoSpacing" style="background-color: white; font-family: Roboto; font-size: 16px;">
                * Management</div>
<div class="MsoNoSpacing" style="background-color: white; font-family: Roboto; font-size: 16px;">
                * R.C on SQL Server Logs</div>
<div class="MsoNoSpacing" style="background-color: white; font-family: Roboto; font-size: 16px;">
                * Configure</div>
<div class="MsoNoSpacing" style="background-color: white; font-family: Roboto; font-size: 16px;">
                * Select checkbox " Limit the no of error logs..................."</div>
<div class="MsoNoSpacing" style="background-color: white; font-family: Roboto; font-size: 16px;">
                                Max no of logs= 20</div>
<div class="MsoNoSpacing" style="background-color: white; font-family: Roboto; font-size: 16px;">
                * OK</div>
<div class="MsoNoSpacing" style="background-color: white; font-family: Roboto; font-size: 16px;">
<br /></div>
<div class="MsoNoSpacing" style="background-color: white; font-family: Roboto; font-size: 16px;">
* To filter the events from error log</div>
<div class="MsoNoSpacing" style="background-color: white; font-family: Roboto; font-size: 16px;">
<span style="font-weight: 700;">                sp_readerrorlog 0,1,'Error',null<o:p></o:p></span></div>
<div class="MsoNoSpacing" style="background-color: white; font-family: Roboto; font-size: 16px;">
<br /></div>
<div class="MsoNoSpacing" style="background-color: white; font-family: Roboto; font-size: 16px;">
Value of error log file you want to read:</div>
<div class="MsoNoSpacing" style="background-color: white; font-family: Roboto; font-size: 16px;">
                                0 = current, 1 = Archive #1, 2 = Archive #2, etc...</div>
<div class="MsoNoSpacing" style="background-color: white; font-family: Roboto; font-size: 16px;">
<br /></div>
<div class="MsoNoSpacing" style="background-color: white; font-family: Roboto; font-size: 16px;">
Log file type: 1 or NULL = error log, 2 = SQL Agent log</div>
<div class="MsoNoSpacing" style="background-color: white; font-family: Roboto; font-size: 16px;">
Search string 1: String one you want to search for</div>
<div class="MsoNoSpacing" style="background-color: white; font-family: Roboto; font-size: 16px;">
Search string 2: String two you want to search for to  further refine the results</div>
<div class="MsoNoSpacing" style="background-color: white; font-family: Roboto; font-size: 16px;">
                5th         : Start Date</div>
<span style="background-color: white; font-family: calibri, sans-serif; font-size: 11pt; line-height: 16.8667px;">                     6th         : End Date</span></div>
</div>

Transferring Logins in SQL Server

<div><div dir="ltr" style="text-align: left;" trbidi="on">
<span style="background-color: white; font-family: Roboto; font-size: 16px;">First 2 steps should be executed in primary server and copy 2nd step output and run in standby or second server</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: medium;"><span style="font-weight: 700;">--step1</span></span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">USE master</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">GO</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">  DROP PROCEDURE sp_hexadecimal</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">GO</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">CREATE PROCEDURE sp_hexadecimal</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">    @binvalue varbinary(256),</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">    @hexvalue varchar (514) OUTPUT</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">AS</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">DECLARE @charvalue varchar (514)</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">DECLARE @i int</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">DECLARE @length int</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">DECLARE @hexstring char(16)</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">SELECT @charvalue = '0x'</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">SELECT @i = 1</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">SELECT @length = DATALENGTH (@binvalue)</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">SELECT @hexstring = '0123456789ABCDEF'</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">WHILE (@i <= @length)</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">BEGIN</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">  DECLARE @tempint int</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">  DECLARE @firstint int</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">  DECLARE @secondint int</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">  SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">  SELECT @firstint = FLOOR(@tempint/16)</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">  SELECT @secondint = @tempint - (@firstint*16)</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">  SELECT @charvalue = @charvalue +</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">    SUBSTRING(@hexstring, @firstint+1, 1) +</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">    SUBSTRING(@hexstring, @secondint+1, 1)</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">  SELECT @i = @i + 1</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">END</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">SELECT @hexvalue = @charvalue</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">GO</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">  DROP PROCEDURE sp_help_revlogin</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">GO</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">DECLARE @name sysname</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">DECLARE @type varchar (1)</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">DECLARE @hasaccess int</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">DECLARE @denylogin int</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">DECLARE @is_disabled int</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">DECLARE @PWD_varbinary  varbinary (256)</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">DECLARE @PWD_string  varchar (514)</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">DECLARE @SID_varbinary varbinary (85)</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">DECLARE @SID_string varchar (514)</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">DECLARE @tmpstr  varchar (1024)</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">DECLARE @is_policy_checked varchar (3)</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">DECLARE @is_expiration_checked varchar (3)</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">DECLARE @defaultdb sysname</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">IF (@login_name IS NULL)</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">  DECLARE login_curs CURSOR FOR</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">      SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM </span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">sys.server_principals p LEFT JOIN sys.syslogins l</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">      ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name <> 'sa'</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">ELSE</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">  DECLARE login_curs CURSOR FOR</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">      SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM </span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">sys.server_principals p LEFT JOIN sys.syslogins l</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">      ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_name</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">OPEN login_curs</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">IF (@@fetch_status = -1)</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">BEGIN</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">  PRINT 'No login(s) found.'</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">  CLOSE login_curs</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">  DEALLOCATE login_curs</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">  RETURN -1</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">END</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">SET @tmpstr = '/* sp_help_revlogin script '</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">PRINT @tmpstr</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">SET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">PRINT @tmpstr</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">PRINT ''</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">WHILE (@@fetch_status <> -1)</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">BEGIN</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">  IF (@@fetch_status <> -2)</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">  BEGIN</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">    PRINT ''</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">    SET @tmpstr = '-- Login: ' + @name</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">    PRINT @tmpstr</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">    IF (@type IN ( 'G', 'U'))</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">    BEGIN -- NT authenticated account/group</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">      SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']'</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">    END</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">    ELSE BEGIN -- SQL Server authentication</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">        -- obtain password and sid</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">            SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">        EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">        EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">        -- obtain password policy state</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">        SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">        SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">            SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']'</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">        IF ( @is_policy_checked IS NOT NULL )</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">        BEGIN</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">          SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">        END</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">        IF ( @is_expiration_checked IS NOT NULL )</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">        BEGIN</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">          SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">        END</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">    END</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">    IF (@denylogin = 1)</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">    BEGIN -- login is denied access</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">      SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">    END</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">    ELSE IF (@hasaccess = 0)</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">    BEGIN -- login exists but does not have access</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">      SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">    END</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">    IF (@is_disabled = 1)</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">    BEGIN -- login is disabled</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">      SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">    END</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">    PRINT @tmpstr</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">  END</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">  FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">   END</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">CLOSE login_curs</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">DEALLOCATE login_curs</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">RETURN 0</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">GO</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: medium;"><span style="font-weight: 700;">--step2</span></span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">EXEC sp_help_revlogin</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: medium;"><span style="font-weight: 700;">--step3:</span></span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">The above s.p generates some output as follows</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">/* sp_help_revlogin script </span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">** Generated May 25 2009  9:11PM on ONLINE */</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">-- Login: BUILTIN\Administrators</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">CREATE LOGIN [BUILTIN\Administrators] FROM WINDOWS WITH DEFAULT_DATABASE = [master]</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">-- Login: NT AUTHORITY\SYSTEM</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">CREATE LOGIN [NT AUTHORITY\SYSTEM] FROM WINDOWS WITH DEFAULT_DATABASE = [master]</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">Copy, paste above output in Stand by server instance and run for required logins.</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><span style="background-color: white; font-family: Roboto; font-size: 16px;">Alternatively you can download the script from</span><br style="background-color: white; font-family: Roboto; font-size: 16px;" /><a href="http://support.microsoft.com/kb/918992/" style="background-color: white; color: #155cd0; font-family: Roboto; font-size: 16px; outline: 0px; text-decoration-line: none;">http://support.microsoft.com/kb/918992/</a></div>
</div>

SQL Talk

Table Partitioning in SQL Server

SQL Server Errorlogs

Transferring Logins in SQL Server

SQL Server DBA Demo

Follow Me

Subscribe YouTube

Popular Posts

About Me

Followers

Recent Posts

SQL Server DBA Demo

SQL Server DBA Demo

SQL Talk

Recent Posts

Recent Comments

Flickr