www.nuneworld.net

SELECT IsNull(ColumnName, ' bla bla')
As NewColumnName
FROM TableName

Perfmon

Short:

You are runign in services.mcs  Smlogsvc.exe service and that service is run under Network Service account that have no privilege to read  data from MS SQL bin folder. Locate your MSSQL bin folder add permisons to  Network Service to be able to read.

—

Long: http://support.microsoft.com/kb/912399/en-us

The description for Event ID ( 0 ) in Source ( ODBC ) cannot be found. The local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. You may be able to use the /AUXSOURCE= flag to retrieve this description; see Help and Support for details. The following information is part of the event: Error in d:\nt\enduser\databaseaccess\src\mdac\odbc\core\dm\perf.c(349), Access is denied.

: Failed to create mutex for MS ODBC Perf File Mapping.

Couse:

This problem occurs because the Performance Logs and Alerts service (Smlogsvc.exe) does not have sufficient permissions to access the performance counter DLL or the “bin” directory and “bin\en” directoryfor SQL Server 2005 Analysis Services.

On a Windows XP-based computer, the Performance Logs and Alerts service runs under the Network Service account. However, the Network Service account has limited permissions on the local computer. The Network Service account cannot access the performance counter DLL or the SQL Server 2005 Analysis Services “bin” directory and the “bin\en” directorywhen you collect data from SQL Server 2005 Analysis Services performance counters.

Solution:

To resolve this problem, you must grant the Read & Execute permission to the Network Service account on the performance counter DLL for SQL Server 2005 Analysis Services. The DLL is located in the following folder:

Additionally, you must grant Read permissions to the Network Service account on the “bin” directory and the “bin\en” directory for SQL Server 2005 Analysis Services. For example:

• C:\Program Files\Microsoft SQL Server\MSSQL.x\OLAP\bin
• C:\Program Files\Microsoft SQL Server\MSSQL.x\OLAP\bin\en

Note MSSQL.x represents the corresponding value for the instance ID in the system.

For more information about how to grant permissions to a specific folder in Windows XP, click the following article number to view the article in the Microsoft Knowledge Base:

resource: Microsoft

Tutorial from

This blog post was inspired from a newsgroup discussion. The question basically is:
What do you need to do in order to use xp_cmdshell?

Note that there are obvious security implications of doing this. (I’m not recommending usage of xp_cmdshell in general, this is atechnical blog post!) We first need to think about what happens here, from an architectural level:

Somebody who has logged in to SQL Server executes xp_cmdshell. For this, SQL Server need to spawn a process in Windows. A process in Windows need to execute in a Windows user account.

So, what Windows account is used? If the SQL Server login who is executing xp_cmdshell is sysadmin, then SQL Server will use the service account (it will not “pretend to be somebody else”). But if the login isn’t sysadmin, then we need to configure what Windows account to be used (using sp_xp_cmdshell_proxy_account). Note that this configuration is the same for all non-sysadmins!

But there’s a little bit more to it. Below is an outline of what need to be done. Step 2 and 3 are only needed if the one who is to execute xp_cmdshell isn’t sysadmin. Note that the steps don’t have to be performed in the order listed below.

1. We need to allow usage of xp_cmdshell in general (on 2005). Use “Surface Area Configuration” or sp_configure for this.
2. We need to have a user in the master database which has execute permission on xp_cmdshell. If you are uncertain about the difference between logins and users, you should read up about it in BOL.
3. We need to say what Windows account should be used when a non-sysadmin user is executing xp_cmdshell.

So, here’s the TSQL script that does all above:

–1, allow xp_cmdshell
EXEC sp_configure ‘xp_cmdshell’, 1
RECONFIGURE
GO

–2, grant permission to xp_cmdshell
USE master
CREATE LOGIN JohnDoe WITH PASSWORD = ‘jlkw#.6(’

–Note, we are in the master database!!!
CREATE USER JohnDoe FROM LOGIN JohnDoe

–Run as login x
EXECUTE AS login = ‘JohnDoe’
–Below fails, no execute permission on xp_cmdshell
EXEC xp_cmdshell ‘DIR C:\*.*’
REVERT
GO

–Note, we are in the master database!!!
GRANT EXECUTE ON xp_cmdshell TO JohnDoe

–Try again
EXECUTE AS login = ‘JohnDoe’
–Execution of xp_cmdshell is allowed.
–But I haven’t configured the proxy account…
EXEC xp_cmdshell ‘DIR C:\*.*’
REVERT
GO

–3, specify the proxy account for non-syadmins
–Replace obvious parts!
EXEC sp_xp_cmdshell_proxy_account ‘Domain\WinAccount’,'pwd’
EXECUTE AS login = ‘JohnDoe’
–Execution of xp_cmdshell is allowed.
–And executes successfully!!!
EXEC xp_cmdshell ‘DIR C:\*.*’
REVERT

–Cleanup
EXEC sp_xp_cmdshell_proxy_account null

DROP USER JohnDoe
DROP LOGIN JohnDoe
EXEC sp_configure ‘xp_cmdshell’, 0
RECONFIGURE

—-

if you have problem with step 3 then continue here and after that return to step 3

Everything was ok til step 3! When i run step 3 it says something like this:

An error occurred during the execution of sp_xp_cmdshell_proxy_account. Possible reasons: the provided account was invalid or the ‘##xp_cmdshell_proxy_account##’ credential could not be created. Error code: ‘0′.

The account was ok but SQL stil reporting error… so I create crdental manualy with command:

create credential ##xp_cmdshell_proxy_account## with identity = ‘SERVERNAME\useracount’, secret = ‘42342eddds#’

(i m not on a domain, and servername mast be inculded because if you  not you will same error that windows user is not valid!!!)

next in Microsoft SQL Server Menagment studio  go to server Agent  an in proxies define new proxi

in General

proxy name: some name

credetials: clisk on  … button and select

##xp_cmdshell_proxy_account##

in Principals:

clisc add button and select User  click, OK..

and now run:

EXEC sp_xp_cmdshell_proxy_account ‘Domain\WinAccount’,'pwd’

EXECUTE AS login = ‘JohnDoe’

–Execution of xp_cmdshell is allowed.

–And executes successfully!!!

EXEC xp_cmdshell ‘DIR C:\*.*’

REVERT

and should be OK….

UPDATE:

if  you have a stored procedure that is using bcp xp_cmdchell  and you want to call from your ASP.NET application just add to your stored procedure   EXECUTE AS ‘Jondoe’ , the procedure should be  executed with user that have privilage to start xp_cmdshell otherwise  you if you call your procedure from youar apllication you will recive error something like : The EXECUTE permission was denied on the object ‘xp_cmdshell’, database ‘mssqlsystemresource’, schema ’sys’….  so  to you  u have:

ALTER PROCEDURE [dbo].[procedureNAME] (@parametar1 bigint,@parametar2 varchar(100)) with EXECUTE AS ‘JhonDoe’

AS

BEGIN

…..
end.

who is JhonDoe user  read the tutorial above this update… or here

Windows Genuine Advantage (WGA) is an anti-piracy system enacted by Microsoft that enforces Microsoft Windows online validation of the authenticity of several recent Microsoft operating systems when accessing several Microsoft Windows services, such as Windows Update, and downloading from the Microsoft Download Center.