James Howards SQL Blog - All Comments

re: Attaching a data file without the log file...

erick-583394 — Wed, 24 Feb 2010 11:49:46 GMT

thanks really got me out of a bind.

re: Please, Sir, I want some more… orphaned database users.

samir.santra — Mon, 21 Dec 2009 03:38:57 GMT

Fantastic script.

re: Intellisense isn’t working? An IntelliSensible list of Common Causes

rahman82pk — Thu, 10 Dec 2009 10:56:38 GMT

Checked all options. still unable to find solution to working with intellisense

re: Please, Sir, I want some more… orphaned database users.

john.moreno — Fri, 04 Dec 2009 17:42:48 GMT

Any particular reason you're not using:

exec sp_change_users_login 'Report'

I've ran into some permissions issue with it, but it generally works...

re: Please, Sir, I want some more… orphaned database users.

wes_williams — Fri, 04 Dec 2009 14:39:57 GMT

Nice one James. I've also modified the script incorporating two changes. First is Jonathan's change as we have a couple of databases with periods in the name. Second, I've changed from a temp table to a table variable since it's a little cleaner if your server is new enough to support them.

re: Please, Sir, I want some more… orphaned database users.

James Howard — Fri, 04 Dec 2009 12:37:24 GMT

Good spot, I have amended my submission to the script area! Thanks Jonathan

re: Please, Sir, I want some more… orphaned database users.

jonathan allen — Fri, 04 Dec 2009 10:29:08 GMT

Nice script James,

I simply had to adjust the line "...from ?..sysusers..." to be "...from [?]..sysusers..." as we have a couple of databases that use a hyphen "-" in their names!

Thanks

re: Please, Sir, I want some more… orphaned database users.

Tao Klerks — Fri, 04 Dec 2009 09:44:06 GMT

slightly more efficient (because it avoids the function call), but using SQL2000 deprecated objects:

SELECT sysusers.name --, *

FROM sysusers

LEFT JOIN master.dbo.syslogins AS syslogins ON sysusers.sid = syslogins.sid

WHERE sysusers.islogin = 1

AND sysusers.isntname = 0

AND syslogins.sid Is Null

AND sysusers.sid Is Not Null

AND sysusers.sid <> 0x00

AND sysusers.sid <> 0x01

AND sysusers.uid <> 1

I'm sure the same could easily be done using the 2005 and later management views.

re: Please, Sir, I want some more… orphaned database users.

James Howard — Fri, 04 Dec 2009 08:46:13 GMT

No I hadn't, good thinking - I have just added it now. I'll keep that section of the site in mind when blogging on other scripts in future... thanks

re: Please, Sir, I want some more… orphaned database users.

Steve Jones — Thu, 03 Dec 2009 22:33:05 GMT

Nice, did you submit this in the Scripts section as well? So many people look there for this type of code.

Dew Drop – December 2, 2009 | Alvin Ashcraft's Morning Dew

Dew Drop – December 2, 2009 | Alvin Ashcraft's Morning Dew — Wed, 02 Dec 2009 13:42:24 GMT

Pingback from Dew Drop – December 2, 2009 | Alvin Ashcraft's Morning Dew

re: She Sells PowerShell on The Sea Shore

Steve Jones — Tue, 01 Dec 2009 15:37:41 GMT

Good list of articles. Send us some of your favorite code items that explain what you have done with it. I'd like to see more practical examples of where people have chosen it for specific tasks.

Dew Drop – December 1, 2009 | Alvin Ashcraft's Morning Dew

Dew Drop – December 1, 2009 | Alvin Ashcraft's Morning Dew — Tue, 01 Dec 2009 13:06:23 GMT

Pingback from Dew Drop – December 1, 2009 | Alvin Ashcraft's Morning Dew

re: Common Forum Queries: Calculate age from the date of birth

Mitesh Oswal — Fri, 27 Nov 2009 09:16:43 GMT

DECLARE @date datetime,

@tmpdate datetime,

@years int,

@months int,

@days int

SELECT @date = '11/26/80'

SELECT

@years = DATEDIFF(yy,@date,GETDATE())

SELECT

@date= DATEADD(yy,@years,@date)

SELECT

@months = DATEDIFF(mm,@date,GETDATE())

SELECT

@date = DATEADD(mm,@months,@date)

SELECT

@days = DATEDIFF(dd,@date,GETDATE())

SELECT

CASE WHEN (@days <= 0 AND @months <= 0 ) THEN @years - 1 ELSE @years END AS "Year",

CASE WHEN (@days <= 0 AND @months < 0 ) THEN 12+@Months-1

ELSE CASE WHEN @months < 0 THEN 11 ELSE @months END END MONTHS,

CASE WHEN @days < 0 THEN 31+@days ELSE @days END AS Days

re: Maintenance Plan clean up task - not working to plan

rd_in_sd — Tue, 24 Nov 2009 06:50:13 GMT

The things you are mentioning may or may not fix the problem. I did a lot of Google searches too and tried this on every possible permutation of xp_delete_files. Also, upgraded the service packs and applied hot-fixes on test servers.

The problems with this XP are several. The same code works on some servers but not others. MS includes it in maintenance plans as an undocumented extended procedure, and it will be deprecated in future releases or SQL Server.

I took the advice of a few DBA's out there in the cloud and wrote a PowerShell script that does it flawlessly, irrespective of hardware or SQL Server versions. It takes about a day to figure it out and customize it to your environment (another day to deploy) but really worth the effort.

Ron