September 21, 2005 at 2:17 am
Hello
I am trying to prepare a script that will be used with my Log Shipping/DR process.
Once I have rought all Dbs online I would like to step through each database and either drop or add a specific user (which will be the DBO).
The problem that I have is that in some databases the dbo is MachineName\NewUser and others it is NewUser. I would like to drop all these occurences and recreate them so that the user in the db wil be NewUser.
However my logic seems to be a bit over the place as when I run the following script the user that is returned from the (select name from sysusers)appears to be coming from the database that you actually run the script in rather that taking it from the database in the cursor
DECLARE @dbname VARCHAR(250)
DECLARE @Adduser NVARCHAR(250)
DECLARE @Amenduser NVARCHAR(250)
DECLARE @loginname VARCHAR(250)
DECLARE @SQLString NVARCHAR(250)
DECLARE @SQLString1 NVARCHAR(250)
DECLARE databasename CURSOR FOR
SELECT name FROM master.dbo.sysdatabases
WHERE dbid > 4
OPEN databasename
FETCH NEXT
FROM databasename
INTO @dbname
IF @@FETCH_STATUS <> 0
PRINT 'No User Defined Databases Found'
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sqlstring = 'USE ' + @dbname
EXEC sp_executesql @sqlstring
PRINT @dbname + ' ******active db name*****'
SET @loginname = (SELECT name FROM sysusers WHERE name LIKE '%NewUser%')
print @loginname + ' User name'
IF (SELECT COUNT(@loginname)) <> 0
BEGIN
SET @Amenduser = 'EXEC SP_DROPUSER ' + char(39)+ @loginname + char(39) + + char(13) +
'EXEC SP_ADDUSER ' + char(39) + 'MachineName\NewUser' + char(39)+ ','+ char(39)+'NewUser' + char(39)+ ',' + char(39)+ 'db_owner' + char(39)
Print @amenduser + ' Drop and add'
EXEC sp_executesql @amenduser
END
-- ELSE
--BEGIN
-- SET @Adduser = 'EXEC SP_ADDUSER ' + char(39) + 'MachineName\NewUser' + char(39)+ ','+ char(39)+'NewUser' + char(39)+ ',' + char(39)+ 'db_owner' + char(39)
-- Print @adduser + ' Add user'
-- EXEC sp_executesql @adduser
--END
FETCH NEXT FROM databasename INTO @dbname
END
CLOSE databasename
DEALLOCATE databasename
GO
Any help would be grately appreciated as my mind is going round the bend....
Cheers
Scott
September 21, 2005 at 4:27 pm
I have just a suggestion. Have you tried this?
Instead of
SET @sqlstring = 'USE ' + @dbname
EXEC sp_executesql @sqlstring
PRINT @dbname + ' ******active db name*****'
SET @loginname = (SELECT name FROM sysusers WHERE name LIKE '%NewUser%')
You could use
SET @sqlstring = ' SET @loginname = (SELECT name FROM ' + @dbname + 'dbo.sysusers WHERE name LIKE '%NewUser%') '
EXEC sp_executesql @sqlstring
September 22, 2005 at 8:52 am
This might be stupid, but last i heard was that some of the system tables are no longer in the master db. Just a chek.
cheers,
babu.
September 22, 2005 at 9:01 am
Hello All
Many thanks for your responses. Unfortunately my issues are specific to SQL 2000 and not Yukon. I opened the thread in the wrong forum.
Once again thank you for your suggestions.
Doh!!!
Cheers
Scott
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply