2

I have a lab exercise that involves creating a script that uses dynamic SQL and a cursor to loop through each row of a table and then create a loginID and more.

We've done NO work concerning cursors/dynamicSQL because our instructor said we'll never need it. I don't think I'd have any problems with an action query within a cursor, but I'm stuck on how to pass data from a table row into a CREATE LOGIN statement, within a cursor, using a dynamicSQL string.

Should the cursor be nested inside the DynamicSQL code? Vice versa? I haven't attempted to run it as it's not finished, but I think I'm on the wrong track anyway. Can anyone help? Thanks.

Here are the lab instructions and my (incomplete) code so far.

LAB Instructions:

Write a script that uses dynamic SQL and a cursor to loop through each row of the Administrators table and

(1) create a login ID for each row that consists of the administrator’s first and last name with no space between;

(2) set a temporary password of “temp” for each login;

(3) set the default database for the login to the MyGuitarShop database;

(4) create a user for the login with the same name as the login; and

(4) assign the user to the OrderEntry role you created in Exercise 1

My Code:

-- Create Dynamic SQL
DECLARE @DynamicSQL varchar (max)   

-- Cursor variables 
-- Should "SET @DynamicSQL =" go before the cursor variables?
DECLARE @FirstName varchar(255), @LastName varchar(255), 
        @updateCount int;  -- needed?

-- Create cursor, select table rows through which to loop
DECLARE Admin_Cursor CURSOR FOR                         
    SELECT FirstName, LastName  -- Data needed from rows?
    FROM Administrators         -- Table with needed rows
OPEN Admin_Cursor;              -- Open the cursor

-- Get values from first row and store in cursor variables (FirstName/LastName)
FETCH NEXT FROM Admin_Cursor INTO @FirstName, @LastName;

-- set loop condition/begin loop
WHILE (@@FETCH_STATUS = 0)                              
    BEGIN
        SET @DynamicSQL = (CREATE LOGIN 'FirstName' + 'LastName' -- this is where Im lost
    -- some code
    -- some code

     -- begin next iteration of loop
    FETCH NEXT FROM Admin_Cursor INTO @FirstName, @LastName;    
    END;

CLOSE Admin_Cursor;         -- Close the cursor 
DEALLOCATE Admin_Cursor;    -- Erase from database?
EXEC (@DynamicSQL);         -- Execute the dynamicSQL string

Thanks to anyone willing and able to help.

closed as off-topic by mustaccio, Colin 't Hart, Md Haidar Ali Khan, Philᵀᴹ, LowlyDBA Nov 9 '18 at 17:19

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – mustaccio, Md Haidar Ali Khan, Philᵀᴹ, LowlyDBA
If this question can be reworded to fit the rules in the help center, please edit the question.

  • Thanks for the link. I bookmarked it and will be reading it soon. – ZMU Nov 7 '18 at 12:46
1

Try this one, it might need some changes depending on the database context it needs to run in. I added a temporary table to create some records. You can generate the statements without using a cursor however. If you need to directly execute them, then you will need a cursor.

DECLARE  @Administrators TABLE(firstname varchar(255),
                               lastname varchar(255))

INSERT INTO @Administrators VALUES('John','Wick'),
                                  ('Frodo','Baggins'),
                                  ('Gandalf','The Grey')

-- Debug for printing or executing. Printing = 1 , Executing = 0 or NULL
DECLARE @debug bit = 1;
--Create Dynamic SQL
DECLARE @DynamicSQL varchar (max)   

-- Cursor variables 
-- Should "SET @DynamicSQL =" go before the cursor variables?
DECLARE @FirstName varchar(255), @LastName varchar(255), 
        @updateCount int;  -- needed?

-- Create cursor, select table rows through which to loop
DECLARE Admin_Cursor CURSOR  LOCAL FAST_FORWARD FOR                         
    SELECT FirstName, LastName  -- Data needed from rows?
    FROM @Administrators         -- Table with needed rows
OPEN Admin_Cursor;              -- Open the cursor

-- Get values from first row and store in cursor variables (FirstName/LastName)
FETCH NEXT FROM Admin_Cursor INTO @FirstName, @LastName;

-- set loop condition/begin loop
WHILE (@@FETCH_STATUS = 0)                              
    BEGIN

    --(1) create a login ID for each row that consists of the administrator’s first and last name with no space between;
    --(2) set a temporary password of “temp” for each login;
    -- ,CHECK_POLICY  = off means that it will not use the password policy check
        SET @DynamicSQL = 'CREATE LOGIN ' +QUOTENAME(@FirstName+@LastName)+' with password = ''temp'' ,CHECK_POLICY  = off';
        IF @debug =1 
        BEGIN
        print (@DynamicSQL);
        END
        ELSE
        BEGIN
        exec(@DynamicSQL);
        END

        --(3) set the default database for the login to the MyGuitarShop database;
        SET @DynamicSQL = 'ALTER LOGIN '+QUOTENAME(@FirstName+@LastName)+'WITH DEFAULT_DATABASE = [MyGuitarShop]';
        IF @debug =1 
        BEGIN
        print (@DynamicSQL);
        END
        ELSE
        BEGIN
        exec(@DynamicSQL);
        END

        --(4) create a user for the login with the same name as the login; and
        SET @DynamicSQL = 'CREATE USER '+QUOTENAME(@FirstName+@LastName)+' FOR LOGIN '+QUOTENAME(@FirstName+@LastName)+';'
        IF @debug =1 
        BEGIN
        print (@DynamicSQL)
        END
        ELSE
        BEGIN
        exec(@DynamicSQL)
        END

        --(4) assign the user to the OrderEntry role you created in Exercise 1
        SET @DynamicSQL = 'ALTER ROLE OrderEntry ADD MEMBER '+QUOTENAME(@FirstName+@LastName)+';'
        IF @debug =1 
        BEGIN
        print (@DynamicSQL)
        END
        ELSE
        BEGIN
        exec(@DynamicSQL)
        END


     -- begin next iteration of loop
    FETCH NEXT FROM Admin_Cursor INTO @FirstName, @LastName;    
    END;

CLOSE Admin_Cursor;         -- Close the cursor 
DEALLOCATE Admin_Cursor;    -- Erase from database?
  • Upvoted but just a couple of things I would improve - don't need a global, dynamic cursor (see this post) and I'd also use QUOTENAME() for safety over just applying your own square brackets (anything a user has entered, like their first or last name on a form, should be considered a weapon). – Aaron Bertrand Nov 7 '18 at 12:37
  • You are completely right, I edited it, thanks! – Randi Vertongen Nov 7 '18 at 12:42
  • Worked perfectly. My main confusion was whether or not the cursor is part of the dynamicSQL string, and how to transfer the FirstName/LastName rows from the Administrators table to the CREATE and ALTER statements. Your solution makes perfect sense. By the way, the ONE page in our textbook that covers dynamicSQL doesnt use +QUOTENAME, it uses the square brackets. If either of you gentlemen have any reading recommendations (I prefer books) I would love to know them. FYI our textbook is "Murach's SQL Server 2016 for developers". It's just OK, seems to leave out quite a bit. Thanks again! – ZMU Nov 7 '18 at 13:02
  • Glad it helped, a very good read (which is not a book however) on dynamic sql is this blogpost by Erland Sommarskog: sommarskog.se/dynamic_sql.html . He shows you why to use Quotename() instead of brackets and much more information on dynamic SQL. Next to that to improve your main T-SQL knowledge I liked " exam ref 70-761 Querying data with transact-SQL". It's mainly for getting certified though. If you like visual sessions, I would advise GroupBy, it is created by Brent Ozar and features many renowed speakers. – Randi Vertongen Nov 7 '18 at 13:20
  • 1
    I'll check out all of your suggestions. Thank you! – ZMU Nov 7 '18 at 13:27
4

I understand that the exercise is to use a cursor and dynamic SQL, but just to give you another view (and something to take beyond this one class I suppose), you can do a lot of administrative tasks like this without cursors. I feel that when you default to cursors for tasks where you do need to process things iteratively in a loop, you tend to then rely on them for tasks where you don't need a loop at all. So almost always better to think about what you need to do to a set of rows, rather than to each row. In this case, we need to execute the same set of commands against a set of users. Borrowing from the other answer:

DECLARE  @Administrators TABLE(firstname varchar(255),
                               lastname varchar(255));

INSERT INTO @Administrators VALUES('John','Wick'),
                                  ('Frodo','Baggins'),
                                  ('Gandalf','The Grey');

DECLARE @sql nvarchar(max) = N'';

;WITH u AS (SELECT u = QUOTENAME(firstname + lastname) FROM @Administrators)
SELECT @sql += CHAR(13) + CHAR(10)
  + N'CREATE LOGIN ' + u + N' WITH PASSWORD = ''temp'', CHECK_POLICY = OFF;
       ALTER LOGIN ' + u + N' WITH DEFAULT_DATABASE = MyGuitarShop;
       CREATE USER ' + u + N' FROM LOGIN ' + u + N';
       ALTER ROLE  OrderEntry ADD MEMBER ' + u + N';'
FROM u;

PRINT @sql;
--EXEC MyGuitarShop.sys.sp_executesql @sql;

Result:

CREATE LOGIN [JohnWick] WITH PASSWORD = 'temp', CHECK_POLICY = OFF;
  ALTER LOGIN [JohnWick] WITH DEFAULT_DATABASE = MyGuitarShop;
  CREATE USER [JohnWick] FROM LOGIN [JohnWick];
  ALTER ROLE OrderEntry ADD MEMBER [JohnWick];
CREATE LOGIN [FrodoBaggins] WITH PASSWORD = 'temp', CHECK_POLICY = OFF;
  ALTER LOGIN [FrodoBaggins] WITH DEFAULT_DATABASE = MyGuitarShop;
  CREATE USER [FrodoBaggins] FROM LOGIN [FrodoBaggins];
  ALTER ROLE OrderEntry ADD MEMBER [FrodoBaggins];
CREATE LOGIN [GandalfThe Grey] WITH PASSWORD = 'temp', CHECK_POLICY = OFF;
  ALTER LOGIN [GandalfThe Grey] WITH DEFAULT_DATABASE = MyGuitarShop;
  CREATE USER [GandalfThe Grey] FROM LOGIN [GandalfThe Grey];
  ALTER ROLE OrderEntry ADD MEMBER [GandalfThe Grey];
  • Thank you so much for the reply. Even though I do have to use a cursor for the LAB, I copied your code and will use a version of it to test against the database we use in the class. As far as not using a cursor, what if, just for the sake of knowledge, I didn't have access to the Administrators names but still had generate a loginID, password etc. Is there a way to use a loop without creating a cursor to assign the data from the table rows as values for the loginID, password etc.? Also do you have any reading recommendations that may be more thorough than "Murach's SQL Server 2016"? Thanks! – ZMU Nov 7 '18 at 13:10
  • Please disregard my question about loops that aren't cursors. I read your code more thoroughly and I now understand how to get the rows from the appropriate table using your example. – ZMU Nov 7 '18 at 13:31

Not the answer you're looking for? Browse other questions tagged or ask your own question.