[Last Call] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 478
  • Last Modified:

Dummy users bulk insert


I am trying to add around 100 users with increasing userid for e.g 001,002...100.
How do I add these in SQL server 2005?  Also, there are a few IDs that already exist in the database.  Will the script/code stop or just ignore those IDs and move on to add remaining records?

Thank you
  • 2
1 Solution
Vijaya Reddy Pinnapa ReddyCommented:
Can you use Active Directory and put all those logins into a single AD Group. Then add that group to SQL Server.

If you have AD that method will save you a lot of time and trouble in the future as you won't have to manage the 100 logins.
Christopher KileCommented:
You understand that you have to add logins for each of these database users.  That said, you can do this using this code - change the user id prefix and the password prefix to suit yourself, but be sure to change the name of the database to your default database:

use master;

SET @maxUsers = 100

DECLARE @userPrefix VARCHAR(256)
SET @userPrefix = 'userid'

DECLARE @passPrefix VARCHAR(256)
SET @passPrefix = '$passPhrase'

SET @dbName = 'databaseName'

SET @eostmt = ';'

DECLARE @counter INT
SET @counter = 1

WHILE @counter <= @maxUsers
    DECLARE @user NVARCHAR(256)
    DECLARE @passPhrase NVARCHAR(256)
    DECLARE @countString VARCHAR(4)
    SET @countString = CONVERT(VARCHAR(4), @counter)
    DECLARE @countLen INT
    SET @countLen = LEN(@countString)
    SET @countString = 
            WHEN @countlen < 4 
                THEN SUBSTRING('0000', 1, 4 - @countLen) + @countString 
    SET @user = CONVERT(NVARCHAR(256), @userPrefix) + CONVERT(NVARCHAR(256), @countString)
    SET @passphrase = CONVERT(NVARCHAR(256), @passPrefix) + CONVERT(NVARCHAR(256), @countString)
    SET @x = 
            N'IF  EXISTS (SELECT * FROM sys.server_principals WHERE name = N''' + @user + N''')' + 
            N'    DROP LOGIN [' + @user + N']' + @eostmt +
            N'CREATE LOGIN [' + @user + N'] WITH PASSWORD=N''' + @passPhrase + N''', DEFAULT_DATABASE=[' + @dbName + N'], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF' + @eostmt +
            N'USE [' + @dbName + N']' + @eostmt +
            N'IF  EXISTS (SELECT * FROM sys.database_principals WHERE name = N''' + @user + N''')' + 
            N'    DROP USER [' + @user + ']' + @eostmt +
            N'CREATE USER [' + @user + N'] FOR LOGIN [' + @user + N'] WITH DEFAULT_SCHEMA=[dbo]' + @eostmt
    SELECT @x
    EXEC sp_executesql @x 
    SET @counter = @counter + 1           

Open in new window

perlwhiteAuthor Commented:
I do not have access to the AD.

Thank you for the code.  However, I am trying to understand it before executing since we do not have a test db to try this on!
My questions are-
1. Use master-  our 'master' is specified under System Databases and I see all the tables we use are listed under another folder name xxxData.   Do I need to modify that line to
use xxxData?
2. Is this code inserting 4 digits IDs?
WHEN @countlen < 4
                THEN SUBSTRING('0000', 1, 4 - @countLen) + @countString

I need to insert IDs from guest001 to guest100.  

Thank you,
Christopher KileCommented:
No,  You need to be in the master database to execute that code.  It does not modify the master database, it just needs to be in the context of the master database.  In fact, the only thing in that code you should modify is the parts I mentioned earlier.  I modified the number of logins for my test to 3 (yes, this code has been thoroughly tested).  Try it with three users yourself first, that will establish it works and if you don't like the results you can delete the users and logins easily enough (that is how I cleaned up after my test).

If you want to generate 3-digit ids, then change that block of code to read:

WHEN @countlen < 3
                THEN SUBSTRING('0000', 1, 3 - @countLen) + @countString

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now