DISCOVER_MEMORYUSAGE

The DISCOVER_MEMORYUSAGE command is not well documented. Any chance someone from the dev team could give a brief definition of each field you get in the results of this command?

I'm hoping for a definition so I can provide better documentation here (see the field definitions at the bottom):

http://www.codeplex.com/ASStoredProcedures/Wiki/View.aspx?title=MemoryUsage&referringTitle=Home

Thanks.

I was doing research on this recently and this is the information I gathered:

CanShrink - true if it can shrink (represented in perfmon counter Shrinkable, versus Unshrinkable)

MemoryUsed - bytes of memory used

MemoryAllocated - bytes of memory allocated

MemoryAllocBase - bytes for base (initial) allocation

MemoryAllocFromAlloc - this is for recursive definitions

Elements - count of elements

I hope this helps.

|||

Thanks for the info. I probably shouldn't have asked for "brief" definitions as I was hoping for a little more info. But those definitions are better than nothing!

Some other questions if you should feel so inclined to answer:

1. What's considered an "element"? Does this tie to count of members in a dimension? Count of rows in a partition? Count of attribute hierarchies in a dimension?

2. What are recursive definitions?

3. Would it be more accurate to say that for memory that's shrinkable, it's the MemoryUsed or the MemoryAllocated that can be shrunk?

4. Any general suggestions for things to look for when you're studying the results of DISCOVER_MEMORYUSAGE?

5. Any feedback on the MemoryUsage class would be welcome: http://www.codeplex.com/ASStoredProcedures/Wiki/View.aspx?title=MemoryUsage&referringTitle=Home

Disabling extended stored procedures for security

Our security team wants us to disable access to (or drop) all of the built-in extended stored procedures in MSDE 2000 as they feel it is a vulnerability. Where can I find out which extended procs are safe to disable or how we can disable them during install time? Or, is the security team being too cautious and we should just tell them to leave these intact?

There are some that are extremely useful (perhaps even necessary) for many routine maintenance tasks.

Perhaps your security department just needs to know that you have access to the stored procedures 'locked' down to only administrative users.

|||

The onyl procedure which is questionable to let it enabled it the xp_cmdshell procedure as it can execute a command shell on the server. But... if noone (or only adminstrators) are granted access to this procedure there should be no security impact for you at all. make just sure that noone create a extended stored procedure which is not shipped by MS.

Code Snippet

SELECT

xproc.name AS [Name],

SCHEMA_NAME(xproc.schema_id)AS [Schema],

CAST(

xproc.is_ms_shipped

ASbit)AS [IsSystemObject],

xproc.create_date AS [CreateDate]

FROM

sys.all_objectsAS xproc

WHERE

(xproc.type='X')and(CAST(

xproc.is_ms_shipped

ASbit)=0)

ORDERBY

[Schema] ASC,[Name] ASC

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks Jens. The security dept wants us to drop xp_cmdshell, not just remove the rights. Our MSDE 2000 database stores app settings and is not involved in heavy transactions or replication, etc. So would dropping this cause any harm? I read in the Microsoft KB article that it could affect some other stored procedures that depend on it, but those SPs appear to support enterprise-level features.

|||

I agree with Jens. I would not remove xp_cmdshell since I find it very useful for automating maintenance tasks.

However, set the permissions so that only the sysadmin role can execute xp_cmdshell. (Unless, of course, the worst imaginable sin has been commited and the application is running under the 'sa' account.)

No one, no group, no other role, except the very small list of SQL Server administrators 'should' be in the sysadmin role -AND the sa account 'should' NOT be used at all, and it should be tightly locked down with a strong password.

Since the SQL Server administrators have access to the server, there is nothing that they could do using xp_cmdshell that they couldn't do at an OS level command prompt.

Completely removing xp_cmdshell is folly. Tightly limiting access through appropriate permissions is a 'best practice'.

But if you MUST remove it, there 'may' be little impact. Only time will tell. And you can always put it back if necessary.

Disabling extended stored procedures for security

Our security team wants us to disable access to (or drop) all of the built-in extended stored procedures in MSDE 2000 as they feel it is a vulnerability. Where can I find out which extended procs are safe to disable or how we can disable them during install time? Or, is the security team being too cautious and we should just tell them to leave these intact?

There are some that are extremely useful (perhaps even necessary) for many routine maintenance tasks.

Perhaps your security department just needs to know that you have access to the stored procedures 'locked' down to only administrative users.

|||

The onyl procedure which is questionable to let it enabled it the xp_cmdshell procedure as it can execute a command shell on the server. But... if noone (or only adminstrators) are granted access to this procedure there should be no security impact for you at all. make just sure that noone create a extended stored procedure which is not shipped by MS.

Code Snippet

SELECT

xproc.name AS [Name],

SCHEMA_NAME(xproc.schema_id) AS [Schema],

CAST(

xproc.is_ms_shipped

AS bit) AS [IsSystemObject],

xproc.create_date AS [CreateDate]

FROM

sys.all_objects AS xproc

WHERE

(xproc.type='X')and(CAST(

xproc.is_ms_shipped

AS bit)=0)

ORDER BY

[Schema] ASC,[Name] ASC

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks Jens. The security dept wants us to drop xp_cmdshell, not just remove the rights. Our MSDE 2000 database stores app settings and is not involved in heavy transactions or replication, etc. So would dropping this cause any harm? I read in the Microsoft KB article that it could affect some other stored procedures that depend on it, but those SPs appear to support enterprise-level features.

|||

I agree with Jens. I would not remove xp_cmdshell since I find it very useful for automating maintenance tasks.

However, set the permissions so that only the sysadmin role can execute xp_cmdshell. (Unless, of course, the worst imaginable sin has been commited and the application is running under the 'sa' account.)

No one, no group, no other role, except the very small list of SQL Server administrators 'should' be in the sysadmin role -AND the sa account 'should' NOT be used at all, and it should be tightly locked down with a strong password.

Since the SQL Server administrators have access to the server, there is nothing that they could do using xp_cmdshell that they couldn't do at an OS level command prompt.

Completely removing xp_cmdshell is folly. Tightly limiting access through appropriate permissions is a 'best practice'.

But if you MUST remove it, there 'may' be little impact. Only time will tell. And you can always put it back if necessary.