simple loop query 1

[peac3]

Hi guys,

I am trying to create loop to drop and create users in user_databases which start from database_id = 5

The query that I'm trying to achieve like this

USE database_name
IF EXISTS (SELECT * FROM sys.database_principals WHERE name = 'AU\username')
      BEGIN
            DROP USER [AU\username];            
      END
CREATE USER [AU\username] FOR LOGIN [AU\username];

Because I don't wanna generate 5-6 blocks query like that, so I'm creating the loop like this code

DECLARE           @DB_Id                  Int,
                  @DB_Max                 Int,
                  @DB_Name          varchar(100),
                  @SQL_SCRIPT       nvarchar(200)

SET @DB_Id = 5
SELECT @DB_Max = max(database_id) FROM sys.databases

WHILE @DB_Id <= @DB_Max
BEGIN

      SELECT @DB_Name = name from sys.databases WHERE database_id = @DB_Id

SELECT @DB_Name

      SET @SQL_SCRIPT = 'USE ' + @DB_Name

      SELECT @SQL_SCRIPT
      EXEC sp_executesql @SQL_SCRIPT

      IF EXISTS (SELECT * FROM sys.database_principals WHERE name = 'AU\username')
      BEGIN
            DROP USER [AU\username];            
      END

    CREATE USER [AU\username] FOR LOGIN [AU\username];

      SET @DB_Id = @DB_Id + 1

END

for some reason the Drop user works only database_id = 5 not for the 6,7,8,etc. but the SELECT @DB_Name is working. I suspect the use @DB_NAME is not working properly.

Any idea how to fix my codes?

Thanks heaps guys,

 

[bborissov]

You must put whole scipt in one String:

    SET @SQL_SCRIPT = 'USE ' + @DB_Name +'
      IF EXISTS (SELECT * 
                       FROM sys.database_principals
                  WHERE name = ''AU\username'')
      BEGIN
            DROP USER [AU\username]
      END
      CREATE USER [AU\username] FOR LOGIN [AU\username]'
     
      EXEC (@SQL_SCRIPT)

      SET @DB_Id = @DB_Id + 1

END

NOT TESTED!!!!

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[peac3]

Hi bborissov,

Thanks for your reply and it worked but I need to add the role as db_owner there and here's my script

DECLARE		@DB_Id			Int,
			@DB_Max			Int,
			@DB_Name		varchar(100),
			@SQL_SCRIPT		nvarchar(200)

SET @DB_Id = 5
SELECT @DB_Max = max(database_id) FROM sys.databases

WHILE @DB_Id <= @DB_Max
BEGIN
 
	SELECT @DB_Name = name from sys.databases WHERE database_id = @DB_Id

	SET @SQL_SCRIPT = 'USE ' + @DB_Name + '

	IF EXISTS (SELECT * FROM sys.database_principals WHERE name = ''AU\username'')
	BEGIN
		DROP USER [AU\username];		
	END

	CREATE USER [AU\username] FOR LOGIN [AU\username];
	EXEC sp_addrolemember N''db_owner'', N''AU\username'';'

	EXEC (@SQL_SCRIPT)

--	EXEC master..sp_addrolemember N'db_owner', N'AU\username';

	SET @DB_Id = @DB_Id + 1

END

the EXEC sp_addrolemember did not work and even I tried put it outside the sql_script still didn't help.

But If I run the query using t-sql it was working, any idea why?

Thanks again,

 

[bborissov]

Split it to two different strings and then execute them:

 SET @SQL_SCRIPT = 'USE ' + @DB_Name + '
    IF EXISTS (SELECT * FROM sys.database_principals WHERE name = ''AU\username'')
    BEGIN
        DROP USER [AU\username];        
    END

    CREATE USER [AU\username] FOR LOGIN [AU\username];
    EXEC (@SQL_SCRIPT)

 SET @SQL_SCRIPT = 'USE ' + @DB_Name + '
    EXEC sp_addrolemember N''db_owner'', N''AU\username'';'

    EXEC (@SQL_SCRIPT)

NOT TESTED!

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[peac3]

Hi bborissov,

It was working! Thanks.

Just wondering how you figured that out?
In T-SQL, we dont need to split them.