Column Encryption And Decryption In MSSQL

-- Create Master Key
CREATE MASTER KEY ENCRYPTION BY
    PASSWORD ='mandep@123'
GO
  
-- Create Certificate
CREATE CERTIFICATE test
    WITH SUBJECT='mandeep'
GO
 
 
-- Create Symmetric Key
CREATE SYMMETRIC KEY TESTKEY
    WITH ALGORITHM = TRIPLE_DES
    ENCRYPTION BY CERTIFICATE tEST
GO

CREATE TABLE USERS (UID INT IDENTITY(100,1),UFNAME VARCHAR(100),ULNAME VARCHAR(100),ULOGINDI VARCHAR(50),
UPASSWORD VARBINARY(256))

-- Create a Procedure to Insert Data in Table 

CREATE PROC InsertUSER
  @UFNAME VARCHAR(100),
  @ULNAME VARCHAR(100),
  @ULOGINDI VARCHAR(12),
  @UPASSWORD VARCHAR(20)
AS
BEGIN
-- you must open the key as it is not already
    OPEN SYMMETRIC KEY TESTKEY
        DECRYPTION BY CERTIFICATE TEST;
    
-- Insert statement
    INSERT INTO [USERS]
    (UFNAME, ULNAME, ULOGINDI, UPASSWORD)
    VALUES
    (@UFNAME, @ULNAME, @ULOGINDI,
     EncryptByKey(Key_GUID('TESTKEY'), @UPASSWORD));
     
END;

Predefined SQL Database Roles In Sql Server

SQL roles make your work easier, they allow assigning permissions to a role, or group of users, instead to individual users

Predefined SQL database roles are:

db_owner - members have full access
db_accessadmin - members can manage Windows groups and SQL Server logins
db_datareader - members can read all data
db_datawriter - Members can add, delete, or modify data
db_ddladmin - members can run data definition statements
db_securityadmin - members can modify role membership and manage permissions
db_bckupoperator - members can create backups
db_denydatareader - members cannot see the database data
db_denydatawriter - members cannot change/delete database data

Reserved keywords In Sql Server

Reserved keywords

Avoid using reserved keywords for SQL Server database object names. If you do, make sure you use either quoted identifiers or delimited identifiers

Here are the lists of SQL Server, ODBC and future SQL Server reserved keywords

SQL Reserved Key words List.

ADD	EXTERNAL	PROCEDURE
ALL	FETCH	PUBLIC
ALTER	FILE	RAISERROR
AND	FILLFACTOR	READ
ANY	FOR	READTEXT
AS	FOREIGN	RECONFIGURE
ASC	FREETEXT	REFERENCES
AUTHORIZATION	FREETEXTTABLE	REPLICATION
BACKUP	FROM	RESTORE
BEGIN	FULL	RESTRICT
BETWEEN	FUNCTION	RETURN
BREAK	GOTO	REVERT
BROWSE	GRANT	REVOKE
BULK	GROUP	RIGHT
BY	HAVING	ROLLBACK
CASCADE	HOLDLOCK	ROWCOUNT
CASE	IDENTITY	ROWGUIDCOL
CHECK	IDENTITY_INSERT	RULE
CHECKPOINT	IDENTITYCOL	SAVE
CLOSE	IF	SCHEMA
CLUSTERED	IN	SECURITYAUDIT
COALESCE	INDEX	SELECT
COLLATE	INNER	SEMANTICKEYPHRASETABLE
COLUMN	INSERT	SEMANTICSIMILARITYDETAILSTABLE
COMMIT	INTERSECT	SEMANTICSIMILARITYTABLE
COMPUTE	INTO	SESSION_USER
CONSTRAINT	IS	SET
CONTAINS	JOIN	SETUSER
CONTAINSTABLE	KEY	SHUTDOWN
CONTINUE	KILL	SOME
CONVERT	LEFT	STATISTICS
CREATE	LIKE	SYSTEM_USER
CROSS	LINENO	TABLE
CURRENT	LOAD	TABLESAMPLE
CURRENT_DATE	MERGE	TEXTSIZE
CURRENT_TIME	NATIONAL	THEN
CURRENT_TIMESTAMP	NOCHECK	TO
CURRENT_USER	NONCLUSTERED	TOP
CURSOR	NOT	TRAN
DATABASE	NULL	TRANSACTION
DBCC	NULLIF	TRIGGER
DEALLOCATE	OF	TRUNCATE
DECLARE	OFF	TRY_CONVERT
DEFAULT	OFFSETS	TSEQUAL
DELETE	ON	UNION
DENY	OPEN	UNIQUE
DESC	OPENDATASOURCE	UNPIVOT
DISK	OPENQUERY	UPDATE
DISTINCT	OPENROWSET	UPDATETEXT
DISTRIBUTED	OPENXML	USE
DOUBLE	OPTION	USER
DROP	OR	VALUES
DUMP	ORDER	VARYING
ELSE	OUTER	VIEW
END	OVER	WAITFOR
ERRLVL	PERCENT	WHEN
ESCAPE	PIVOT	WHERE
EXCEPT	PLAN	WHILE
EXEC	PRECISION	WITH
EXECUTE	PRIMARY	WITHIN GROUP
EXISTS	PRINT	WRITETEXT
EXIT	PROC