Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Automate New Logins Creation


Automate New Logins Creation

Author
Message
Leo Peysakhovich
Leo Peysakhovich
SSC-Enthusiastic
SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)

Group: General Forum Members
Points: 161 Visits: 339
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/lPeysakhovich/automatenewloginscreation.asp



Tatsu
Tatsu
Old Hand
Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)

Group: General Forum Members
Points: 302 Visits: 307

Very nice article.

Another thing you can do if the password generator is a command line application is use xp_cmdshell to run the application and output the results to a table. I learned about this technique from an article I read here and use it to get a directory listing of backup files on the disk to be compared to the backup history tables in msdb.

When I build a new application, I script out the logins from the development server and provide them to the DBA in a script. The DBA can change the passwords if necessary but the logins are assured of having the same SID regardless of which environment we're working in. This makes it really easy to restore a production copy of the database on the development server.



Bryant E. Byrd, BSSE MCDBA MCAD
Business Intelligence Administrator
MSBI Administration Blog
Yelena Varshal
Yelena Varshal
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3480 Visits: 593

Good Article.

I agree with Bryant Byrd who suggests running xp_cmdshell. I would run it as a last step to run a VB script to send SMTP email to a user to notify him/her of the new password. Remember the original requirement from the beginning of the article? For those choosen who have Excange on the network and can use SQL Mail they can use that. Or use SMTPDLL third party product mentioned here a couple of times.

Yelena




Regards,
Yelena Varshal

Charley Jones
Charley Jones
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 4
Kudos on the use of openrowset to test the userid/password combination. Always great to check your work, especially if you can automate that check!

Charley Jones
A+, ITIL, MCAD, MCDBA, MCITP: Sql Server 2005 Adminstration, MCT,
MCTS: Sql Server 2005, MCP, MCSA, MCSE, MOUS, PMP
President and Founder S3oLV.Com: Sql Server Society of Las Vegas
President and Founder LVXUG.Com: Las Vegas XNA Users Group
Kenneth Lee
Kenneth Lee
SSC Journeyman
SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)

Group: General Forum Members
Points: 75 Visits: 1

Note that there is a typo in step three. Drop the last ' mark.

A couple of things. This isn't general purpose, the only reason it works is because there aren't any spaces in any of the tables or the DB name. Second thing, I intentionally went into the wrong DB and ran the code. Yep, got the wrong tables listed. Thought I could just exec ('use '+@DB_nme) but the scope wasn't long enough. This will generate the script for any DB. I haven't fixed all possible errors for data entry on @DB_nme.

declare @DB_nme sysname
set @DB_nme = ' pubs '
set NOCOUNT ON
declare @sql nvarchar(500)
if (CHARINDEX('[',@DB_nme, 0) = 0) set @DB_nme = '[' + rtrim(ltrim(@DB_nme)) + ']'
set @sql = 'use ' + @DB_nme + '
print ''set NOCOUNT ON''
select ''select count(*) as ['' + table_name + ''] from '+ @DB_nme
+ '.dbo.['' + table_name + '']'' from INFORMATION_SCHEMA.TABLES'

exec sp_executesql @sql


Kenneth Lee
Kenneth Lee
SSC Journeyman
SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)

Group: General Forum Members
Points: 75 Visits: 1

This is my third iteration on this idea. Produces 1 result set, documents the DB and table so this could be used on several DBs to produce a joined list:

declare @DB_nme sysname
set @DB_nme = ' pubs '
set NOCOUNT ON
declare @sql nvarchar(900)
if (CHARINDEX('[',@DB_nme, 0) = 0) set @DB_nme = '[' + rtrim(ltrim(@DB_nme)) + ']'
set @sql = 'use ' + @DB_nme + '
set NOCOUNT ON
declare @sql nvarchar(500)
create table #TablCnt (DBName sysname, TableName sysname, NumRows int)
declare @crs cursor
set @crs = cursor FAST_FORWARD FOR
select ''insert into #TablCnt (DBName, TableName, NumRows)
select ''''' + @DB_nme + ''''',''''['' + table_name + '']'''',count(*) from '+ @DB_nme
+ '.dbo.['' + table_name + '']'' from INFORMATION_SCHEMA.TABLES
open @crs
FETCH NEXT FROM @crs
INTO @sql
WHILE @@FETCH_STATUS = 0
BEGIN
exec (@sql)
FETCH NEXT FROM @crs
INTO @sql
END
close @crs
deallocate @crs
select * from #TablCnt
'

exec sp_executesql @sql


Kenneth Lee
Kenneth Lee
SSC Journeyman
SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)

Group: General Forum Members
Points: 75 Visits: 1

There's a lot of quotes in this script. Using pubs as a DB, the 900 character @sql will look like:


use [pubs]
set NOCOUNT ON
declare @sql nvarchar(500)
create table #TablCnt (DBName sysname, TableName sysname, NumRows int)
declare @crs cursor
set @crs = cursor FAST_FORWARD FOR
select 'insert into #TablCnt (DBName, TableName, NumRows)
select ''[pubs]'',''[' + table_name + ']'',count(*) from [pubs].dbo.[' + table_name + ']' from INFORMATION_SCHEMA.TABLES
open @crs
FETCH NEXT FROM @crs
INTO @sql
WHILE @@FETCH_STATUS = 0
BEGIN
exec (@sql)
FETCH NEXT FROM @crs
INTO @sql
END
close @crs
deallocate @crs
select * from #TablCnt

On the first fetch into the 500 character @sql variable, it would look like:

insert into #TablCnt (DBName, TableName, NumRows)
select '[pubs]','[authors]',count(*) from [pubs].dbo.[authors]

Dumb me. I don't have to have the "use [pubs]" code. I just need to make "from INFORMATION_SCHEMA.TABLES" read like "from [pubs].INFORMATION_SCHEMA.TABLES". I still think I need the two layers of code to get it to work and it's not worth the work to change it now.


Leo Peysakhovich
Leo Peysakhovich
SSC-Enthusiastic
SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)

Group: General Forum Members
Points: 161 Visits: 339

Thanks, it is a good idea to automate even more and add email option as additional step

Leo Peysakhovich





Leo Peysakhovich
Leo Peysakhovich
SSC-Enthusiastic
SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)

Group: General Forum Members
Points: 161 Visits: 339

Thank you. It is always intresting to get new and additional ideas from the professionals





LP-181697
LP-181697
SSC Rookie
SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)

Group: General Forum Members
Points: 48 Visits: 143

Thanks for the thoughts. I am always trying to outline some ideas and general ways to look into a problem. It is possible to create a batch process or stored procedure to accomplish this task and place a database name as one of the parameters. But the whole idea of the article is to show the way for the automation. Anyway this is great idea and great comment. Thanks.


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search