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.

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

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

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

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.

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

Leo Peysakhovich

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

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.