Developers have no access to linked servers after creating one

 So, you just created a linked server back to DB2 on MS SQL Server 2008R2. However, the developer calls you and says I getting this error;
 The EXECUTE permission was denied on the object ‘xp_prop_oledb_provider’, database ‘mssqlsystemresource’, schema ‘sys’. (.Net SqlClient Data Provider)

This script assumes the user is basis_admin
use master
go
create user basis_admin for login basis_admin
go
grant exec on sys.xp_prop_OLEDB_provider to basis_admin

Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created. You have either made changes to a table that can't be re-created or enabled the option Prevent saving changes that require the table to be re-created.

I just want to add MS 2008 R2 steps: Tools > Options > Designers > Table and Database Designers then uncheck Prevent saving changes that require table re-creation

How to create an SSIS package to import data

Open BIDS click new project >click Control Flow> Data flow > add source object and then add destination object. Next you can run the project or save it.

Msdb cannot be restored

Ever get stuck restoring a master database from a higher version of SQL server than the current one you just built? Well the post will help you.

Problem:
"Restore failed -The backup of the system database on device
msdb cannot be restored because it
was created by a different version of the server (134218407) than this server
(134218262)."

Solution :
bring the system(sql server) up to date with the hotfixes and service packs...

Suspect databases are not cool

Dba days are always fun. I came in Monday morning to a list of suspect databases. I think this was a SAn issue. But, I still can not prove it. This is what i did to repair them.

Step 1:

Use Master
Go
Sp_resetstatus 'Database Name'
--The above command will reset the status flag of the suspect database.

Step 2:-- Once the command is executed you need to restart the SQL services and check
the status of the database.

Step 3: Execute the below query to check the integrity of the database

Dbcc Checkdb('Database Name')
Step 4: None of these worked, so I ended up restoring the database from a previous day.

Rename a Computer that Hosts a Stand-Alone Instance of SQL Server

Have you ever renamed a windows server after installing SQL server on it? Well, sql still holds the old name if you run select @@servername.
So, there are a few steps to follow after the rename. First, allow master (system database ) to see the new default instance name. Second, restart the database engine.

Open new query windows or sqlcmd:

use master
go

sp_dropserver
GO
sp_addserver , local
GO

Cannot delete job | The DELETE statement conflicted with the Foreign

I got a call from user today who was trying to delete a job from sql agent. The problem is that the maintenance plan still exist. So, you should delete the parent( Maintenance plan) before attempting to delete the child (JOB). However, Microsoft doesn't give you a clear error.

Cannot delete job The DELETE statement conflicted with the Foreign Key-- Read more: http://www.sqldbadiaries.com/2010/11/11/cannot-delete-job-the-delete-statement-conflicted-with-the-foreign-key/#ixzz1qzPlu15d