RSS

Monthly Archives: November 2011

reset the identity coulmn

if you want to reset the identity coulmn in any table use The following line resets the Identity value for the table to 0 so that the next record added starts at 1
DBCC CHECKIDENT(‘Table Name’, RESEED, 0)
So i make Curssor to Select all tables with Identity column in any database then make reset to this tables

DECLARE @Table_Name AS NVARCHAR(100)
DECLARE @NewLineChar AS CHAR(2)
SET @NewLineChar = CHAR(13) + CHAR(10)
DECLARE Reset_Identity CURSOR
for
select o.name
from syscolumns c, sysobjects o
where c.status = 128  and c.xtype = 56 and c.colid =1 and c.offset = 2
and o.id = c.id
order by o.name
OPEN Reset_Identity

FETCH NEXT FROM Reset_Identity INTO @Table_Name
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
DECLARE @message varchar(100)
SELECT @message =  ‘DBCC CHECKIDENT(”’ + @Table_Name + ”’, RESEED, 0) ‘  + @NewLineChar +’GO’
PRINT @message
END
FETCH NEXT FROM Reset_Identity INTO @Table_Name
END

CLOSE Reset_Identity
DEALLOCATE Reset_Identity
GO

After you run this code on any database take the result becouse this code will genrate the reset identity column code in the database so if you take the result an run it in any database in this case you make reset  to the identity column

 
1 Comment

Posted by on November 30, 2011 in Script

 

Options for hiding SQL Server code

The WITH ENCRYPTION clause is used to encrypt the definition of SQL Server objects (views, stored procedures, functions ), so that no one may be able to view the definition/code of that specific object. Using the clause while creating an object is fairly easy, but there are some serious points and possible complications.

Denying View Definition permission will hide the object for a specific user and also the user will not be able to see the definition using sp_helptext.

 
Leave a comment

Posted by on November 2, 2011 in Encryption Tips

 

Transparent data encryption (TDE)

Introduction: Encrypting at the Database Level :

Transparent data encryption (TDE) is a new encryption feature introduced in Microsoft® SQL Server™ 2008. to use encryption in Microsoft SQL Server 2005, the column data type must be changed to varbinary;

Q & A :

What happen if you need to restore or detach/attach an encrypted database on a different server or instance?
The certificate that was used to encrypt the database encryption key must be available. Otherwise, the operation will fail.

TDE :
Transparent data encryption is the new database-level encryption feature introduced in SQL Server 2008.
How to Enable TDE :
To enable TDE, you must have the normal permissions associated with creating a database master key and certificates in the master database. You must also have CONTROL permissions on the user database.
To enable TDE :

1- Create Master Key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘some password’;
2- Create Certificate
CREATE CERTIFICATE tdeCert WITH SUBJECT = ‘TDE Certificate’;
3- Create Database Encryption
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE tdeCert
4- Enable TDE in Database
ALTER DATABASE myDatabase SET ENCRYPTION ON

Finally, let’s find the state of the database encryption.
SELECT db_name(database_id), encryption_state
FROM sys.dm_database_encryption_keys

 
1 Comment

Posted by on November 2, 2011 in Encryption Tips