sp_detach_db causing loss of permissions

I was using sp_detach_db to detach a database and then attempt to transfer with another DOS command. I was receiving a Access denied and couldn't figure why . Investigating closer revealed a change of permissions when using the sp_detach_db.

This was only in the situation of being a windows principal. In other words when attempting to detach a db the system resets the ACL of the file giving the windows user full control, with all permissions being removed

The workaround is to use the security context of a SQL  Logon, an example would be :

 EXECUTE AS LOGIN='sqlLogon'

 exec sp_detach_db 'my_db'

 exec xp_cmdshell 'COPY "E:\mssqlserver\mssql$inst1\data\my_db.mdf" "E:\mssqlserver\mssql$inst1\data\my_db_copy.mdf"'

 REVERT
 GO

Recover from a Suspect database

--Recover from a Suspect db on SQL Server 2005 Standard Service Pack2
---Error: 3414, Severity: 21, State: 1.
--During redoing of a logged operation in database 'MY_DB', an error occurred at log record ID (5006:4238:75).
--Typically, the specific failure is previously logged as an error in the Windows Event Log service.
--Restore the database from a full backup, or repair the database.
--I progressed with these steps , to get the db back to an ONLINE state
--NOTE : This is a last resort , and I only tried , once I'd exhausted recovery from backup etc

ALTER DATABASE MY_DB SET ONLINE;
GO
DBCC CHECKDB (MY_DB, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS;
GO
ALTER DATABASE MY_DB SET EMERGENCY;

GO
DBCC CHECKDB (MY_DB, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS;
GO
ALTER DATABASE MY_DB SET SINGLE_USER;
GO
DBCC CHECKDB (MY_DB, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS;
GO
ALTER DATABASE MY_DB SET MULTI_USER;
GO
SELECT state_desc FROM sys.databases WHERE name='MY_DB';

SQL Server detected a DTC/KTM in-doubt transaction with UOW

There was an unexpected server shutdown , on a sql server box . Upon restart one of the databases went into an IN RECOVERY state ,
and once the database had reached 99% complete  the following message appeared in the SQL Server log files .

SQL Server detected a DTC/KTM in-doubt transaction with UOW  {63E15310-78DA-42DA-8BB1-38B59FDA866E}.Please resolve it following the guideline for Troubleshooting DTC Transactions.

Initially I tried the  : KILL '{63E15310-78DA-42DA-8BB1-38B59FDA866E}' WITH COMMIT   ,but this didn't resolve the issue.

The resolution was achieved by :

Type "mmc.exe" (without the quotation marks) in the Run dialog box to open Microsoft Management Console (MMC).
In MMC, on the Console menu, click Add/Remove Snap-in.
In the Add/Remove Snap-in dialog box, click Add.
In the Add Standalone Snap-in dialog box, click Component Services, and then click Add to add the snap-in.
Click Close.
In the Add/Remove Snap-in dialog box, click OK.
In the console root under Component Services, expand Component Services, point to Computers, point to My Computer, and then click Distributed Transaction Coordinator.
Select Transaction List. You see transactions in the details pane. Right-click the transaction, and then click Resolve. You now have three options: Commit, Abort, or Forget. Choose the one that is appropriate for your situation.
DTC jobs

sp_spaceused not refreshing

I ran the command

EXEC sp_msforeachdb @command1="use ? exec sp_spaceused"

which returned all the relevant information per database on a SQL Server instance I was troubleshooting.
On the basis of the information I adjusted one of the db sizes .

I then ran the same command and notices that the information was not refreshing .

Adding "@updateusage = N'TRUE'" derived the latest information , and reflected the changes I made.

EXEC sp_msforeachdb @command1="use ? exec sp_spaceused @updateusage = N'TRUE';"

hardware jobs

Viewing legacy DTS packages in SSMS

I installed SQL Server Management Studio  , to view some a SQL Server instance 2000.
I attempted to access the packages via the : "Management | Legacy | Data Tramsformation Services"
and got the following message:

SELECT permission denied on table sysdtspackages, database 'master', owner 'dbo'

I was perplexed , because when I attempted to view the DTS packages via SQL Server 2000 Enterprise Manager - I was to view tehm.

I got around the problem , via creating a new role called "DTS_user" and assigned
EXECUTE on sp_enum_dtspackages and sp_get_dtspackage in msdb.

I then went into the user and assigned that role.

DTS jobs

SQL Server restore problem - Error 3154: The backup set holds a backup of a database other than the existing database

On SQLServer 2005 Standard Edition Service Pack 2  - we have a routine that backs up databases onto a dedicated drive.
It is a full backup to disk. This routine does not drop the database

 BACKUP DATABASE MY_DB TO  DISK = 'I:\backups\jvTest20081127.BAK' WITH NOFORMAT,COPY_ONLY, INIT,  NAME = MY_DB  , SKIP, NOREWIND,

NOUNLOAD,  STATS = 10;

A couple of weeks later , I needed one of the databases and attempted a standard RESTORE DATABASE
but without the WITH REPLACE parameter.

   RESTORE DATABASE MY_DB FROM DISK =  'I:\backups\jvTest20081127.BAK'

An error was encountered : Error 3154: The backup set holds a backup of a database other than the existing database
The error occurs because I was  trying to restore the database on another existing active database.

The solution is :

RESTORE DATABASE
   FROM DISK = N'I:\backups\jvTest20081127.BAK'
WITH REPLACE

Storage Jobs

mssqlsystemresource moving error

Trying to run the following :

ALTER DATABASE mssqlsystemresource
MODIFY FILE (NAME = data, FILENAME = 'D:\MSSQLSERVER\MY_SQL_SERVER_INSTANCE\Data\mssqlsystemresource.mdf')
GO
ALTER DATABASE  mssqlsystemresource
MODIFY FILE (NAME = log, FILENAME = 'D:\MSSQLSERVER\MY_SQL_SERVER_INSTANCE\Data\mssqlsystemresource.ldf')
GO

error is :
Could not locate entry in sysdatabases for database 'mssqlsystemresource'

Tried the following  and it solved the problem:

NET START MY_SQL_SERVER_INSTANCE /f /T3608

The "/f" switch - This flag starts SQL Server with minimal configuration and allows updates to system tables. Sometimes a DBA makes config

changes , which won't allow the SQL Server instance to start.The switch is useful , as sometimes it is the only way to correct

configuration mistakes

The "/T3608" flag skips automatic recovery for all databases except the master database at the startup

London DBA jobs  

Account Locked out

One of the application owners sent me a message the other day: Login failed for user ‘sa’ because the account is currently locked out. The system administrator can unlock it.

The following code will solve the issue

--------------------CODE START----------------------------------

use master

ALTER LOGIN sa WITH PASSWORD = 'password' UNLOCK

-----------CODE END-------------------------

SQL Security vacancies

Excel Connect to SQL Server

Excel connect o SQL Server

Connecting to SQL Server from Excel is a relatively straightforward process.
Quite often users want to distribute an Excel document , for example , within
a network. Particuarly as there is a great deal of libraries the user can access from Excel
as a presentation layer.

To implement this solution you will need :
Microsoft Excel
Visual Basic Editor

Step 1: Create your Excel workbook
Step 2: In Visual Basic Editor create a new project
and within Tools | References , select "Microsoft ActiveX Data Objects Library"
Step 3: Insert a module into your new project , and create a subprocedure
Step 4: Within that sub procedure copy and paste the follwoing code:

------------------Start Code------------------------------

' Create a connection object.
Dim cnMyDB As ADODB.Connection
Set cnMyDB = New ADODB.Connection

' Provide the connection string.
Dim strConn As String

'Use the SQL Server OLE DB Provider.
strConn = "PROVIDER=SQLOLEDB;"

'Connect to the Pubs database on the local server.
strConn = strConn & "DATA SOURCE=(local);INITIAL CATALOG=MyDB;"

'Use an integrated login.
strConn = strConn & " INTEGRATED SECURITY=sspi;"

'Now open the connection.
cnMyDB.Open strConn

' Create a recordset object.
Dim rsMyDB As ADODB.Recordset
Set rsMyDB = New ADODB.Recordset

With rsMyDB
' Assign the Connection object.
.ActiveConnection = cnMyDB
' Extract the required records.
.Open "SELECT * FROM MyTable"
' Copy the records into cell A1 on Sheet1.
Sheet1.Range("A1").CopyFromRecordset rsMyDB

' Tidy up
.Close
End With

cnMyDB.Close
Set rsMyDB = Nothing
Set cnMyDB = Nothing

---------------------End Code -------------------------------------

Step 5: Run the code , and check your Excel worksheet
Step 6 : If you have problems , check connection and permissions

Excel jobs
 

Quick Insert

I had a problem regarding saving data (10 million rows) to an archive table prior to updating the records .
This was the quickest way I could find.

declare @act INT
SET @act = (SELECT MIN(AccountMonthNo) FROM CurrentDetail)
SELECT *
INTO table_dest
FROM dbo.CurrentDetail WITH (NOLOCK) 
WHERE AccountMonthNo = @act

**add the deleted bit at this point
Then do a :
CREATE INDEXES ,ALTER TABLE ADD PRIMARY KEY etc

SQL jobs