Dummy users bulk insert

Posted on 2012-09-14
Last Modified: 2012-09-28

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
Question by:perlwhite
    LVL 9

    Expert Comment

    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.
    LVL 23

    Accepted Solution

    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;
    DECLARE @maxUsers INT
    SET @maxUsers = 100
    DECLARE @userPrefix VARCHAR(256)
    SET @userPrefix = 'userid'
    DECLARE @passPrefix VARCHAR(256)
    SET @passPrefix = '$passPhrase'
    DECLARE @dbName VARCHAR(256)
    SET @dbName = 'databaseName'
    DECLARE @eostmt NVARCHAR(2)
    SET @eostmt = ';'
    DECLARE @counter INT
    SET @counter = 1
    WHILE @counter <= @maxUsers
        DECLARE @user NVARCHAR(256)
        DECLARE @passPhrase NVARCHAR(256)
        DECLARE @x NVARCHAR(4000)
        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


    Author Comment

    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,
    LVL 23

    Expert Comment

    by:Christopher Kile
    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 run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
    Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Via a live example, show how to setup several different housekeeping processes for a SQL Server.

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now