Sql-server – Howto use typeperf to store performance monitor values in a custom schema

perfmonschemasql server

I have setup a performance monitoring of my SQL server system using typeperf.exe to store the values directly to SQL Server.

I went through the following article, that explains howto use typperf.exe with correct parameters and a counter configuration file to store the results into a SQL server database, after a connection to this SQL server has been configured in a local System-DSN:

Collecting Performance Data into a SQL Server Table
20 January 2011 by Feodor Georgiev

I use a command as article explains like to this one:

TYPEPERF -f SQL -s SqlServer1 -cf “C:\Counters.txt” -si 15 -o SQL:SQLServerDSN!log1 -sc 4

After that I have the monitored values stored in my SQL server (as configured in the SystemDSN) and can easily query those (without having to bother with the different counter types and calculation of correct average values because of accumulated values)… e.g. like this:

SELECT  MachineName ,
    CounterName ,
    InstanceName ,
    CounterValue ,
    CounterDateTime ,
    DisplayString
FROM    dbo.CounterDetails cdt
    INNER JOIN dbo.CounterData cd ON cdt.CounterID = cd.CounterID
    INNER JOIN DisplayToID d ON d.GUID = cd.GUID

It works fine – except one detail: the tables are created in the dbo schema. I would like to have them in another schema.
Anyone knows if this is possible and how?

Edit:
I tried to use Windows Authentication because it always fails using SQL Auth and I found this article explaining that SQL Auth is kind of broken in DSN: http://forums.hexus.net/windows/178393-windows-server-2008-perfmon-logging-database.html

So changed to Windows Auth, I figured out the Account SQLAgent is running on, setup a new Job that runs the Batch and to be sure ECHOed out the user name in a log. It's the user the SQL Agent is running with.

This user has only the server role PUBLIC and is mapped to msdb. So I mapped it to the destination DB (the DB that I have defined in the SystemDSN where I want the PerfCtr tables in) with default schema I want the tables to be in and granted him permission to ALTER the schema and assigned datawriter role for the db.
After the JobAgent ran the job, the PerfMon tables successfully have been created -> but now they are in dbo schema ?
– How can this user create objects in dbo, that from my perspective it does not have permission to ?
– Why is the default database setting for this user not used by the connection defined in the system DSN?

Best Answer

It seems like the schema used will be the default schema of the database user associated with the login you provide to the DSN.

How you set this up in detail depends on factors like whether SQL Authentication is allowed to this SQL Server instance. You can set the default schema with ALTER USER.

Regarding the edit (almost becoming a new question now), from Database-Level Roles:

Members of the db_datawriter fixed database role can add, delete, or change data in all user tables.

You could try granting more granular permissions (though having alter on the schema may already be sufficient). It might also be that the implementation hard-codes use of the dbo schema.

Another thing to check: make sure the login is not a member of the sysadmin fixed server role. The value of DEFAULT_SCHEMA is ignored if the user is a member of the sysadmin fixed server role. All members of the sysadmin fixed server role have a default schema of dbo. Grant CONTROL SERVER to the login instead; this gives exactly the same rights, without the schema side-effect.

Related Question