How to recover deleted data from SQL Server

Recover DELETED DATA FROM SQL Server

SQL Server Portal

In all my years of working SQL server, one of the most commonly asked questions has always been “How can we recover deleted record?”

Now, it is very easy to recover deleted data from your SQL server 2005 or above.(Note: This script can recover following data types & compatible with CS collation).

  • image
  • text
  • uniqueidentifier
  • tinyint
  • smallint
  • int
  • smalldatetime
  • real
  • money
  • datetime
  • float
  • sql_variant
  • ntext
  • bit
  • decimal
  • numeric
  • smallmoney
  • bigint
  • varbinary
  • varchar
  • binary
  • char
  • timestamp
  • nvarchar
  • nchar
  • xml
  • sysname

Let me explain this issue demonstrating simple example.

Now, you need to create this procedure to recover your deleted data

Explanation:

How does it work? Let’s go through it step by step. The process requires seven easy steps:

Step-1:

We need to get the deleted records from sql server. By using the standard SQL Server function fn_blog, we can easily get all transaction log (Including deleted data. But…

View original post 866 more words

How to recover modified records from SQL Server without Backup

Recover Updated Data from SQL Server without BackUp

SQL Server Portal

In my previous post “How to recover deleted records from SQL Server”, I received requests to “develop a mechanism that can recover the modified records from SQL Server”

Now, it is very easy to recover modified data from your SQL server 2005 or above.(Note: This script can recover following data types & compatible with CS collation).

  • tinyint
  • smallint
  • int
  • bigint
  • bit
  • char
  • varchar
  • nchar
  • nvarchar
  • datetime
  • smalldatetime
  • money
  • smallmoney
  • decimal
  • numeric
  • real
  • float
  • binary
  • varbinary
  • uniqueidentifier

Let me explain this issue demonstrating simple example.




Now, you need to create this procedure to recover your modified data



Estimated execution time for this stored procedure is 1-3 minutes.

How does it work is explained in Article -2.

Appreciate your feedback on my posts. Please do comment.

View original post

User Restriction in SQL Server

User Restriction in SQL Server

Let’s see how we can restrict users to access only specific objects in Sql Server.

Let’s Create a Login

1. Connect to the SQL Server as SA user.
2. Goto Security–>Login–>New Login

UR1

3. In the “General” Tab, Enter the Login Name. Select SQL Server Authentication
4. Enter Password and Confirm Password.
5. Uncheck “Enforce Password Policy”
6. Click OK

UR2

7. Now we have Created a Login.

Next step is now we are going to Map this Login to a database user.

Goto the Database where you want to give access to the created Login.\

1. Expand the Database. Select Security–>New–>User.

UR3

2. Enter the Username. It is not necessary to have same as that of login name. But just to avoid confusion, I am
creating the username as same as Login name.
3. In the Login Name, Click on the browse button, and select the login that you have created just now.

UR4

4. Goto Securables tab.
5. Click on Search
6. Select “All Object of the Types”

UR5

7. Select “Tables”.

UR6

8. Click OK.
9. Select the table that you want to give the access.
10. In the below tab, Tick the “GRANT” option for “SELECT”.
This means, you are providing the user to view the records in the particular table.
[In the same window, you can select whichever tables that you want to provide access and also specify access levels like “select”, “Insert”, “update” and “delete”]

UR7

11. If you want to restrict the user even to the column level, Click on “Column Properties” and Select the Columns that you want to
give the access and give OK.

UR8

12. Click OK on the database user window.

Now we have done with the creating database user.
In short, we have created a user who have only access to one column named “C1” in the table “dbo.a” in the database “AdventureWorks2008R2”.

Now lets check this user access that we have created now.
Disconnect the SQL Server connection in the object explorer. Connect to the Sql Server using the login we have created just now.

Try to access some other database that the user doesn’t have access to.
It should throw error message as “The Database is not accessible”.

UR9

Expand the database “AdventureWorks2008R2” in object explorer. Expand “Tables”.
As you see, the only table dbo.a is listed out. because the user have access only to that particular table.

UR10

Run the Query “Select * from dbo.a”

You will get error message as
Msg 230, Level 14, State 1, Line 1
The SELECT permission was denied on the column ‘c2’ of the object ‘a”, database ‘AdventureWorks2008R2’, schema ‘dbo’.
Msg 230, Level 14, State 1, Line 1
The SELECT permission was denied on the column ‘c3’ of the object ‘a”, database ‘AdventureWorks2008R2’, schema ‘dbo’.

If you run “Select c1 from dbo.A” then it will work. because we did a column level restriction for this user.

Also If you try to insert, update, or delete, then also you should get a permission denied error because we have not given permission for the user for those statements.