Changing the data type of a column in MS SQL Script

Changing the data type of a column

 Author: Faiz Farazi
This Script will change datatype except PK and FK  columns.
-- =============================
USE database_name
GO
declare @colname nchar(100);
declare @tabname nchar(100);
declare @fromtype nchar(100);
declare @totype  nchar(100);
declare @size    int;
----------------------------------------------------------------------------------
----Modify this part----------
----According to your need ---------
----To mention size of the @fomtype---
----Mention in the @size not in ------
----@fromtype like varchar(6400)------
----split like @fromtype = 'varchar'--
----@size     = 6400     --
----------------------------------------------------------------------------------
set @fromtype = 'varchar' -- only change this  (from whidh type you want to change. if it is like varchar(100) discard the (100) )
set @size   = 15 -- change this to the @fromtype size, for example if varchar(6400), then @size = 6400
set @totype = 'nvarchar(15)' -- only change this (include the size like int (100) )
declare tab cursor for Select distinct table_name from information_schema.columns
open tab
fetch next from tab into @tabname
while @@FETCH_STATUS = 0
begin
        declare col cursor for Select column_name from information_schema.columns where data_type = @fromtype and table_name = @tabname and character_maximum_length >= @size;

    open col
    fetch next from col into @colname

    while @@FETCH_STATUS = 0
    begin
       
        print 'Changing '+ltrim(@colname)+' from type '+ltrim(@fromtype)+' to '+ltrim(@totype)
       
        begin try
            exec('alter table '+@tabname+' alter column '+@colname+' '+@totype)
        end try
        begin catch
            PRINT 'errno: ' + ltrim(str(error_number()))
            PRINT 'errmsg: ' + error_message()
        end catch
       
        fetch next from col into @colname
    end

    close col
    deallocate col
    fetch next from tab into @tabname
end

close tab
deallocate tab

How to Install SQL Server 2011/Denali

Microsoft recently released the next version of SQL Server, code-name SQL Server Denali. For now Free Download  from Microsoft Microsoft® SQL Server® code-named 'Denali' . Below Steps will show how to install "Microsoft® SQL Server® code-named 'Denali'.
1.To install  Microsoft SQL Server 2011 Denali CTP 1, you can see  to Installation tab on  the left side of SQL Server Installation Center.Click Planing .

.

2 .Click On Installation  you will see below option Like installation of SQL Server..

3. Click On Maintenance and you will see below feature

4. Click On Tools and you will see below feature

5. Click On  Resource  and you will see below future .

6. Click On Advanced  you will see below future .

7.Click  Options  and you will see below future .

8.Click installation  and Click on New SQL Server stand-alone installation or add features to an  
   existing   installation.

9.Click On Show details and To continue, click OK

10. Enter your product key or use free edition and To continue, click Next.

11. You need to Accept the License Terms and To continue, click Next.

 

12. Click Install   To continue 

13. To continue, click Next.

 14. Click one of the future and To continue, click Next.

 15. click  and select the future you like to add and To continue, click Next.

 16. To continue, click Next.

 17. Click Default instance or name  instance and To continue, click Next.

18. See the space it will use and drive for installation and To continue, click Next.

19. Service account : add your service account and PWD and To continue, click Next.

20. I used Domain ID and PWD - To continue, click Ok.

21. Select collation - I used default and To continue, click Next.

22 . I select Mixed mode  and add the user and To continue, click Next.

23. Enter the location of the Data file and To continue, click Next.

24. File stream enable and disable option and To continue, click Next.

25 . Analysis Service account.and To continue, click Next.

26. Reporting service confi..and To continue, click Next.

27 . Do you like to send the error to Microsoft ? and To continue, click Next.

28 . Below location is the configuration ini file location. (If you like to use for other installation) and 
       click install

 29.Sample of configuration.ini file

 31.Installation in process.

 32.If installation successes - It will looklike  ...  and click close  and restart the OS .

33. SQL Server Denali SQL Server Management Studio look like ...

Securing Database Backup with Transparent Database Encryption (TDE)

SQL server database backup file can copy to any where with ought any permission.SQL Server 2008 comes to provide database backup security by encrypting database files so that data could not be easily restored without consent of database administrator. Transparent Database Encryption (TDE) is the answer for this issue. Let me show how to implement secure database backup-restore with TDE.

First create instance master key encrypted by password and create certificate that would be encrypted by instance master key. These processes will be done under context of master database

USE master

GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD ='password'

GO

CREATE CERTIFICATE TDE_CERTIFICATE

WITH SUBJECT = 'Encryption by TDE';

GO

Now change database context to target database that we  want to encrypt. Create database encryption key by server certificate’ TDE_CERTIFICATE ‘ and choose encryption algorithm provided by SQL Server 2008  AES_256  and turn on database encryption by altering database property with ALTER DATABASE.

USE adventureworks

GO

CREATE DATABASE ENCRYPTION KEY

WITH ALGORITHM = AES_256

ENCRYPTION BY SERVER CERTIFICATE TDE_CERTIFICATE;

GO

USE adventureworks

GO

ALTER DATABASE Adventureworks

SET ENCRYPTION ON;

GO

Let’s do a  database backup of AdventureWorks and restore it to another SQL Server .Once try to restore we will receive An error .The restored database need server certificate from source SQL Server , so backup the server certificate along with private key for decryption function and restore the certificate on desalination  SQL Server .

-- first SQL Server .

USE master

GO

BACKUP CERTIFICATE Tde_cert

TO FILE = 'C:\TDE_CERTIFICATE.cer'

WITH PRIVATE KEY

(

FILE = 'C:\TDE_CERTIFICATE.pvk',

ENCRYPTION BY PASSWORD = 'password'

);

GO

--Second SQL Server.

USE master

GO

CREATE DATABASE MASTER KEY ENCRYPTION BY PASSWORD = 'A@akt!kdk';

GO

CREATE CERTIFICATE Tde_cert

FROM FILE = 'C:\TDE_CERTIFICATE.cer'

WITH PRIVATE KEY

(

FILE = 'C:\TDE_CERTIFICATE.pvk',

DECRYPTION BY PASSWORD = 'password'

);

Now you can restore the database on second SQL Server withought an error.

SQL server View

Create View 

USE AdventureWorks

GO

IF object_id(N'dbo.Top10Sales', 'V') IS NOT NULL

      DROP VIEW dbo.Top10Sales

GO

CREATE VIEW dbo.Top10Sales AS

SELECT TOP 10 * FROM Sales.SalesOrderHeader ORDER BY TotalDue DESC

Create Indexed View

USE AdventureWorks

GO

SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON

SET NUMERIC_ROUNDABORT OFF

IF object_id(N'dbo.Top10Sales', 'V') IS NOT NULL

      DROP VIEW dbo.Top10Sales

GO

CREATE VIEW dbo.Top10Sales

WITH SCHEMABINDING AS

SELECT TOP 10 * FROM Sales.SalesOrderHeader ORDER BY TotalDue DESC

GO

CREATE UNIQUE CLUSTERED INDEX Top10Sales_IndexedView

ON dbo.Top10Sales(OrderDate)

 Drop View 

DROP VIEW dbo.Top10Sales

SQL Server Security

1.HOW to Create Login For SysAdmin User

2.HOW to Create SQL Login Must Change Password

3.HOW to Drop Login

4.HOW to Create Database Role

5.HOW to Drop Role Member

6.HOW to Drop Role

7.HOW to Create Data Reader User

8.HOW to Create Schema User

9.HOW to Create User as DBO

10.HOW to Drop User

==========================================================================

1.HOW to Create Login For SysAdmin User

CREATE LOGIN [Domain\windows_userID] FROM WINDOWS WITH DEFAULT_DATABASE= master

GO

EXEC master..sp_addsrvrolemember @loginame = N'Domain\windows_userID', @rolename = N'sysadmin'

GO

2.HOW to Create SQL Login Must Change Password

CREATE LOGIN Lgin_name

      WITH PASSWORD = N'Change_Password'

      MUST_CHANGE,

      CHECK_POLICY = ON;

GO

3.HOW to Drop Login

DROP LOGIN Login_name

GO

4.HOW to Create Database Role

USE AdventureWorks

GO

-- Create the database role

CREATE ROLE Production_Owner AUTHORIZATION [dbo]

GO

-- Grant access rights to a specific schema in the database

GRANT

      ALTER,

      CONTROL,

      DELETE,

      EXECUTE,

      INSERT,

      REFERENCES,

      SELECT,

      TAKE OWNERSHIP,

      UPDATE,

      VIEW DEFINITION

ON SCHEMA::Production

      TO Production_Owner

GO

-- Add an existing user to the role

EXEC sp_addrolemember N'Production_Owner', N'windows_userID'

GO

5.HOW to Drop Role Member

USE AdventureWorks

GO

-- Create the database role

CREATE ROLE Production_Owner AUTHORIZATION [dbo]

GO

-- Grant access rights to a specific schema in the database

GRANT

      ALTER,

      CONTROL,

      DELETE,

      EXECUTE,

      INSERT,

      REFERENCES,

      SELECT,

      TAKE OWNERSHIP,

      UPDATE,

      VIEW DEFINITION

ON SCHEMA::Production

      TO Production_Owner

GO

-- Add an existing user to the role

EXEC sp_addrolemember N'Production_Owner', N'windows_userID'

GO

-- Drop the user from the role

EXEC sp_droprolemember N'Production_Owner', N'windows_userID'

6.HOW to Drop Role

USE AdventureWorks;

GO

-- Create the database role

CREATE ROLE Production_Owner AUTHORIZATION [dbo];

GO

-- Drop the role

DROP ROLE Production_Owner;

GO

7.HOW to Create Data Reader User

USE AdventureWorks

GO

-- Create a login using a Windows user

CREATE LOGIN [Domain\windows_userID] FROM WINDOWS WITH DEFAULT_DATABASE = AdventureWorks

-- Add the user to the database using their login name and the user name

CREATE USER [Domain\windows_userID]

      FOR LOGIN [Domain\windows_userID]

      WITH DEFAULT_SCHEMA = Production

GO

-- Add user to db_datareader role

EXEC sp_addrolemember N'db_datareader', N'Domain\windows_userID'

GO

8.HOW to Create Schema User

USE AdventureWorks;

GO

-- Create a login using a Windows user

CREATE LOGIN [Domain\windows_userID] FROM WINDOWS WITH DEFAULT_DATABASE = AdventureWorks;

-- Add the user to the database using their login name and the user name

CREATE USER [Domain\windows_userID]

      FOR LOGIN [Domain\windows_userID]

      WITH DEFAULT_SCHEMA = Production;

GO

-- Grant specific access rights to use based on Schema

GRANT

      DELETE,

      EXECUTE,

      INSERT,

      SELECT,

      UPDATE,

      VIEW DEFINITION

ON SCHEMA::[Production]

      TO [Domain\windows_userID];

     

9.HOW to Create User as DBO

USE AdventureWorks

GO

-- For login Login_name, create a user in the database

CREATE USER windows_userID

      FOR LOGIN Login_name

      WITH DEFAULT_SCHEMA = Production

GO

-- Add user to the database owner role

EXEC sp_addrolemember N'db_owner', N'windows_userID'

GO

10.HOW to Drop User

USE AdventureWorks

GO

DROP USER windows_userID

GO