PWDCOMPARE–a hidden function of SQL SERVER TIP #88

 

I the last tip TIP#88 we saw how to encrypt a password. Now in this tip I would like to share how to check encrypted password ?

Means once you stored your encrypted password in database now next step is to compare that particular password with your input password and return results accordingly.

The Syntax of the PWDCOMPARE   is very simple

PWDCOMPARE(‘Password plain text’, ‘Password encrypted form’)

This function return 1 if plain text and hash value  are matched else return o.

For example

Lets suppose we have created a table with 3 columns like userId, username and password

as shown below

DECLARE @tblLogin AS TABLE (UserId INT IDENTITY,
                            Username  VARCHAR(100),
                            EncryptedPassword NVARCHAR(MAX))

Now suppose we have inserted 2 rows in to it wit encrypted password

INSERT INTO @tblLogin VALUES (‘Indiandotnet’,PWDENCRYPT(N’MyPassword’))
INSERT INTO @tblLogin VALUES (‘SQLRaaga’, PWDENCRYPT(N’Test’))

Now, Suppose we have want to write a query which return rows from @tbllogin whose password is Test then it should return SQL Raaga for this

I have to write following query

SELECT * FROM @tblLogin WHERE PWDCOMPARE(N’Test’,EncryptedPassword) = 1

For detail  take a look of below snap

PWDCompare_Indiandotnet

 

I hope you understand with above provided  example.

 

Enjoy !!!

RJ!!

How to Encrypt password in SQL Server ? TIP #87

Security is always a concern for every database developer. How to secure valuable information is one of the major and important aspect.

The first approach toward security to have a strong username & password and the next step is to have password in encrypted form.

Now this article will help you to encrypt your password in hash. Isn’t it interesting ?

So SQL Server provided a function by using that particular simple function we can encrypt a password from plain text to hash.

The valuable function is PWDENCRYPT.  By the name it is clear that it will crease the password.

The syntax is very simple PWDENCRYPT(N’String which you want to encrypt’)

see below snap for more detail.

PWDEncrypted_Indiandotnet

I hope this tip help you to secure your password.

Enjoy !!

Thanks

RJ

How easy to determine table dependencies ? TIP # 86

 

Determine the table dependencies is challenging sometime but we can easily resolve this by using a simple stored procedure which  SQL Server provides.

By using this stored procedure we can easily determine all the dependencies of particular table.

The stored procedure is sp_msdependencies

We can use this stored procedure as shown below

Execute sp_msdependencies ‘tableName’

For example I am using Adventureworks2012 and I want to know the dependencies of product table then I have to write following command

Use AdventureWorks2012
Go
EXEC sp_msdependencies ‘[Production].[Product]’
GO

When I run this command I get result as shown in below figure

sp_msdependecies_Indiandotnet

I hope this tip may help you somewhere.

Thanks for reading.

Enjoy !!!

Rj !!!

“sp_helptext“ – Useful command to view detail TIP #85

 

It might be already known to you but I thought for sharing because I frequently use this command and it is very useful command.

When someone wants to determine detail of a function or stored procedure he/she can use this useful command.

The syntax is very simple. Just write

sp_helptext Storedprocedure/ functionname

For example If I want to determine detail of a stored procedure “proc_FindStudentUsingCorrect” then I have to write following command

sp_helptext proc_FindStudentUsingCorrect

see below snap for detail

sp_helptext

 

You can now copy the result text and check what exactly written in the stored procedure or function.

I hope you will use it in your day to day practice.

Enjoy!!!

RJ

Easy way to diagnose SQL Server “sp_Who2” TIP #84

 

Why the SQL Server is running slow ?

What are the processes running currently on SQL SERVER instance ?

Many other like the above which might help us to understand our current SQL Server instance health can be answered by a simple command “sp_who2”.

“sp_Who2”  is an Undocumented command. You can utilize this command to check current status of your SQL SERVER.

We can run this stored procedure directly in Management studio.

See below snap for detail

sp_who2

if you see above snap you will find the sp_who2 providing login detail, database name, command action which is currently applied , CPU time, DiskIO etc.

You can easily find which spId consuming highest CPU,DISKIO etc.

I hope this stored procedure might help you.

Thanks

RJ

How to configure memory in SQL SERVER ? TIP#83

 

One of the good sentence I remember “When someone has teeth he/she is not having nuts and when someone has nuts he/she not having teeth”. Just joke a part. You understand what I mean to say here. If you have the resources then utilize it.

One of the most most important aspect in performance is Memory.

The main point here is if we have high configured machine which have more than 16 GB RAM but the pain point is our SQL Server is not using available Memory.

Now to configure memory for SQL SERVER is supper easy but on the same time you need to understand how much memory you can assign to SQL SERVER to use because you need some buffer memory to your operating system and other

Now just follow below steps to configure the memory for SQL SERVER

Step 1:- Right click the server and open SQL SERVER Properties

Step 2:- Now select the memory tab and you will find the below screen

Memory 

Step 3:- You can change the above boxed max memory value according to your available memory calculation.

OR you can run following command as well to set the Maximum memory that SQL SERVER can utilize

CommandMemoryset

sp_configure ‘show advanced options’, 1;
GO
RECONFIGURE;
GO
sp_configure ‘max server memory’, 4096; — 4GB
GO
RECONFIGURE;
GO

One more important point I would like to share if your machine is X86 machine in such case you have to use /3GB switch with boot file first.

You can find the instruction to set /3GB switch with following link  https://technet.microsoft.com/en-us/library/bb124810%28v=exchg.65%29.aspx

I hope this article might help you.

Enjoy !!!

Rj!!

How to Enable / Disable Xp_cmdShell in SQL SERVER? TIP #82

Friends,

Sometimes it might be possible that you have to run dos command from SQL SERVER. In such situation you have to enable the xp_cmdShell option of  sql configuration.

To enable this we can write following statement

EXEC sp_configure ‘show advanced options’, 1;
GO
RECONFIGURE;
GO
EXEC sp_configure ‘xp_cmdshell’, 1;
GO
RECONFIGURE;
GO

Just wanted to share that it can be a security thread as well.  So enabling the xp_cmdshell option might be sometime dangerous if we did not handle the SQL injections.

Now to disable this we can write following commands

EXEC sp_configure ‘show advanced options’, 1;
GO
RECONFIGURE;
GO
EXEC sp_configure ‘xp_cmdshell’, 0;
GO
RECONFIGURE;
GO

We can enable / disable this by following steps as well (Below steps will work with SQL SERVER 2008 or higher version )

Step 1: Select facets  option by right clicking SQL database as shown below

Facet

Step 2:-  When you go with option one you will get a new screen. In that new screen select facet “Surface Area Configuration”

Surfacearea

Step 3:- Now all the advance option will be available of Surface area Configuration as shown in below figure you can enable disable Xp_CmdShell.

xp_cmd_shell

 

Thanks

RJ

How to insert value in Identity column ? TIP #80

 

Happy new year 2015 Smile .

Suppose you have a source table and one destination table (Which is exact replica of source table) and you want to copy all the rows of source table into destination table. Now the challenging part here is that there is an identity column and in source & destination table and you want to insert same value of identity column in destination tables column.

Lets understand this by an example.

Suppose you have tblStudentSource table which have StudentId as primary key and with Identity feature

Student_SourceTable

Now you have another table which is tblStudentDest with same column structure which tblStudentSource has

now you want to insert all the value of tblStudentSource into tblStudentDest with keeping the identity value.

In this case you have to write following command.

Identity_Insert_statment

SET IDENTITY_INSERT  tblStudentDest ON;
INSERT INTO tblStudentDest (StudentId,FirstName,LastName,Course,detail) SELECT * FROM tblStudentSource
SET IDENTITY_INSERT  tblStudentDest OFF;

I hope this might help you somewhere.

Thanks

RJ

Happy new Year 2015.

Thanks for your support in 2014

Dear Friends,

Thanks for your support in 2014.

The WordPress.com stats helper monkeys prepared a 2014 annual report for our blog.

Here’s an excerpt:

The concert hall at the Sydney Opera House holds 2,700 people. This blog was viewed about 18,000 times in 2014. If it were a concert at Sydney Opera House, it would take about 7 sold-out performances for that many people to see it.

Click here to see the complete report.