script for sp_help_revlogin procedure

----------start of the script-------------
-- this script is used to generate logins in your sql server instance


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 -----

Database Virtualization

I guess this has already been implemented in some or the other DBMS, latest when I searched in Google, I've seen this being implemented first by Delphi.
But of course everyone is catching up with Cloud.


So I thought why not Sql Server and so are my thoughts on this:

I would like to see the following features in a virtualized database:

1) To restore a database, just drag the source database and drop it on the destination, the system alerts you if you want to replace the destination database and on clicking yes, it should just replace the destination with source

2) drag and drop the database to create a new copy on the destination

3) Create a virtual database with data/log files etc all virtual

4) implement other solutions like mirroring, log shipping and clustering on the virtual databases.

BCP all the tables in your database

Declare @query nvarchar(100)
Declare @tbl_name varchar(50)
Declare  Del_arch_tbls cursor for

 Select table_name from information_schema.tables where table_schema='schema2'

Open del_arch_tbls
Fetch next from del_arch_tbls into @tbl_name
While @@fetch_status=0
begin
Set @query = 'xp_cmdshell ' +char(39) +
  'bcp databaseA.schema2.'+@tbl_name+
  ' out f:\ppte\schema2-' + @tbl_name+'.dat -SserverA -T -n'
  +char(39)
print @query
exec Sp_executesql @query
Fetch next from del_arch_tbls into @tbl_name
end
Close del_arch_tbls
Deallocate del_arch_tbls

I am updating this post after I found that there's an easy way of doing this.
 simply use  the below to query all the tables in your database.

sp_msforeachtable ' sp_spaceused [?]'

so easy

Query to list all the backups

use msdb
go
select f.file_size/(1024*1024) 'file_size',s.backup_size/(1024*1024) 'backup_size',s.backup_finish_date,s.database_name,f.logical_name from
backupset s inner join backupfile f on s.backup_set_id=f.backup_set_id
where s.database_name='rar' and s.type='D'
order by s.backup_finish_date desc

Query to backup all databases

There are other easy ways to take backup, but this is just one of the other ways.

DECLARE @name VARCHAR(50) -- database name 
DECLARE @path VARCHAR(256) -- path for backup files 
DECLARE @fileName VARCHAR(256) -- filename for backup 
DECLARE @fileDate VARCHAR(20) -- used for file name
SET @path = 'e:\Program Files\Microsoft SQL Server\backup-CR30581-20100507' 

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

DECLARE db_cursor CURSOR FOR 
SELECT name
FROM master.dbo.sysdatabases
--WHERE name NOT IN ('master','model','msdb','tempdb') 
OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @name  
WHILE @@FETCH_STATUS = 0  
BEGIN  
       SET @fileName = @path + @name + '_' + @fileDate + '.BAK' 
       BACKUP DATABASE @name TO DISK = @fileName 
       FETCH NEXT FROM db_cursor INTO @name  
END  
CLOSE db_cursor  
DEALLOCATE db_cursor
--master.xp_fixeddrives

SQL Server - inventories

I've been thinking and thinking to dig out how sql server was built and how the storage engine works in sql server.
I almost became an instant fan of Paul S Randal by reading his blogs on the concepts.
So I started my own blog.

Here are some features in MSSQL on which I have little or no knowledge.

1) Status/Status2 columns in sysdatabases table in Master database, I checked with Paul on this and most of the statuses apart from the usual 1,2,4,8.... are undocumented and hence the results/descriptions vary after every release

2) Implementing Database in RAM, there is ofcourse Ramdisk which is available as free download where you can make some part of your RAM as a hard drive and put your user databases in that.
I am yet to try this

http://arstechnica.com/phpbb/viewtopic.php?f=15&t=273154

3) DB Cluster in the same instance - kind of wierd thought but y not??
Lets suppose we have two databases in the same instance, one acting as an active DB and the other as a passive DB,  Now I'll create a virtual DB to which users have access.
Users don't know that there are two physical databases.
So at any point of time only one Database is online and the other will be readonly/standby mode same as the secondary database in log shipping.
And the t-log backups should be applied continuously on the secondary.
so its like we have log shipping in place between two different databases with the same name on the same instance.
Now the next question is what happens in case of failover.
when Database A goes down, all the connections will be redirected to Database B with all the incomplete transactions starting from the beginning on B.
This seems a bit of work.

Also we can configure the failover like this:
Do the failover if any of the following conditions are true:
1) If Tlog is full and no further disk space to grow
2) If file group is full and the corresponding disk is full as well
3) DB going to Suspect mode

Is this ideally possible ?? I don't have an answer for this.