Creating Sql users from an Active Directory export
-
Hello!
I'm trying to figure out how to add users en masse from an export of users from AD into SQL Server user accounts. The easy part is creating a csv file with the usernames but I have no clue how to extract the username and put it into an sp_adduser statement.
Please help, I have 3000 domain users and I'm not really much of a programmer.
Best regards,
Bran
-
A few questions. Do you want to have all users have the same permission?
If that is the case, you can just make NT Group from AD and add that NT group into SQL server.
If you really really want to do, I think other people have better idea but my approch would be,
1. import csv file into staging table. Assume table name is "AD_Users"
2. Run this "select 'EXEC sp_grantlogin [' + usename+ ']' + char(13) + 'GO' + char(13)
Assume username has "Domain\Username" format, if not, you can change that to like this.
"select 'EXEC sp_grantlogin [Domain\' + loginname + ']' + char(13) + 'GO' + char(13)"
And change above query to assign permission, add to db access, etc.
Still, I am sure there is a lot better way and I still like NT group solution so that you don't have to worry about add/move logins everytime there is new hire and lay off.
Also, I am not sure why all domain user need to access sql server as well.
-
Here is a simple 2 step solution for SQL Logins.
More complex is 1 step cursor solution that I don't publish here.
STEP 1
Import your Active Directory names into any database into any table. For example I am using LastName field of Employees table of Northwind database.
Set Query -> Results in Text in Query Analyzer
Run the following code in Query Analyzer that will create a set of EXEC sp_addlogin statements in query analyzer. The 4 single quotes in a row are for the single quote in the output statements. Do not forget a space after an sp_addlogin and after a comma
Use Northwind
select 'EXEC sp_addlogin ' + '''' + LastName + '''' + ', ' + '''' + LastName + ''''
from Employees
STEP 2
Copy the statements from the result window to a different query window. Either change the database to Master, or add a line
USE MASTER
as a first line of your code. Run your EXEC statemets. Your logins will be created with the password same a s a user name.
Similarily you may compose a select statement to create a list of a statements EXEC sp_grantdbaccess. Look up the syntax and you will see that you may add users to a role as well with this statement. Please, pay attention what database you are using:
To create a set of sp_addlogin statements or a set of sp_grantdbaccess statements you have to be in you database that contains the table with you AD extract
To run sp_addlogin you have to be in Master
To run sp_grantdbaccess statements you need to be in you production database where you want to add users
Yelena
Regards,Yelena Varsha
-
I just looked up the original request and it says that there are 3000 users. I agree with the previous statement of IloveSQL that with this number of users who are the domain users you will be MUCH better off granting access to the domain user group, like a global group / groups. Please, read about a difference between Windows logins in SQL Server and SQL Server standard logins. If you implement a Windows Group approach, you will not have to manage those users. If you implement SQL Standard logins you will need to manage 3000 logins when someone comes to your company or if he leaves. The permissions will be very difficult to manage too.
Is there any way for you to use Windows Authentication?
Yelena
Regards,Yelena Varsha
-
Thanks for the replies Yelena and Ilove.
I'll give your suggestions a try.
Though unfortunately I don't have a say in the matter. I had tried convincing my boss otherwise but boss's word is gospel around here.
Thanks again!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply