SQL Script to add domain users in SQL Server

Posted on 2011-10-11
Last Modified: 2012-05-12
Hi there,
Just wondering if anyone can help me out here...I guess a vendor asked me to add users to the SQL Server via a script (I can do it manually, but that would take We have about 125 users and they need to be mapped to 2 databases. Within 1 database, the users will sorted in 3 roles within the database: Admin, Editor, Viewers and assinged a schema.
Normally, I would create a Workgroup in Active Directory (AD) then add that workgroup in the SQL Server, and apply the roles, schemas, etc...but the vendor said this would not work...
What would be the best practice to accomplish this? I have access to the AD server, so I was thinking this:
Export the users from AD into a Excel file
Run a macro to generate the SQL Syntax code based on those users
Run the script against the SQL Server....

Would this work, or am I out to lunch Can anyone provide a sample script where I can edit the default databases, roles, and schemas?

Thanks for your help!
Question by:Classic1
    LVL 3

    Accepted Solution

    If the SQL Serve ins in the domain then
    --Brackets are required due to the rules for identifiers
    Use Database
    CREATE USER user_name
          FOR LOGIN  [DOMAIN\Account]
          [ WITH DEFAULT_SCHEMA = schema_name ]
    Grant Perm On Obj to user_name

    But I would questions the vendor on the workgroups. They allow the Domain Admin to deligate the function of controling access to the groups on a group by group basis. Giving each user an SQL login requires the DB administrator to make any future changes and control access.
    LVL 39

    Expert Comment

    I think you could do that however I would recommend a  Standard Database "Role Based Security" model where you create a database role, grant all rights you need to that role, and add users to it in order to avoid a user level permission(rights) SQL admin nightmare.

    Please see SQL roles and system stored procs that can help you achieve that:

    Author Closing Comment

    Hi Kinsey,
    Thanks for the quick reply, I appreciate it...yup that did the trick...I was doing some research as well, and this is what I came up with...I was missing where to add the DEFAULT_SCHEMA, but you helped me out....quick question, do I have to use the "Grant Perm On Obj to user_name" line?

    USE database;
    CREATE USER [domainuser] FOR LOGIN [domainuser];
    EXEC sp_addrolemember 'databaserole', [domainuser];

    So pretty much, I'll have to create 3 different scripts, one for ADMINS role, EDITORS role, and VIEWERS role, but that's ok...

    Lol...yeah, you have no idea how many times I've asked that question for assigning Domain Workgroups within the SQL you said, easier to maintain and control access, but for some reason, ESRI doesn't support that capability...

    Anyhow, thanks for your help, it worked!!!

    LVL 3

    Expert Comment

    you dont need the grant of the roles are handling permissions.

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Join & Write a Comment

    Suggested Solutions

    In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
    This video is in connection to the article "The case of a missing mobile phone (". It will help one to understand clearly the steps to track a lost android phone.
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…

    755 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

    19 Experts available now in Live!

    Get 1:1 Help Now