Switching database context within a script or proc

Question

Post reply

Switching database context within a script or proc

fhanlon

SSChampion

Points: 13131
More actions
February 3, 2004 at 3:13 pm

#107619

I would like to be able to switch database context within a script in order to execute the sp_dropuser command. I guess my alternative to sp_drop user is to edit the sysuser table directly but I hate doing that sort of thing. I want to drop users that are not found in an application table called USERS. So far I have:

DECLARE @sql nvarchar(1200)
DECLARE @db sysname
DECLARE @name char(11)
SET @db = ''
WHILE @db IS NOT NULL
BEGIN
SELECT @db = MIN( name ) FROM master.dbo.sysdatabases
WHERE name LIKE 'MDS[_]%'
AND name > @db
IF @db IS NOT NULL
  BEGIN
  SET @name = ''
  WHILE @name IS NOT NULL
   BEGIN
   SET @sql=N'SELECT @name = MIN(s.name) FROM '
   + @db + N'.dbo.sysusers s
   LEFT OUTER JOIN ' + @db + N'.dbo.USERS u ON u.UserID = s.name
   WHERE issqluser = 1
   AND hasdbaccess = 1
   AND name NOT IN (''dbo'', ''Admin'', ''MDSOpTx'')
   AND u.UserID IS NULL
   AND name > @name'
   EXEC sp_executesql @sql,N'@name char(11) OUTPUT',@name OUTPUT
   SET @sql = N'USE '+ @db   -- can't do this
   EXEC sp_executesql @sql   -- can't do this
   EXEC sp_executesql @sql    -- can't do this
   SET @sql = N'sp_dropuser ' + @name
   select @sql
   --EXEC sp_executesql @sql
   END
  END
END

I also though of running this via sp_MSforeachdb but I can only by 128 characters. If I turn the above into a stored proc so executing it fits within the 128 characters then it runs in the context of the database I stored in in (I have over 45 databases to run this in and I was hoping not to have to compile this in every one) Any idea as to how to switch database context?
Francis

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jonathan SSC-Insane Points: 20427 More actions · Answer 1

Try something like this:

SET @sql = @db + '..sp_dropuser ' + @name

EXEC sp_executesql @sql

--Jonathan

gljjr SSChampion Points: 10080 More actions · Answer 2

If Jonathan's suggestion doesn't work use xp_cmdshell and call osql with the command. BTW: That will also work even if you need to do it on another machine

Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer

This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.

fhanlon SSChampion Points: 13131 More actions · Answer 3

Thanks guys!!! Jonathan your suggestion worked.

Francis