July 5, 2005 at 12:55 pm
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/lPeysakhovich/automatenewloginscreation.asp
July 19, 2005 at 6:50 am
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.
[font="Tahoma"]Bryant E. Byrd, BSSE MCDBA MCAD[/font]
Business Intelligence Administrator
MSBI Administration Blog
July 19, 2005 at 8:45 am
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 Varsha
July 19, 2005 at 10:34 am
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
July 19, 2005 at 6:19 pm
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
July 19, 2005 at 6:22 pm
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
July 19, 2005 at 7:04 pm
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.
July 24, 2005 at 6:39 am
Thanks, it is a good idea to automate even more and add email option as additional step
Leo Peysakhovich
July 24, 2005 at 6:42 am
Thank you. It is always intresting to get new and additional ideas from the professionals
July 25, 2005 at 6:54 am
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.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply