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.
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,
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
I hope you understand with above provided example.
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.
I hope this tip help you to secure your password.
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
EXEC sp_msdependencies ‘[Production].[Product]’
When I run this command I get result as shown in below figure
I hope this tip may help you somewhere.
Thanks for reading.
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
see below snap for detail
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.
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
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.
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
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
sp_configure ‘show advanced options’, 1;
sp_configure ‘max server memory’, 4096; — 4GB
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.
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;
EXEC sp_configure ‘xp_cmdshell’, 1;
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;
EXEC sp_configure ‘xp_cmdshell’, 0;
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
Step 2:- When you go with option one you will get a new screen. In that new screen select facet “Surface Area Configuration”
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.