?
Solved

SQL Script to add domain users in SQL Server

Posted on 2011-10-11
4
Medium Priority
?
207 Views
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 forever...lol....). 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 somewhere...lol? Can anyone provide a sample script where I can edit the default databases, roles, and schemas?


Thanks for your help!
Classic
0
Comment
Question by:Classic1
  • 2
4 Comments
 
LVL 3

Accepted Solution

by:
kinsey earned 2000 total points
ID: 36951759
If the SQL Serve ins in the domain then
--Brackets are required due to the rules for identifiers
CREATE LOGIN [DOMAIN\Account] FROM WINDOWS
GO
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.
0
 
LVL 40

Expert Comment

by:lcohan
ID: 36951971
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:
http://msdn.microsoft.com/en-us/library/ms189121.aspx
0
 

Author Closing Comment

by:Classic1
ID: 36952001
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?

CREATE LOGIN [DomainUser] FROM WINDOWS WITH DEFAULT_DATABASE=[database];
GO
USE database;
GO
CREATE USER [domainuser] FOR LOGIN [domainuser];
GO
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 Server...like 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!!!

Regards,
Classic
0
 
LVL 3

Expert Comment

by:kinsey
ID: 36952137
you dont need the grant of the roles are handling permissions.
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

840 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