ERROR 229: The EXECUTE permission was denied on the object 'xp_cmdshell', database 'mssqlsystemresource', schema 'sys'

I once got this error while experimenting :

ERROR 229: The EXECUTE permission was denied on the object 'xp_cmdshell', database 'mssqlsystemresource', schema 'sys'.

So seems like xp_cmdshell is an object in database 'mssqlsystemresource' and schema 'sys'. mssqlsystemresource cannot be accessed without DAC ...

In the first go I could not understand much ..But then i tried to run the same query ( it was a BCP query ) using the windows domain account it was successful ..

So it was clear that this is a permission issue on the sql login that I was using ..
Then after long time searching BOL i found that we need to set the database trustworthy .

ALTER DATABASE mydb SET TRUSTWORTHY ON ...and we were comfortable ..

The other way is to create the xp_cmdshell proxy account to impersonate the non-sysadmin users to allow them to access resources outside the SQL Server.

sp_xp_cmdshell_proxy_account [ NULL | { 'account_name' , 'password' } ]

otherwise you can also use the GUI ..

Hope this helps ..

Script that will help in Identifying query causing TempDB growth

I am not creating any indexes assuming we know how to create them

CREATE TABLE [dbo].[tempUsage](

[Date_Time] [datetime] NOT NULL,

[session_id] [smallint] NULL,

[sql_handle] [varbinary](64) NULL,

[SQL_Text] [varchar](8000) NULL,

[request_internal_objects_alloc_page_count] [bigint] NULL,

[request_internal_objects_dealloc_page_count] [bigint] NULL

) ON [PRIMARY]



GO



CREATE VIEW all_request_usage

AS

SELECT session_id, request_id,

SUM(internal_objects_alloc_page_count) AS request_internal_objects_alloc_page_count,

SUM(internal_objects_dealloc_page_count)AS request_internal_objects_dealloc_page_count

FROM sys.dm_db_task_space_usage

GROUP BY session_id, request_id;



GO



CREATE VIEW all_query_usage

AS

SELECT R1.session_id, R1.request_id,

R1.request_internal_objects_alloc_page_count, R1.request_internal_objects_dealloc_page_count,

R2.sql_handle, R2.statement_start_offset, R2.statement_end_offset, R2.plan_handle

FROM all_request_usage R1

INNER JOIN sys.dm_exec_requests R2 ON R1.session_id = R2.session_id and R1.request_id = R2.request_id;



GO



Let’s run the statement every minute to capture SQL Statement that is causing TempDB growth.

{

INSERT INTO tempUsage

SELECT

GetDate() As Date_Time,

R1.session_id,

R1.sql_handle,

Cast ( R2.text As Varchar(8000)) SQL_Text,

R1.request_internal_objects_alloc_page_count,

R1.request_internal_objects_dealloc_page_count

FROM all_query_usage AS R1

OUTER APPLY sys.dm_exec_sql_text(R1.sql_handle) AS R2

where R1.request_internal_objects_alloc_page_count <> 0;

}

Installing Replication bits on a cluster after upgrading to SP2

Reciently we faced an issue where SQL Server was on SP2 ( clustered Enviornment) and we were not able to install replication ..This is fairly simple on a standalone machine but not on cluster ..

If you try to achieve it through ADD/Remove (appwiz.cpl) program you may get this error : The Installer has encountered an unexpected error. The Error code is 2819 .ControlProductclash1 on dialog Product clash needs a property linked to it .

The solution is to use the commandline setup ..we tried several combinations untill this was successful :

start /wait setup.exe /qb VS=TSQL31 INSTANCENAME=TSQL31 ADDLOCAL=SQL_Replication

Now , Replication is installed but its on RTM :) ...you still need to apply SP2 ..
Moreover , any CU after SP2 will fail as Replication bits are on RTM ...

use the same command with hotfix name instead of setup.exe

Can we detach the databses with Replication ON ..

The answer is no ........we cannot detach the database which is participating in replicaion ..

Consider a scenario where you have hundereds of databases replicated and you fall short of disk :D ...

Disabling/removing Replication will be costly and time consuming ...
But we can follow the simple steps below and everything will be fine ...

1.Run ALTER DATABASE database_name SET OFFLINE.
2.Move the file to the new location.
3.Run ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name, FILENAME = 'new_path/os_file_name'.
4. Run ALTER DATABASE database_name SET ONLINE.

Trust me ...you are done :) ..

Comments are welcome as always ..