Switching database context within a script or proc
-
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
-
Try something like this:
SET @sql = @db + '..sp_dropuser ' + @name
EXEC sp_executesql @sql
--Jonathan -
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 EngineerThis 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.
-
Thanks guys!!! Jonathan your suggestion worked.
Francis
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply