Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Automate New Logins Creation Expand / Collapse
Author
Message
Posted Tuesday, July 05, 2005 12:55 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 03, 2014 6:02 AM
Points: 138, Visits: 259
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/lPeysakhovich/automatenewloginscreation.asp


Post #197103
Posted Tuesday, July 19, 2005 6:50 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 1:24 PM
Points: 292, Visits: 255

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
Post #202325
Posted Tuesday, July 19, 2005 8:45 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, December 26, 2013 9:45 AM
Points: 3,475, Visits: 577

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

Post #202389
Posted Tuesday, July 19, 2005 10:34 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, October 24, 2012 10:45 AM
Points: 11, 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
Post #202458
Posted Tuesday, July 19, 2005 6:19 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, August 18, 2005 4:21 PM
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

Post #202614
Posted Tuesday, July 19, 2005 6:22 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, August 18, 2005 4:21 PM
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

Post #202615
Posted Tuesday, July 19, 2005 7:04 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, August 18, 2005 4:21 PM
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.

 

Post #202624
Posted Sunday, July 24, 2005 6:39 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 03, 2014 6:02 AM
Points: 138, Visits: 259

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

 

Leo Peysakhovich




Post #203772
Posted Sunday, July 24, 2005 6:42 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 03, 2014 6:02 AM
Points: 138, Visits: 259

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




Post #203773
Posted Monday, July 25, 2005 6:54 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, January 30, 2012 5:12 AM
Points: 40, 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.

Post #203923
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse