Developer's Closet A place where I can put my PHP, SQL, Perl, JavaScript, and VBScript code.

6Aug/100
Query Database Size and Unallocated Space from SQL Database

I created the he following stored procedure to give me a more clear look at the unallocated and unused space used by my databases. The stored procedure takes one variable, the database name, and queries for the database size (data file size plus transaction log file size), unallocated space, index size, and unused space. This stored procedure borrows from the sp_spaceused stored procedure.

use master
GO
IF EXISTS (SELECT * FROM [dbo].[sysobjects]
	WHERE ID = object_id(N'[dbo].[sp_spaceused_with_size]') AND 
	OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[sp_spaceused_with_size]
GO
-- 1. sp_spaceused_with_size 
CREATE PROC sp_spaceused_with_size
	@db varchar(255)
	-- This stored procedure is used to query for the database
	-- size and unallocated space of a database. This query is similar
	-- to sp_spaceused, but also outputs data and transaction log files 

AS
	declare @pages bigint -- Working variable for size calculation.
			,@dbsize bigint
			,@logsize bigint
			,@reservedpages bigint
			,@usedpages bigint
			,@sql nvarchar (2000)
			,@ParmDefinition nvarchar(2000)

	--SET @db = db_name()
			
	/* unallocated space could not be negative */
	SET @sql = N'use ' + @db + ';
		select 
		@dbsizeOUT = sum(convert(bigint,case when status & 64 = 0 then size else 0 end)), 
		@logsizeOUT = sum(convert(bigint,case when status & 64  0 then size else 0 end)) 
		from dbo.sysfiles;'
	
	SET @ParmDefinition = N'@dbsizeOUT bigint OUTPUT, @logsizeOUT bigint OUTPUT';

	EXECUTE sp_executesql @sql, @ParmDefinition, @dbsizeOUT=@dbsize OUTPUT, @logsizeOUT=@logsize OUTPUT;

	SET @sql = 'use ' + @db + ';
			select @reservedpagesOUT = sum(a.total_pages),
			@usedpagesOUT = sum(a.used_pages),
			@pagesOUT = sum(
					CASE
						-- XML-Index and FT-Index-Docid is not considered "data", but is part of "index_size"
						When it.internal_type IN (202,204) Then 0
						When a.type  1 Then a.used_pages
						When p.index_id = @reservedpages then
			(convert (dec (15,2),@dbsize) - convert (dec (15,2),@reservedpages)) 
			* 8192 / 1048576 else 0 end),15,2) + ' MB'),
		data_file = ltrim(str((convert (dec (15,2),@dbsize)) 
			* 8192 / 1048576,15,2) + ' MB'),
		transaction_log_file = ltrim(str((convert (dec (15,2),@logsize)) 
			* 8192 / 1048576,15,2) + ' MB'),
		reserved = ltrim(str((convert (dec (15,2),@reservedpages)) 
			* 8192 / 1048576,15,2) + ' MB'),
		data = ltrim(str((convert (dec (15,2),@pages)) 
			* 8192 / 1048576,15,2) + ' MB'),
		index_size = ltrim(str((case when @usedpages > @pages then
			(convert (dec (15,2),@usedpages) - convert (dec (15,2),@pages)) 
			* 8192 / 1048576 else 0 end),15,2) + ' MB'),
		unused = ltrim(str((case when @reservedpages > @usedpages then
			(convert (dec (15,2),@reservedpages) - convert (dec (15,2),@usedpages)) 
			* 8192 / 1048576 else 0 end),15,2) + ' MB')
GO
-- 2. Setup the stored procedure to run as as a system stored procedure instead of a user stored procedure
EXEC sys.sp_MS_marksystemobject sp_spaceused_with_size
GO
use master
go
exec sp_spaceused_with_size msdb
go

2Aug/100
Terminate Connections to an SQL Database

Have you ever tried to restore a database to an SQL server with existing connections? Unless the connections are terminated, the database restore will fail with the error that you cannot obtain exclusive access. You can manually kill each connection, but with many users this can be time consuming. I've found the SQL server just doesn't do a good job at this. This script does a really good job of killing connections to a specific database.

-- Create temporary table for sysprocesses
set QUOTED_IDENTIFIER Off
SET NOCOUNT ON

-- Variables
declare @strsql char(200),
  @USRCOUNT varchar(3),
  @PRINTUSRCOUNT VARCHAR (255), 
  @KILLSTATE varchar(32),
  @spid smallint 

-- Query for id number of all connections
use master
set @strsql = "select hostname,program_name,spid from sysprocesses where database='Database Name' order by hostname"

create table #tokill
(
  hostname varchar(50),
  program_name varchar(50),
  spid varchar(5)
)

-- Insert into temp table
INSERT INTO #tokill EXEC(@strsql)

-- Comment out the query below to hide connections
select * from #tokill

SELECT @USRCOUNT = (SELECT CONVERT(CHAR, COUNT(*)) FROM #tokill)
SET @PRINTUSRCOUNT = 'Attempting to disconnect ' + @USRCOUNT + ' Connections' 
PRINT @PRINTUSRCOUNT 

-- Pause script for 10 seconds
WAITFOR DELAY '00:00:10' 

-- Begin cursor 
DECLARE CUSRKILL SCROLL CURSOR FOR 
SELECT SPID FROM #tokill 

OPEN CUSRKILL

FETCH FIRST FROM CUSRKILL INTO @spid 
SELECT @KILLSTATE = 'KILL ' + CONVERT(char, @spid) 
PRINT @KILLSTATE 
EXEC(@KILLSTATE ) 

WHILE @@FETCH_STATUS = 0 
BEGIN 
  FETCH NEXT FROM Cusrkill into @spid 
  WHILE @@FETCH_STATUS = 0 
  BEGIN 

    SELECT @KILLSTATE = 'KILL ' + CONVERT(char, @spid) 
    PRINT @KILLSTATE 
    EXECUTE( @KILLSTATE ) 

    FETCH NEXT FROM Cusrkill INTO @spid 

  END 
END 
CLOSE Cusrkill 
DEALLOCATE Cusrkill 

--End cursor
PRINT 'Finished Killing IMA SPIDs'
DROP TABLE #tokill
1Aug/100
List all Connections to an SQL Database

A simple, but useful command, this script will show all connections to an SQL server. Replace Database Name with the name of the database and click go.

use master
GO
select name as [database], rtrim(hostname) as hostname, rtrim(program_name) as program_name, rtrim(net_library) as net_library, rtrim(loginame) as loginame, login_time, last_batch from master.dbo.sysprocesses 
inner join master.dbo.sysdatabases on master.dbo.sysdatabases.dbid = master.dbo.sysprocesses.dbid
where name = 'Database Name' order by hostname;
29Jul/100
Search for Orphaned Users After a Database Restore

After a database has been restored, users may become orphaned, or disconnected, from the database. You will see an entry for the user in the syslogins table (select * from syslogins;), but the user will not be able to sign onto the restored database. You have to clear these with the sp_change_users_login stored procedure.

Check for user disconnects or orphaned users by using the following SQL script:

-- Search for orphans using the following user report
use [Database Name]
go
sp_change_users_login 'report'
go

Attach the orphaned users:

-- If there are orphans, clear them with the following script
sp_change_users_login 'update_one', 'User Name', 'User Name'
go
Tagged as: , No Comments
29Jun/100
SQL Server CPU Utilization

Some helpful SQL stats from this article.

-- From the SQL Server Performance Dashboard (sys.dm_os_ring_buffers)
-- Query CPU utilization for each four minute intervals
declare @ts_now bigint
select @ts_now = cpu_ticks / convert(float, cpu_ticks_in_ms) from sys.dm_os_sys_info
select record_id,
      dateadd(ms, -1 * (@ts_now - [timestamp]), GetDate()) as EventTime,
      SQLProcessUtilization,
      SystemIdle,
      100 - SystemIdle - SQLProcessUtilization as OtherProcessUtilization
from (
      select
            record.value('(./Record/@id)[1]', 'int') as record_id,
            record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') as SystemIdle,
            record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') as SQLProcessUtilization,
            timestamp
      from (
            select timestamp, convert(xml, record) as record
            from sys.dm_os_ring_buffers
            where ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
            and record like '%%') as x
      ) as y
order by record_id desc

-- 4:12 (two procs CPU=8,9,9,9)
DECLARE @count int
SELECT @count =100000000
WHILE @count > 0
	BEGIN
	SELECT @count=@count-1
	--PRINT @count
END
29Jun/100
List all Documents in SharePoint

I've used these queries for years, they come in handy when planning growth or a migration. A quick Google search will find many more queries to extract information from SharePoint. These work in wss2 and wss3.

Total number of items stored in the list/document library, ordered by the total item count:

SELECT CASE WHEN webs.fullurl = '' 
THEN 'Portal Site' 
ELSE webs.fullurl
END AS [Site Relative Url], 
Webs.Title AS [Site Title], 
CASE tp_servertemplate
WHEN 104 THEN 'Announcement'
WHEN 105 THEN 'Contacts'
WHEN 108 THEN 'Discussion Boards'
WHEN 101 THEN 'Docuemnt Library'
WHEN 106 THEN 'Events'
WHEN 100 THEN 'Generic List'
WHEN 1100 THEN 'Issue List'
WHEN 103 THEN 'Links List'
WHEN 109 THEN 'Image Library'
WHEN 115 THEN 'InfoPath Form Library'
WHEN 102 THEN 'Survey'
WHEN 107 THEN 'Task List'
ELSE 'Other' END AS TYPE,
tp_title 'Title', 
tp_description AS [Description], 
tp_itemcount AS [Total Item]
FROM lists inner join webs ON lists.tp_webid = webs.Id
WHERE tp_servertemplate IN (104,105,108,101, 
	106,100,1100,103,109,115,102,107,120)
ORDER BY tp_itemcount DESC

Total number of documents:

SELECT COUNT(*) 
FROM Docs INNER JOIN Webs On Docs.WebId = Webs.Id
INNER JOIN Sites ON Webs.SiteId = SItes.Id
WHERE Docs.Type <> 1 
AND (LeafName IS NOT NULL) 
AND (LeafName <> '')
AND (LeafName NOT LIKE '%.stp')
AND (LeafName NOT LIKE '%.aspx')
AND (LeafName NOT LIKE '%.xfp')
AND (LeafName NOT LIKE '%.dwp')
AND (LeafName NOT LIKE '%template%')
AND (LeafName NOT LIKE '%.inf')
AND (LeafName NOT LIKE '%.css')

Total number of Microsoft Word documents:

SELECT COUNT(*)
FROM Docs INNER JOIN Webs On Docs.WebId = Webs.Id
INNER JOIN Sites ON Webs.SiteId = SItes.Id
WHERE Docs.Type <> 1 
AND (LeafName LIKE '%.doc')
AND (LeafName NOT LIKE '%template%')

List documents by age:

SELECT Webs.FullUrl AS SiteUrl, Webs.Title AS [Title], DirName + '/' + LeafName AS [Document Name], Docs.TimeCreated
FROM Docs INNER JOIN Webs On Docs.WebId = Webs.Id
INNER JOIN Sites ON Webs.SiteId = SItes.Id
WHERE Docs.Type <> 1 
AND (LeafName IS NOT NULL) 
AND (LeafName <> '')
AND (LeafName NOT LIKE '%.stp')
AND (LeafName NOT LIKE '%.aspx')
AND (LeafName NOT LIKE '%.xfp')
AND (LeafName NOT LIKE '%.dwp')
AND (LeafName NOT LIKE '%template%')
AND (LeafName NOT LIKE '%.inf')
AND (LeafName NOT LIKE '%.css')
ORDER BY Docs.TimeCreated DESC

Size of files ordered by file size:

SELECT TOP 100 Webs.FullUrl AS SiteUrl, Webs.Title AS [Title], DirName + '/' + LeafName AS [Document Name], CAST((CAST(CAST(Size AS DECIMAL(10,2))/1024 AS DECIMAL(10,2))/1024) AS DECIMAL(10,2)) AS 'Size in MB'
FROM Docs INNER JOIN Webs ON Docs.WebId = Webs.Id
INNER JOIN Sites ON Webs.SiteId = SItes.Id
WHERE Docs.Type <> 1 
AND (LeafName IS NOT NULL) 
AND (LeafName <> '')
AND (LeafName NOT LIKE '%.stp')  
AND (LeafName NOT LIKE '%.aspx') 
AND (LeafName NOT LIKE '%.xfp') 
AND (LeafName NOT LIKE '%.dwp') 
AND (LeafName NOT LIKE '%template%') 
AND (LeafName NOT LIKE '%.inf') 
AND (LeafName NOT LIKE '%.css') 
ORDER BY 'Size in MB' DESC

Total size of all files stored in SharePoint:

SELECT SUM(CAST((CAST(CAST(Size AS DECIMAL(10,2))/1024 AS DECIMAL(10,2))/1024) AS DECIMAL(10,2))) AS 'Total Size in MB'
FROM Docs INNER JOIN Webs ON Docs.WebId = Webs.Id
INNER JOIN Sites ON Webs.SiteId = SItes.Id
WHERE Docs.Type <> 1
AND (LeafName IS NOT NULL)
AND (LeafName NOT LIKE '%.stp')
AND (LeafName NOT LIKE '%.aspx')
AND (LeafName NOT LIKE '%.xfp')
AND (LeafName NOT LIKE '%.dwp')
AND (LeafName NOT LIKE '%template%')
AND (LeafName NOT LIKE '%.inf')
AND (LeafName NOT LIKE '%.css')

Reference: http://www.codeproject.com/kb/dotnet/QueriesToAnalyzeSPUsage.aspx and http://www.mcplusa.com/blog/2009/07/use-sql-queries-to-find-sharepoint-doc-count/

3Aug/090
List Databases from an SQL 2005 Server

There are a number of ways to list databases from an SQL 2005 Server.

-- Easiest way is to use the undocumented SQL 2005 stored procedure
exec sp_msForEachDB 'PRINT ''?'''

-- Query the sysdatabases table (for SQL 2000 and 2005)
select * from sysdatabases

-- query the sys.sysdatabases table (for SQL 2005)
select * from sys.sysdatabases

-- Stored procedure to list all databases
EXEC sp_databases

-- Stored procedure to list specific information about a single database or all databases.
EXEC sp_helpdb
Tagged as: No Comments
2Aug/091
Transfer SQL Logins to Another SQL 2005 Server

After you move a database from one instance of SQL 2005 to another instance of SQL 2005, the users may not be able to log in to the database on the new server. Additionally, the users may receive the following error message: Login failed for user 'MyUser'. (Microsoft SQL Server, Error: 18456).

This problem occurs because you did not transfer the logins and the passwords from the old instance of SQL 2005 to the new instance.

To transfer the logins and the passwords from one instance of SQL 2005 to another, follow these steps:

  1. On the old server, start SQL Server Management Studio, and then connect to the instance of SQL Server from which you moved the database.
  2. Open a New Query, and then run the following script. The script will create a stored procedure that can be scheduled.
    -- this script is intended for a SQL 2005 server 
    -- the execute script will NOT work on a SQL 2000 server
    -- begin script, create sp_help_revlogin procedure
    USE master
    GO
    IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
      DROP PROCEDURE sp_hexadecimal
    GO
    CREATE PROCEDURE sp_hexadecimal
        @binvalue varbinary(256),
        @hexvalue varchar (514) OUTPUT
    AS
    DECLARE @charvalue varchar (514)
    DECLARE @i int
    DECLARE @length int
    DECLARE @hexstring char(16)
    SELECT @charvalue = '0x'
    SELECT @i = 1
    SELECT @length = DATALENGTH (@binvalue)
    SELECT @hexstring = '0123456789ABCDEF'
    WHILE (@i <= @length)
    BEGIN
      DECLARE @tempint int
      DECLARE @firstint int
      DECLARE @secondint int
      SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
      SELECT @firstint = FLOOR(@tempint/16)
      SELECT @secondint = @tempint - (@firstint*16)
      SELECT @charvalue = @charvalue +
        SUBSTRING(@hexstring, @firstint+1, 1) +
        SUBSTRING(@hexstring, @secondint+1, 1)
      SELECT @i = @i + 1
    END
    
    SELECT @hexvalue = @charvalue
    GO
     
    IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
      DROP PROCEDURE sp_help_revlogin
    GO
    CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS
    DECLARE @name sysname
    DECLARE @type varchar (1)
    DECLARE @hasaccess int
    DECLARE @denylogin int
    DECLARE @is_disabled int
    DECLARE @PWD_varbinary  varbinary (256)
    DECLARE @PWD_string  varchar (514)
    DECLARE @SID_varbinary varbinary (85)
    DECLARE @SID_string varchar (514)
    DECLARE @tmpstr  varchar (1024)
    DECLARE @is_policy_checked varchar (3)
    DECLARE @is_expiration_checked varchar (3)
    
    DECLARE @defaultdb sysname
     
    IF (@login_name IS NULL)
      DECLARE login_curs CURSOR FOR
    
          SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM 
    sys.server_principals p LEFT JOIN sys.syslogins l
          ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name  'sa'
    ELSE
      DECLARE login_curs CURSOR FOR
    
          SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM 
    sys.server_principals p LEFT JOIN sys.syslogins l
          ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_name
    OPEN login_curs
    
    FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
    IF (@@fetch_status = -1)
    BEGIN
      PRINT 'No login(s) found.'
      CLOSE login_curs
      DEALLOCATE login_curs
      RETURN -1
    END
    SET @tmpstr = '/* sp_help_revlogin script '
    PRINT @tmpstr
    SET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
    PRINT @tmpstr
    PRINT ''
    WHILE (@@fetch_status  -1)
    BEGIN
      IF (@@fetch_status  -2)
      BEGIN
        PRINT ''
        SET @tmpstr = '-- Login: ' + @name
        PRINT @tmpstr
        IF (@type IN ( 'G', 'U'))
        BEGIN -- NT authenticated account/group
    
          SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']'
        END
        ELSE BEGIN -- SQL Server authentication
            -- obtain password and sid
                SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )
            EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT
            EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
     
            -- obtain password policy state
            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
            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
     
                SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']'
    
            IF ( @is_policy_checked IS NOT NULL )
            BEGIN
              SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked
            END
            IF ( @is_expiration_checked IS NOT NULL )
            BEGIN
              SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked
            END
        END
        IF (@denylogin = 1)
        BEGIN -- login is denied access
          SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )
        END
        ELSE IF (@hasaccess = 0)
        BEGIN -- login exists but does not have access
          SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )
        END
        IF (@is_disabled = 1)
        BEGIN -- login is disabled
          SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'
        END
        PRINT @tmpstr
      END
    
      FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
       END
    CLOSE login_curs
    DEALLOCATE login_curs
    RETURN 0
    GO
    -- end script

    After the stored procedure has been created, run the sp_help_revlogin stored procedure:

    EXEC sp_help_revlogin
  3. The results output a script that can be run on any SQL server to create the users. An example of the output is as follows. If you output the results to a script, you will first remove the [SQLSTATE 01000] from each line and execute:
    -- Login: UserName [SQLSTATE 01000]
    CREATE LOGIN [UserName] WITH PASSWORD = 0x00AAA0000000000AAAAAAAAAAAA000A00000000A00AAAA0AAAAAAA0A0AA HASHED, SID = 0xAA0000A00AA0A0AA0AAAAAAA00000AAA0A, DEFAULT_DATABASE = [DatabaseName], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF [SQLSTATE 01000]
  4. If the login already exists, the password can be repaired by altering the script:
    -- Login: UserName [SQLSTATE 01000]
    ALTER LOGIN [UserName] WITH PASSWORD = 0x0000A000AAAAAAA0AAA000A0A0AA HASHED

The stored procedures can also be scheduled and used for disaster recovery. I usually schedule this stored procedure as another task within the master database backup and output the results to a script.

Note: If there are any warnings or errors, correct them with instructions provided in Microsoft’s reference document. The link is listed above.

Reference: http://support.microsoft.com/kb/918992/

1Aug/090
Transfer SQL Logins to Another SQL 2000 Server

The following script will transfer logins and passwords from one SQL 2000 server to another. This script is intended to be used in the rare case of a database restore to a completely different server that has not yet received a copy of the database.

-- begin script, create sp_help_revlogin procedure
USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
  DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
    @binvalue varbinary(256),
    @hexvalue varchar(256) OUTPUT
AS
DECLARE @charvalue varchar(256)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = '0123456789ABCDEF' 
WHILE (@i <= @length) 
BEGIN
  DECLARE @tempint int
  DECLARE @firstint int
  DECLARE @secondint int
  SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
  SELECT @firstint = FLOOR(@tempint/16)
  SELECT @secondint = @tempint - (@firstint*16)
  SELECT @charvalue = @charvalue +
    SUBSTRING(@hexstring, @firstint+1, 1) +
    SUBSTRING(@hexstring, @secondint+1, 1)
  SELECT @i = @i + 1
END
SELECT @hexvalue = @charvalue
GO

IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
  DROP PROCEDURE sp_help_revlogin 
GO
CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS
DECLARE @name    sysname
DECLARE @xstatus int
DECLARE @binpwd  varbinary (256)
DECLARE @txtpwd  sysname
DECLARE @tmpstr  varchar (256)
DECLARE @SID_varbinary varbinary(85)
DECLARE @SID_string varchar(256)

IF (@login_name IS NULL)
  DECLARE login_curs CURSOR FOR 
    SELECT sid, name, xstatus, password FROM master..sysxlogins 
    WHERE srvid IS NULL AND name  'sa'
ELSE
  DECLARE login_curs CURSOR FOR 
    SELECT sid, name, xstatus, password FROM master..sysxlogins 
    WHERE srvid IS NULL AND name = @login_name
OPEN login_curs 
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd
IF (@@fetch_status = -1)
BEGIN
  PRINT 'No login(s) found.'
  CLOSE login_curs 
  DEALLOCATE login_curs 
  RETURN -1
END
SET @tmpstr = '/* sp_help_revlogin script ' 
PRINT @tmpstr
SET @tmpstr = '** Generated ' 
  + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
PRINT 'DECLARE @pwd sysname'
WHILE (@@fetch_status  -1)
BEGIN
  IF (@@fetch_status  -2)
  BEGIN
    PRINT ''
    SET @tmpstr = '-- Login: ' + @name
    PRINT @tmpstr 
    IF (@xstatus & 4) = 4
    BEGIN -- NT authenticated account/group
      IF (@xstatus & 1) = 1
      BEGIN -- NT login is denied access
        SET @tmpstr = 'EXEC master..sp_denylogin ''' + @name + ''''
        PRINT @tmpstr 
      END
      ELSE BEGIN -- NT login has access
        SET @tmpstr = 'EXEC master..sp_grantlogin ''' + @name + ''''
        PRINT @tmpstr 
      END
    END
    ELSE BEGIN -- SQL Server authentication
      IF (@binpwd IS NOT NULL)
      BEGIN -- Non-null password
        EXEC sp_hexadecimal @binpwd, @txtpwd OUT
        IF (@xstatus & 2048) = 2048
          SET @tmpstr = 'SET @pwd = CONVERT (varchar(256), ' + @txtpwd + ')'
        ELSE
          SET @tmpstr = 'SET @pwd = CONVERT (varbinary(256), ' + @txtpwd + ')'
        PRINT @tmpstr
	EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
        SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name 
          + ''', @pwd, @sid = ' + @SID_string + ', @encryptopt = '
      END
      ELSE BEGIN 
        -- Null password
	EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
        SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name 
          + ''', NULL, @sid = ' + @SID_string + ', @encryptopt = '
      END
      IF (@xstatus & 2048) = 2048
        -- login upgraded from 6.5
        SET @tmpstr = @tmpstr + '''skip_encryption_old''' 
      ELSE 
        SET @tmpstr = @tmpstr + '''skip_encryption'''
      PRINT @tmpstr 
    END
  END
  FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd
  END
CLOSE login_curs 
DEALLOCATE login_curs 
RETURN 0
GO
-- end script

After the stored procedure has been created, run the sp_help_revlogin stored procedure:

EXEC master..sp_help_revlogin

The stored procedures can also be scheduled. I like to schedule this stored procedure as another task within the master database backup and output the results to a file.

Reference: http://www.support.microsoft.com/kb/246133/

8Jun/090
Script to Add a New SQL Differential Backup Job With Daily Incremental Zip Archives

This script builds on the differential backup script, adding a zipped incremental backup. This script will create a zip file for each day of the week. I've used this script when I have a simple database type but need the flexibility of multiple daily differential backups. You will have to turn on xp_cmdshell to run this script.

-- This script creates a differential backup job to backup the DBName database as DBName_Differential.bak (daily at 5:00 PM). This script also creates daily incremental zip archives.

USE [msdb]
GO
DECLARE @jobId BINARY(16)
EXEC  msdb.dbo.sp_add_job @job_name=N'DBName_Differential.bak', 
	@enabled=1, 
	@notify_level_eventlog=2, 
	@notify_level_email=2, 
	@notify_level_netsend=2, 
	@notify_level_page=2, 
	@delete_level=0, 
	@category_name=N'[Uncategorized (Local)]', 
	@owner_login_name=N'sa', @job_id = @jobId OUTPUT
select @jobId
GO
EXEC msdb.dbo.sp_add_jobserver @job_name=N'DBName_Differential.bak', @server_name = N'SERVER\INSTANCE'
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_add_jobstep @job_name=N'DBName_Differential.bak', @step_name=N'Step 1: Backup File', 
	@step_id=1, 
	@cmdexec_success_code=0, 
	@on_success_action=3, 
	@on_fail_action=2, 
	@retry_attempts=0, 
	@retry_interval=0, 
	@os_run_priority=0, @subsystem=N'TSQL', 
	@command=N'BACKUP DATABASE [PBSA] TO  DISK = N''C:\MSSQL.1\MSSQL\Backup\DBName_Differential.bak'' WITH  DIFFERENTIAL ,  DESCRIPTION = N''DBName_Differential.bak'', NOFORMAT, INIT,  NAME = N''DBName_Differential.bak'', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
', 
	@database_name=N'master', 
	@flags=0
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_add_jobstep @job_name=N'DBName_Differential.bak', @step_name=N'Step 2: Zip Backup File', 
	@step_id=2, 
	@cmdexec_success_code=0, 
	@on_success_action=1, 
	@on_fail_action=2, 
	@retry_attempts=0, 
	@retry_interval=0, 
	@os_run_priority=0, @subsystem=N'TSQL', 
	@command=N'xp_cmdshell ''C:\PROGRA~1\WinZip\winzip32.exe -a -en "C:\MSSQL.1\MSSQL\Backup\Backup_zipped\DBName_Differential.bak - %date:~0,3%.zip" "C:\MSSQL.1\MSSQL\Backup\DBName_Differential.bak"''', 
	@database_name=N'master', 
	@flags=0
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_update_job @job_name=N'DBName_Differential.bak', 
	@enabled=1, 
	@start_step_id=1, 
	@notify_level_eventlog=2, 
	@notify_level_email=2, 
	@notify_level_netsend=2, 
	@notify_level_page=2, 
	@delete_level=0, 
	@description=N'', 
	@category_name=N'[Uncategorized (Local)]', 
	@owner_login_name=N'sa', 
	@notify_email_operator_name=N'', 
	@notify_netsend_operator_name=N'', 
	@notify_page_operator_name=N''
GO
USE [msdb]
GO
DECLARE @schedule_id int
EXEC msdb.dbo.sp_add_jobschedule @job_name=N'DBName_Differential.bak', @name=N'Schedule 1', 
	@enabled=1, 
	@freq_type=4, 
	@freq_interval=1, 
	@freq_subday_type=1, 
	@freq_subday_interval=0, 
	@freq_relative_interval=0, 
	@freq_recurrence_factor=1, 
	@active_start_date=20080725, 
	@active_end_date=99991231, 
	@active_start_time=193000, 
	@active_end_time=235959, @schedule_id = @schedule_id OUTPUT
select @schedule_id
GO