Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Using XP_EXECRESULTSET To Obtain Database Size Information and More Expand / Collapse
Author
Message
Posted Wednesday, June 14, 2006 6:38 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, January 25, 2011 9:16 AM
Points: 316, Visits: 4
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/jtshyman/usingxp_execresultsettoobtaindatabasesizeinformati.asp


-- J.T.

"I may not always know what I'm talking about, and you may not either."

Post #287593
Posted Tuesday, June 27, 2006 9:33 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 7:51 AM
Points: 63, Visits: 489

After exchanging questions with J.T. this morning I am including the information as additonal information...

Since the SID column is the SID of the db creator this column may not be useful in this script for comparison of SID>1. The issue I encountered and discussed was that all my databases, save one, were created by sa so the SID column was 1 for all of them, only the database created under my individual user ID had a SID >1. J.T.'s solution is to replace the comparison with the follwing statement.

SELECT NAME FROM MASTER..SYSDATABASES WHERE NAME NOT IN

('MSDB','MASTER','TEMPDB')

 I thank J.T. again for a wonderful article and responsiveness to queries about my results.

 

edited to fix tipos...

Post #290425
Posted Tuesday, June 27, 2006 9:41 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, January 25, 2011 9:16 AM
Points: 316, Visits: 4

Thanks for taking the time to raise questions, Scott. Glad to help.

The SID column is the SID of the creator. Scott pointed out that some servers may have databases that were created by sid=1 (sa) and would be excluded by this logic.

My intent was to prevent the system databases from appearing.

To see all the databases, including the system databases, remove the sid>1 conditonal.

Conversely, you can restrain the results by changing the sid>1 phrase to name not in ('msdb','master','tempdb') or whatever databases you want to exclude.




-- J.T.

"I may not always know what I'm talking about, and you may not either."

Post #290433
Posted Tuesday, June 27, 2006 10:07 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, February 4, 2014 9:35 AM
Points: 1,277, Visits: 1,612
You could try replacing the "sid>1" with "dbid>6". I think that works, too.


Post #290453
Posted Tuesday, June 27, 2006 10:49 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 17, 2007 6:18 AM
Points: 2, Visits: 1

What are the security issues of letting application developers use xp_execresultset?  Is it susceptible to SQL Injection?  And if so, wouldn't it run under the authority of the SQL Server owner?

Post #290469
Posted Tuesday, June 27, 2006 11:12 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 7:51 AM
Points: 63, Visits: 489

I have user databases numbered beginning at 5, system databases respectively are:

1- master

2- tempdb

3- model

4- msdb

5- starts user created dbs

 

so maybe dbid >4?

Post #290485
Posted Tuesday, June 27, 2006 11:21 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, February 4, 2014 9:35 AM
Points: 1,277, Visits: 1,612
We have pub and northwind. That's why I used "dbid>6".


Post #290491
Posted Tuesday, June 27, 2006 11:29 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, January 25, 2011 9:16 AM
Points: 316, Visits: 4
You have Pub and Northwind on a production server?

Typically, they can be removed as they are demo data and really have no place on a production SQL server. Is there a reason you still have them, if this is a production server?



-- J.T.

"I may not always know what I'm talking about, and you may not either."

Post #290494
Posted Tuesday, June 27, 2006 11:33 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, January 25, 2011 9:16 AM
Points: 316, Visits: 4
Well, on general principle I wouldn't let developers use sp_execresultset as it is undocumented. This means Microsoft may choose to change or remove it, as they did in SQL2005, or not support it in versions where it does exist.

As for sql injection, I'm no expert but I am of the belief that a well-written interface would prevent that and not SQL iteself. SQL injection, and someone correct me if I am wrong, is an interface vulnerability and not a SQL one, per se.

I'm pretty sure the sp_execresultset stored proc runs under the authority of the current user. The current user, however, must have rights to query the master database table sysdatabases and each database to sysfiles.



-- J.T.

"I may not always know what I'm talking about, and you may not either."

Post #290496
Posted Tuesday, June 27, 2006 11:49 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, February 4, 2014 9:35 AM
Points: 1,277, Visits: 1,612
I have used NW to do some testing in our prod environments. For example, I wanted to test latency across replicated servers, so I first set up my scripts and jobs, and debugged it using NW. You can't always duplicate a prod environment - at least not with our budget!


Post #290506
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse