May 1, 2010 at 2:05 pm
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
May 1, 2010 at 2:19 pm
MS already supply a script to do just this. google sp_help_revlogin
---------------------------------------------------------------------
May 1, 2010 at 4:43 pm
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[/url]
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
May 2, 2010 at 5:08 am
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
May 3, 2010 at 8:09 am
😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply