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

auto generate logins Expand / Collapse
Author
Message
Posted Saturday, May 01, 2010 2:05 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, April 14, 2014 6:44 AM
Points: 87, Visits: 465
Hi,

I am trying to auto generate logins to create a separate create login script. So far i have this:

SELECT 'create login ' + NAME + ' with password = ' + '''' + password + '''' + ',' +
' CHECK_POLICY=OFF ' + ',' + 'DEFAULT_DATABASE= ' + dbname + ' '
FROM syslogins

now the password has to be the correct password generated so i think the hexedecimal code!!
and last of all the select also returns NULL how to get rid of those

thanks in advance

Bryan
Post #914265
Posted Saturday, May 01, 2010 2:19 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 5:25 PM
Points: 5,849, Visits: 12,586
MS already supply a script to do just this. google sp_help_revlogin

---------------------------------------------------------------------

Post #914268
Posted Saturday, May 01, 2010 4:43 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:32 AM
Points: 5,963, Visits: 12,850
Bryan

the script that George mentions is probably the best way to generate create login scripts.
Your script has NULLs because the script is pulling Windows logins (which have no password) from the syslogins table as well as SQL logins. You could write a new script but it would be re inventing the wheel so to speak. With SQL Server 2005 the views you need to use now are sys.server_principals and sys.sql_logins. To create the SID and password hashes i would use the sys.fn_varbintohexstr function and maybe a CASE to differentiate between a Windows and SQL server login.

The MS script can be found here


-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs"
Post #914279
Posted Sunday, May 02, 2010 5:08 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, April 14, 2014 6:44 AM
Points: 87, Visits: 465
Hi Guys,

Thanks for mentioning this option from microsoft. This was what i was looking for. And you're right
about reinventing the wheel.

Bryan
Post #914339
Posted Monday, May 03, 2010 8:09 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:32 AM
Points: 5,963, Visits: 12,850


-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs"
Post #914701
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse