SQL Server Performance Forum – Threads Archive

How to Automate SQL Account Generation

Hello,
I have searched for a way to do this through MSSQL Enterprise manager with no luck. I have a text file with usernames and passwords. ie. username,password
username2,password
etc… I would like to create new database for each of these users in one shot rather than having to go one by one creating each database per user through enterprise manager. The database name would be the same as the username. Any ideas or scripts would be appreciated. If you need additional info please let me know. If I can at least get the databases created in this way it would be great. These databases would be default template. I could deal with adding the dbowner security login options manually. If there are ways to automate both even better though. Thanks,
Jason

---

Might not be the most elegant way to do it, but something like this should work… Step 1: in the text file:
username,password
username2,password use find and replace to replact the new line with: ‘) INSERT INTO @LoginTable VALUES (‘ including the ‘ Step 2: use find and replace to replace , with ‘,’ So you sould end up with a whole lot of insert statements like this: INSERT INTO @LoginTable VALUES (‘username’,’password’) INSERT INTO @LoginTable VALUES (‘username2′,’password’)
Step 3: paste the insert statements where indicated in the following code: DECLARE @UserName NVARCHAR(100),
@CreationString NVARCHAR (200) DECLARE @LoginTable TABLE
(
UserName NVARCHAR(100),
[Password] NVARCHAR(100)
) ——————————————Paste your insert statements here————————————- INSERT INTO @LoginTable VALUES (‘username’,’password’) INSERT INTO @LoginTable VALUES (‘username2′,’password’) ——————————————Paste your insert statements here————————————- DECLARE DatabaseCursor CURSOR FOR
SELECT UserName
FROM @LoginTable OPEN DatabaseCursor FETCH NEXT FROM DatabaseCursor INTO @UserName WHILE @@FETCH_STATUS = 0
BEGIN
SET @CreationString = ‘CREATE DATABASE ‘ + @UserName
EXEC sp_executesql @CreationString PRINT @CreationString FETCH NEXT FROM DatabaseCursor INTO @UserName
END CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor and run it! As i said, might not be the most elegant way, but it seemed to work when i tried it! Could probably do something similar with the logins… Ben ‘I reject your reality and substitute my own’ – Adam Savage

---

Thanks Alot! I really like the way you explained it. Very quick response too. I’ll let you know how it works soon. Jason

---

no worries..hope it helps! I often find myself with text files i have to do things with (insert, update, etc)…using find and replace to generate SQL statements has been a handy trick! ‘I reject your reality and substitute my own’ – Adam Savage

---

]]>