Thursday, July 26, 2012

Finding Out the Connection Leaks in Sql Server

This Query (Q1) will show the connections that are in sleep mode
e.g. the datareaders associated with this Query might be not closed

(Q1)


SELECT ec.session_id, last_read,
 last_write, text, client_net_address, program_name, host_process_id, login_name
FROM sys.dm_exec_connections  ec
JOIN sys.dm_exec_sessions es
  ON ec.session_id = es.session_id
CROSS APPLY sys.dm_exec_sql_text(ec.most_recent_sql_handle) AS dest
where es.status = 'sleeping'

Q2 will show the number of open connections by the Database Users


(Q2 )



SELECT DB_NAME(dbid) as 'DataBase Name',
COUNT(dbid) as 'Total Connections'
FROM master.dbo.sysprocesses WITH (nolock)
WHERE dbid > 0
GROUP BY dbid



Thursday, June 21, 2012

Query to Find Duplicate Rows from TABLE - SQL SERVER


SELECT  Office_Name , DUPCOUNT FROM (
SELECT  OFFICE_NAME , ROW_NUMBER() OVER (PARTITION BY OFFICE_NAME ORDER BY OFFICE_NAME) AS DUPCOUNT FROM Mast_Office ) OBJ
WHERE OBJ.DUPCOUNT > 1

Finding tables in DataBase which does not have Primary Key SQL Server


USE MyDataBase
GO
SELECT DISTINCT [TABLE] = OBJECT_NAME(OBJECT_ID)FROM SYS.INDEXES
WHERE INDEX_ID = 0 AND OBJECTPROPERTY(OBJECT_ID,'IsUserTable') = 1
ORDER BY [TABLE]
GO

Saturday, June 9, 2012

Why SET NOCOUNT ON in Stored Procedure in SQL Server


When a stored procedure executes, the database engine must keep sending messages back and forth between the server and the client. These relentless interactions create overhead on the processing and in most cases are not necessary. To avoid them, after the AS keyword, add a SET NOCOUNT ON expression before starting the body of the stored procedure. The formula to follow is:
CREATE PROC/PROCEDURE [SchemaName.]ProcedureName
AS
SET NOCOUNT ON
Body of the Procedure
Here is an example:
CREATE PROCEDURE Registration.GetIdentification
AS
SET NOCOUNT ON
BEGIN
    SELECT FirstName, LastName, DateOfBirth, Gender
    FROM Registration.Students
END
GO
Although you can create and use a stored procedure without specifying a schema, it is recommended that you always use a schema. In fact, you should always create your own schema in your database and create your stored procedure in it.

Wednesday, May 23, 2012

query for the size of row in bytes of each table in a particular database.



-- Database: Any
-- User: sa (compulsory)


SELECT A.name, SUM(B.length)
FROM sysobjects A, syscolumns B
WHERE A.id = B.id
AND A.type = 'U'
GROUP BY A.name
ORDER BY A.name

Monday, May 14, 2012

Application Security Guidelines for Web Application.


  1. Authentication process should not be bypassed.
  2. User names should be unique.
  3. All user names should be unpredictable. It should not be possible to guess the user name.
  4. Email addresses should not be allowed as usernames if email is one of the password reset options.
  5.  If user name and/or password or both are incorrect then a generic error message such as “Username and/or password are invalid” should be given
  6. Self registration of usernames is optional. If self registration is unavailable to new users, then the application doesn’t need to disclose whether or not a selected username already exists
  7. Password Lockout
Ø      Ensure that the users account is locked out    for a period of time when the incorrect password is entered more that a specific number of times (usually 5).
Ø      Account holder should not be notified that any specific account has been locked   out. Account holder should be advised to phone customer support and/or answer a series of security questions for successful login.
Ø      If a locked out account attempts to login, it should be rejected immediately without checking credentials.
Ø      Captcha can be implemented to stop automated cracking of login passwords.

  1. Password Lockout (Extended)
Ø      Password suspension should be enforced and accounts should not be locked out (preventing attackers the ability to deny access to legitimate users).
Ø      Account holder should not be notified that any specific account has been suspended. All failed login attempts (including invalid username) should respond with the same generic message, “Accounts are suspended if multiple login failures occur. If you believe that your account has been suspended, please try again later”.
Ø       If any suspended account attempts to login, it should be rejected immediately without checking credentials.      <Suggestions: Exponential time suspension after second bad attempt. For example, after two password tries – 5 minutes, three password tries – 10 minutes, etc>

  1. Blank password should not be allowed
  2. Password should not be same as username or any common word
  3. Password should be between 8 to 40 Characters and should contain a special character like (! @#$%^&*()). Password should use both small and capital letter and numerals.
  4. Password should be changed periodically. Suggested to change every 30 days
  5.  Users should be required to choose fresh passwords when changing them to avoid cycling though easily guessable or common passwords.
  6. All passwords should be unpredictable. If initial passwords are chosen for the user before they are able to pick their own password then the password must be unpredictable. (Suggestions: 16-character random password, etc)
  7. Session token length should be adequate to provide protection from guessing during an authenticated session.
  8. Session token should be valid for a predetermined period after the last request by the user. Provide session timeout e.g 20 minutes. The application should make this actionable in the same way as if the user had logged out of the application.
  9.  All session tokens issued after authentication must only be sent over SSL
  10. Session tokens should be changed when the user moves from an SSL protected resource to a non- SSL protected resource
  11. All session tokens should be created so they could not be reused on a host other than the one it was issued to.
  12. Session token should be invalidated when the user logs out.
  13. Session token should be non-persistent and should never be written to the browsers history or cache.
  14. The application should display an approved, system use notification message before granting access, informing potential users
  15. The application should notify the user, upon successful logon, of the date and time of the last logon, and the number of unsuccessful logon attempts since the last successful logon.
  16. The application must limit the number of concurrent sessions for any user to [defined number of sessions].
            (Each technology will have different "session" limitations this is different to server concurrent connections.)
  1. All user account management activity including account maintenance, and password resets should take place using 256 bit SSL (with valid certificates).
  2. Application should be capable of logging successful and unsuccessful logging attempts
  3. All data being accepted from users, or other systems should be validated to ensure that it is of the correct type, of an expected length, of the correct syntax including having no illegal characters, and if numerical of the correct range. Only data with expected characteristics should be processed.
  4. All input should be encoded into a normalized form before being processed
  5. All data being processed should be validated to ensure that it is of the correct type, of an expected length, of the correct syntax including having no illegal characters, and if numerical of the correct range. Only data with expected characteristics should be processed.
  6. All output should be encoded into a normalized form before being processed
  7. Database Server & Application server should be in different network zone implemented through Firewall zone.
  8. Application should not present application error messages to an attacker that could be used in an attack.

Website security issues & Solutions




  1. Validation of input and output data

As there is no input validation at the client and server end it is possible to generate
detailed error messages from the back end, which can provide good information
about specific modules. All data used by the website (from users, other servers,
other websites and internal systems) must be validated for type (e.g. numeric,
date, string), length (e.g. 200 characters maximum, or a positive integer) and
syntax (amount should be up to 2 decimal places only) and business rules. All
 data written as output (displayed) needs to be safe to view in a browser, email
client or other software and the integrity of any data that is returned must be
checked.

Types   

A.      SQL Injection
SQL injection is a technique used to take advantage of non-validated input vulnerabilities to pass SQL commands through a Web application for execution by a backend database. Attackers take advantage of the fact that programmers often chain together SQL commands with user-provided parameters, and can therefore embed SQL commands inside these parameters. The result is that the attacker can execute arbitrary SQL queries and/or commands on the backend database server through the Web application.

 

Solution –

1.      Create Database users – Proper rights should be assigned to the database users, which application will use. Database user being used should not have any rights for Data Definition Language. Set the database users with different rights and should be used depending upon the situation. Database users should strictly have rights for Database Manipulation Statements.
2.      One method of preventing SQL injection is to avoid the use of dynamically generated SQL in your code. By using parameterized queries and stored procedures, you then make it impossible for SQL injection to occur against your application. Use of procedures to perform any database operation is recommended. Do not write any queries in the code.
3.      Filter Input Parameters for Special Characters. Filtering input works by removing some or all-special characters from user input. Special characters are characters that enable script to be generated within an HTML stream. Any character which belongs to the character set [<, >, ’, %, ”, ;]  should not be allowed as part of user input. Validations should be performed at the server as well as client side. Only client side validations wont serve the purpose as javascript can be disabled at the client side.
4.      Ensure you provide very little information to the user when an error does occur. Exception generated should not be displayed as it is to the user. Always display custom error messages to the user.

 
B.     Cross Site Scripting Attack/Script Injection –

Cross-site scripting attacks occur when an attacker takes advantage of the applications and creates a request with malicious data (such as a script) that is later presented to the user requesting it. If the user then follows the link, the malicious data is sent to the Web application, which in turn creates an output page for the user, containing the malicious content. The user, however, is normally unaware of the attack, and assumes the data originates from the Web server itself, leading the user to believe this is valid content from the Web site. Thus it is possible to inject malicious scripts in the application and get them executed at the client end. The script is embedded in the application permanently and is executed whenever a user accesses the application.

 

Solution – 

1.      Server side validation technique is Mandatory. Any malicious data identified should not be sent to backend server for processing.
2.      Client side verification should be in place in order to minimize server processing.
3.      Data coming from any input element (Text box, combo box, radio button, etc.) should be validated.

C.    OS Command Injection 
Ensure the applications will not process operating system commands from the user.

  1. JSON Hijacking (JavaScript Hijacking)

An attacker targets a system that uses JavaScript Object Notation (JSON) as a transport mechanism between the client and the server to steal possibly confidential information transmitted from the server back to the client inside the JSON object by taking advantage of the loophole in the browser's Single Origin Policy that does not prohibit JavaScript from one website to be included and executed in the context of another website.
   
Suppose the Vulnerable Website returns JSON with sensitive data via an action method like this.

 public JsonResult AdminBalances() {
  var balances = new[] {
    new {Id = 1, Balance=3.14}, 
    new {Id = 2, Balance=2.72},
    new {Id = 3, Balance=1.62}
  };
  return Json(balances);
}

Assuming this is a method of HomeController, you can access this action via a GET request for /Home/AdminBalances which returns the following JSON:

[{"Id":1,"Balance":3.14},{"Id":2,"Balance":2.72},{"Id":3,"Balance":1.62}]

The fact that this is a JSON array is important. It turns out that a script that contains a JSON array is a valid JavaScript script and can thus be executed. A script that just contains a JSON object is not a valid JavaScript file. For example, if you had a JavaScript file that contained the following JSON:

{"Id":1, "Balance":3.14}

And you had a script tag that referenced that file:

<script src="http://example.com/SomeJson"></script>

You would get a JavaScript error in your HTML page. However, through an unfortunate coincidence, if you have a script tag that references a file only containing a JSON array, that would be considered valid JavaScript and the array gets executed.

 

Solution – 


1.      Either never send JSON arrays OR always require an HTTP POST to get that data (except in the case of non-sensitive data in which case you probably don’t care). For example, with ASP.NET MVC, you could use the AcceptVerbsAttribute to enforce this like so:
 
[AcceptVerbs(HttpVerbs.Post)]
public JsonResult AdminBalances() {
  var balances = new[] {
    new {Id = 1, Balance=3.14}, 
    new {Id = 2, Balance=2.72},
    new {Id = 3, Balance=1.62}
  };
  return Json(balances);
}
One issue with this approach is that many JavaScript libraries such as jQuery request JSON using a GET request by default, not POST. For example, $.getJSON issues a GET request by default. So when calling into this JSON service, you need to make sure you issue a POST request with your client library

  1. Malicious file execution

An attacker relying on this vulnerability is typically looking for a Web application
that fails to prohibit or control execution of uploaded files. Environments, such as
.NET and J2EE, are also vulnerable if they allow file upload and execute
capabilities within Web directories. Uploaded files may also contain a malicious
payload so should not be stored in web accessible locations.

Example –
Original Path –

Attack –

This example,  depicts a traversal attack using special character sequences. "../"  
was used to traverse up one directory in order to enter the scripts directory. ""
allows the attacker to bypass any file extension checks. The result of this attack is
 a view of the file foo.cgi.

Solution –
1.      Strongly validate user input using "accept known good" as a strategy
2.      Add firewall rules to prevent web servers making new connections to external web sites and internal systems. For high value systems, isolate the web server in its own VLAN or private subnet


  1. Authentication management

Websites rely on identifying users to provide access permissions to data
and functions. If authentication (verification of identity, registration and
logging in), authorisation (granting access rights) and session management
(keeping track of the identity of a logged in user while they browse a
website) can be circumvented or altered, a user could access resources
they are not allowed to. Beware especially of how password reminders,
remember-me, change password, log out and updating account details are
handled, how session tokens are used and always have login forms on
dedicated and encrypted (SSL) pages.

Solution –
1.      Ensure that the authentication process cannot be bypassed.
2.      Ensure that usernames and passwords are sent over an encrypted channel.
3.      Ensure that the system enforces to use quality passwords only.
4.      Ensure that the user must respond to a secret answer or secret question or other predetermined information before passwords can be reset.
5.      Ensure that the users account is locked out for a period of time when the incorrect password is entered more that a specific number of times (usually 5).
6.      Ensure that the session tokens are only valid for a predetermined period after the last request by the user.
7.      Ensure that the session token is invalidated when the user logs out.
8.      Ensure that the session token is non-persistent and is never written to the browsers history or cache.


  1. System architecture and configuration

The information system architecture model should address the sensitivity of data
identified during the requirements and specification phase of a website project.
This may entail having separate web, application and database servers or involve
clustering or load balancing. Additional security issues can be
created through the way the live environment is configured. Sufficient and safe
logging, monitoring and alerting facilities need to be built in to allow audit.


  1. System information leakage

Revealing system data or debugging information helps an adversary learn about
 the system and form a plan of attack. An information leak occurs when system
data or debugging information leaves the program through an output stream or
logging function.

Web servers, errors, staff, partner organizations can all be the source of important
information about your website – its technologies, business logic and security
methods. An attacker can use such information to their advantage so it is
important to avoid system information leakage as far as possible.

For example, a database error message can reveal that the application is vulnerable to a SQL injection attack. Other error messages can reveal more oblique clues about the system, about the type of operating system, the applications installed on the system, and the amount of care that the administrators have put into configuring the program.
    
Solution –
A good error handling mechanism always tries to capture all exceptions and returns a generic error message that does not reveal any details about the error and the application. Depending on the platform and container the application is running on, there can be different options.
Set a generic custom error page for all unhandled exceptions at the container level. (Normally, this is set in the configuration file.) The generic custom error page should have a simple error message that does not reveal any details about the exception happened.

1.      In ASP.NET, it is the custom Error tag in the web.config file
2.      Use an global error handler to capture all unhandled exceptions.
In ASP.NET, it is the Application_Error sub in the global.asax file.
3.      Handle the error in the page level. In ASP.NET, it is the Page_Error sub on the aspx page or associated code behind page


  1. Error handling

Exceptions such as user data validation messages, missing pages and
server errors should be handled by the code so that a custom page is
displayed that does not provide any system information to the user Logging and
alerting of unusual conditions should be enabled and these should allow
subsequent audit.

Solution –
1.      Ensure that Application does not present application error messages to an attacker that could be used in an attack.
2.      Ensure that the application does not present user error messages to an attacker that could be used in an attack

  1. Broken Access Control –

            It is possible to Bypass authentication and forcefully browse the restricted
sections of the website simply by typing the URL in the browser.

Solution –
1.      Proper authentication and authorization mechanism should be implemented.      (a) What types of users can access the system and
(b) What functions & content each of these types of users should be allowed to    access.                            
2.      Access control mechanism should be extensively tested to be sure that there is no way to bypass it.
3.      Testing should be performed using a variety of accounts and extensive attempts to access unauthorized content or functions.
4.      LOGOUT button must be provided which completes user-session by expiring it from both client and server.

  1. Denial of Service Attack

Malicious users might try to swamp the web server with a vast
number of requests or actions that degrade its performance (filling up
logs, uploading large files, undertaking tasks that require a lot of memory
repeatedly) denial of service attacks include locking out valid user
accounts or be caused by coding problems (e.g. memory leaks, resources
not being released). Automated postings will result in a Denial of Service
 (DoS) attack, causing unavailability of space for further feedbacks from genuine
users.

Solution –
1.      By using technique similar to CAPTCHA (Completely Automated Public Turing Test to Tell Computers and Humans Apart) in the query form before any user can submit the data. It’s actually a graphic that will be displayed next to the submit button. Only data, which has valid text form of the graphics, will be accepted and stored on the server. This will even take care of rejecting all automated posts.
2.      This can be applied for the pages before logging into the System , like Feedback and Login

  1.  Eavesdropping of user credentials – 

This refers to sending sensitive information like username and password to the server in plaintext format. Thus making it more vulnerable for eavesdropping by malicious user. Sending data in clear text format is never advisable.

Solution –
1.      Use salted MD5 technique to hash password. The database stores MD5 hash of the password. (MD5 hash is a cryptographic technique in which the actual value can never be recovered.) When a client requests for the login page, the server generates a random number, the salt, and sends it to the client along with the page. A JavaScript code on the client computes the MD5 hash of the password entered by the user. It then concatenates the salt to the hash and re-computes the MD5 hash. This result is then sent to the server. The server picks the hash of the password from its database, concatenates the salt and computes the MD5 hash. If the user entered the correct password these two hashes should match. The server compares the two and if they match, the user is authenticated.
2.      Use of 128-bit SSL encryption can thwart eavesdropping and replay attacks, and is strongly recommended for all Web sites that use Basic authentication. This should be implanted based on the requirements.


  1. URL Manipulation –

By manipulating certain parts of a URL, a hacker can get a web server to deliver web pages he is not supposed to have access to. URL can be manipulated which can cause severe break in the security of the application.
For e.g. URL  http://website.com/temp.aspx?id=3  can be tampered  as
http://website.com/temp.aspx?id=4  to view the  details whose id = 4 .

Solution –  
1.      Encryption of the URL - Encrypting the parameters in URL will help in preventing the user from seeing the critical information. And also if the URL is tampered it can be detected using encryption and digest calculation. If multiple parameters has to be sent then they will be clubbed together using # or any other suitable character, so that encryption and decryption takes place only once and also hash is also computed only once.

2.      Applying page level security for operations like view, add, edit, delete etc. With page level security the person will be able to access the page where he/she can perform the required operations only if the necessary rights are given to him/her

  1. Session Management –

 Once the session is expired, any page, which requires login, should not be 
 accessible.
Solution –
1.      On each and every page, which requires login to access, Session should be checked if session is expired user should be redirected to the login page.

  1. Insert/Update/Delete Refresh Problem –

After performing any database operation like insert, update or delete, if the page is refreshed [by clicking refresh button provided by the user or by hitting F5 key], last operation is repeated. In this case record, which was inserted, is again inserted in the table. Tables having serial number as a primary key and without primary key are affected by refreshing the web page. 

Solution - 
1.      Proper care in coding should be taken on successful operation user should be redirected to the same page again.

  1. Possible Buffer Overflow –

A buffer overflow occurs when the amount of data being written to memory is larger than the amount of memory reserved for the operation. When that occurs, the data being written actually gets written to memory beyond the reserved section. As a result, the extra data has to go somewhere; and you can bet your next paycheck that it will be going somewhere undesirable.

Solution –
1.      Custom application code, you need to review all code that accepts input from users via the HTTP request and ensure that it provides appropriate size checking on all such inputs.

  1. Requirements of Administration Module Based on OWASP Evaluation Checklist

1.      A strong password should be enforced. No passwords, same as the username, should be allowed.
2.      Password Lockout- User accounts should be locked out if the password entered is incorrect after a fixed number of attempts.
3.      Auto-complete should be explicitly set to off for all login forms.
4.      Users should be required to re-authenticate before significant transactions
5.      Implementation of audit trails (Logging Security related events).
6.      Encryption of connectionstring.
7.      Directory structure of the application must be disabled ( IIS settings).
8.      Once the session is expired, the application pages from the cache should not be shown.
9.      Setting up the proper idle connections timeout period for the database.
10.  Setting up the proper max connections for the database.

Thursday, May 10, 2012

Cross Database Bulk Update

Bulk Updating the Record of Table of One DataBase to Another

update  DataBaseone.UM_Users 
set DataBaseone.UM_Users.Name = B.Name
from  DataBaseone.UM_Users as A,DataBaseTwo.UM_Users as B
where A.UserId =  B.UserId  

Back Up Script For SQL Server 2008

USE [master]
GO
/****** Object:  StoredProcedure [dbo].[sp_BackupDatabases]    Script Date: 05/10/2012 17:31:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- ============================================= 
-- Author: Microsoft 
-- Modified By : Shivkumar Deshmukh
-- Modified Date : 2012-March-26
-- Create date: 2010-02-06
-- Description: Backup Databases for SQLExpress
-- Parameter1: databaseName 
-- Parameter2: backupType F=full, D=differential, L=log
-- Parameter3: backup file location
-- =============================================



--EXEC sp_BackupDatabases @databaseName='PwdMhToll',@backupLocation='G:\Toll\', @backupType='F'

ALTER PROCEDURE [dbo].[sp_BackupDatabases]  
            @databaseName sysname = null,
            @backupType CHAR(1),
            @backupLocation nvarchar(200) 
AS 

       SET NOCOUNT ON; 
           
            DECLARE @DBs TABLE
            (
                  ID int IDENTITY PRIMARY KEY,
                  DBNAME nvarchar(500)
            )
           
             -- Pick out only databases which are online in case ALL databases are chosen to be backed up
             -- If specific database is chosen to be backed up only pick that out from @DBs
            INSERT INTO @DBs (DBNAME)
            SELECT Name FROM master.sys.databases
            where state=0
            AND name=@DatabaseName
            OR @DatabaseName IS NULL
            ORDER BY Name
           
            -- Filter out databases which do not need to backed up
            IF @backupType='F'
                  BEGIN
                  DELETE @DBs where DBNAME IN ('tempdb','Northwind','pubs','AdventureWorks')
                  END
            ELSE IF @backupType='D'
                  BEGIN
                  DELETE @DBs where DBNAME IN ('tempdb','Northwind','pubs','master','AdventureWorks')
                  END
            ELSE IF @backupType='L'
                  BEGIN
                  DELETE @DBs where DBNAME IN ('tempdb','Northwind','pubs','master','AdventureWorks')
                  END
            ELSE
                  BEGIN
                  RETURN
                  END
           
            -- Declare variables
            DECLARE @BackupName varchar(100)
            DECLARE @BackupFile varchar(100)
            DECLARE @DBNAME varchar(300)
            DECLARE @sqlCommand NVARCHAR(1000) 
        DECLARE @dateTime NVARCHAR(20)
            DECLARE @Loop int                  
                       
            -- Loop through the databases one by one
            SELECT @Loop = min(ID) FROM @DBs

      WHILE @Loop IS NOT NULL
      BEGIN

-- Database Names have to be in [dbname] formate since some have - or _ in their name
      SET @DBNAME = '['+(SELECT DBNAME FROM @DBs WHERE ID = @Loop)+']'

-- Set the current date and time n yyyyhhmmss format
      SET @dateTime = REPLACE(CONVERT(NVARCHAR(12), GETDATE(),103),'/','') + '_' +  REPLACE(CONVERT(VARCHAR, GETDATE(),108),':','')  

-- Create backup filename in path\filename.extension format for full,diff and log backups
      IF @backupType = 'F'
            SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_FULL_'+ @dateTime+ '.BAK'
      ELSE IF @backupType = 'D'
            SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_DIFF_'+ @dateTime+ '.BAK'
      ELSE IF @backupType = 'L'
            SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_LOG_'+ @dateTime+ '.TRN'

-- Provide the backup a name for storing in the media
      IF @backupType = 'F'
            SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' full backup for '+ @dateTime
      IF @backupType = 'D'
            SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' differential backup for '+ @dateTime
      IF @backupType = 'L'
            SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' log backup for '+ @dateTime

-- Generate the dynamic SQL command to be executed

       IF @backupType = 'F' 
                  BEGIN
               SET @sqlCommand = 'BACKUP DATABASE ' +@DBNAME+  ' TO DISK = '''+@BackupFile+ ''' WITH INIT, NAME= ''' +@BackupName+''', NOSKIP, NOFORMAT'
                  END
       IF @backupType = 'D'
                  BEGIN
               SET @sqlCommand = 'BACKUP DATABASE ' +@DBNAME+  ' TO DISK = '''+@BackupFile+ ''' WITH DIFFERENTIAL, INIT, NAME= ''' +@BackupName+''', NOSKIP, NOFORMAT'        
                  END
       IF @backupType = 'L' 
                  BEGIN
               SET @sqlCommand = 'BACKUP LOG ' +@DBNAME+  ' TO DISK = '''+@BackupFile+ ''' WITH INIT, NAME= ''' +@BackupName+''', NOSKIP, NOFORMAT'        
                  END

-- Execute the generated SQL command
       EXEC(@sqlCommand)

-- Goto the next database
SELECT @Loop = min(ID) FROM @DBs where ID>@Loop

END

Data Dictionary for SQL Server 2008

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

-------------------------------------------------------------------------------
-- Date: May 10, 2012
-- Author: Shivkumar DESHMUKH
-- Desc: Displays the data dictionary for the specified schema. Provide the name
-- of Schema as input to the Stored Procedure
-- Param: @SchemaName - name of the schema to document
--
-- Return: Nothing
-------------------------------------------------------------------------------
-- exec ShowDataDictionary 'pwdtoll'

CREATE PROCEDURE [pwdtoll].[ShowDataDictionary](
@SchemaName VARCHAR(255)
)
AS

BEGIN
DECLARE @DataDictionary TABLE(
RowID [INT] IDENTITY(1,1) PRIMARY KEY,
TableName [VARCHAR](200),
ColumnName [VARCHAR](200),
OrdinalPosition INT,
ColumnDefault [VARCHAR](200),
IsNullable [BIT],
DataType [VARCHAR](20),
CharacterMaximumLength INT,
IsIndexed [BIT],
IsIdentity [VARCHAR](5),
IsPrimaryKey [BIT],
IsForeignKey [BIT],
ReferencedTableName [VARCHAR](200),
ReferencedColumnName [VARCHAR](200),
ObjectDescription [VARCHAR](500)
)

DECLARE @TableList TABLE(
RowID [INT] IDENTITY(1,1) PRIMARY KEY,
TableCatalog [VARCHAR](200),
TableSchema [VARCHAR](200),
TableName [VARCHAR](200),
TableType [VARCHAR](20),
ObjectDescription [VARCHAR](500)
)

DECLARE
@RowNumber INT,
@RowCount INT,
@TableName VARCHAR(255),
@ObjectDescription VARCHAR(500);

-- Create a temp table to hold the primary keys
-- can't do INSERT EXEC with a table variable
IF OBJECT_ID('tempdb..#PkColumns') IS not null
BEGIN
DROP TABLE #PkColumns
END

CREATE TABLE #PkColumns (
RowID [INT] IDENTITY(1,1) PRIMARY KEY,
table_Qualifier VARCHAR(255) NOT NULL,
owner_name VARCHAR(255) NOT NULL,
table_name VARCHAR(255) NOT NULL,
column_name VARCHAR(255) NOT NULL,
key_seq VARCHAR(255) NOT NULL,
Pk_Name VARCHAR(255) NOT NULL
)

-- Load the table list
INSERT @TableList (
TableCatalog,
TableSchema,
TableName,
TableType,
ObjectDescription)
SELECT
t.Table_Catalog,
t.Table_Schema,
t.Table_Name,
t.Table_Type,
CAST(ISNULL(e.VALUE,'') AS VARCHAR(500))
FROM
INFORMATION_SCHEMA.TABLES t
LEFT JOIN ::FN_LISTEXTENDEDPROPERTY (
NULL,
'schema',
@SchemaName,
'table',
@TableName,
NULL,
NULL
) e
ON t.Table_Name = e.objname COLLATE Latin1_General_CI_AS
WHERE
t.Table_Type = 'BASE TABLE' AND t.Table_Schema = @SchemaName

SELECT @RowCount = COUNT(*) FROM @TableList

-- Loop through the tables
SET @RowNumber = 1

WHILE @RowNumber <= @RowCount
BEGIN
SELECT
@TableName = TableName,
@ObjectDescription = ObjectDescription
FROM @TableList
WHERE RowID = @RowNumber

-- Clear primary keys table
DELETE #PkColumns

-- Insert the primary key records retrieved by the system stored procedure
INSERT #PkColumns
EXEC SP_PKEYS @table_name= @TableName, @table_owner = @SchemaName

-- Insert the table name and description for header purposes
INSERT @DataDictionary(
TableName,
ColumnName,
OrdinalPosition,
ColumnDefault,
IsNullable,
DataType,
CharacterMaximumLength,
IsIdentity,
IsPrimaryKey,
IsForeignKey,
ReferencedTableName,
ReferencedColumnName,
ObjectDescription
) VALUES (
@TableName,
'',
0,
'',
0,
'',
0,
0,
0,
0,
'',
'',
@ObjectDescription
)

-- Insert the column schema information
INSERT @DataDictionary (
TableName,
ColumnName,
OrdinalPosition,
ColumnDefault,
IsNullable,
DataType,
CharacterMaximumLength,
IsIndexed,
IsIdentity,
IsPrimaryKey,
IsForeignKey,
ReferencedTableName,
ReferencedColumnName,
ObjectDescription
)
SELECT
t.table_name,
c.column_name,
c.ordinal_position,
ISNULL(c.column_default, ''),
CASE WHEN c.is_nullable = 'Yes' THEN 1 ELSE 0 END,
c.data_type,
ISNULL(c.character_maximum_length, ''),
(
SELECT CASE WHEN COUNT(sys.columns.name) = 0 OR COUNT(sys.columns.name) IS NULL THEN 0 ELSE 1 END
FROM sys.indexes, sys.index_columns, sys.columns
WHERE sys.indexes.OBJECT_ID = sys.index_columns.OBJECT_ID AND
sys.index_columns.OBJECT_ID = sys.columns.OBJECT_ID AND
sys.index_columns.column_id = sys.columns.column_id AND
sys.indexes.OBJECT_ID=OBJECT_ID(@SchemaName + '.' + t.table_name) AND
sys.columns.name = c.column_name
) AS IsIndexed,
(SELECT COLUMNPROPERTY (
OBJECT_ID(@SchemaName + '.' + t.table_name),
c.Column_Name,'IsIdentity')
) AS IsIdentity,
CASE WHEN pk.column_name IS NULL THEN 0
ELSE 1
END AS IsPrimaryKey,
CASE WHEN Fkey.REFERENCED_TABLE_NAME IS NULL THEN 0
ELSE 1
END AS IsForeignKey,
ISNULL(Fkey.REFERENCED_TABLE_NAME, ''),
ISNULL(Fkey.Referenced_Column_Name, ''),
CAST(ISNULL(e.VALUE,'') AS VARCHAR(500)) AS 'ColumnDescription'
FROM
information_schema.tables t
INNER JOIN information_schema.columns C
ON t.table_name = c.table_name
LEFT OUTER JOIN [#PkColumns] Pk
ON PK.column_Name = c.column_Name
LEFT OUTER JOIN (
SELECT
CASE WHEN OBJECTPROPERTY(CONSTID, 'CNSTISDISABLED') = 0
THEN 'Enabled'
ELSE 'Disabled' END AS Status,
OBJECT_NAME(CONSTID) AS Constraint_Name,
OBJECT_NAME(FKEYID) AS Table_Name,
COL_NAME(FKEYID, FKEY) AS Column_Name,
OBJECT_NAME(RKEYID) AS Referenced_Table_Name,
COL_NAME(RKEYID, RKEY) AS Referenced_Column_Name
FROM SYSFOREIGNKEYS
) AS Fkey
ON c.table_name = Fkey.table_name AND c.column_Name = Fkey.Column_Name
LEFT JOIN ::FN_LISTEXTENDEDPROPERTY (
NULL,
'schema',
@SchemaName,
'table',
@TableName,
'column',
default
) e
ON c.Column_Name = e.objname COLLATE Latin1_General_CI_AS
WHERE t.table_name = @TableName
ORDER BY c.ordinal_position

SET @RowNumber = @RowNumber + 1
END

-- Drop the temp table
DROP TABLE #PkColumns

-- Return the data dictionary
SELECT * FROM @DataDictionary d ORDER BY d.TableName, d.OrdinalPosition
END;