SMO Script Method with Logins - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

SMO Script Method with Logins

Author

Topic

stephe40
Posting Yak Master

218 Posts

Posted - 2006-11-06 : 15:47:48

So I have been working to convert some of our VB scripts using DMO to C# using SMO. One of our daily scripts generated sp_addlogin statments for all the logins for a server for DR purposes. Well with DMO I could script out the statements with the passwords in encrypted form.

I could use a statement like:

oLogin.Script (SQLDMOScript_AppendToFile + SQLDMOScript_ToFileOnly + SQLDMOScript_PrimaryObject, loginfil, SQLDMOScript2_UnicodeFile + SQLDMOScript2_EncryptPWD)

This would generate statements like:

exec sp_addlogin N'mylogin', N'<encrypted text>', @logindb, @loginlang, @encryptopt=N'skip_encryption'

This would allow me to create logins on a server with the previous password because the statement would skip encrypting the text a second time.

After converting this script to using SMO, I noticed that it does not script out the passwords. The script method of the login object produces this:

/* For security reasons the login is created disabled and with a random password. */
CREATE LOGIN [cx3] WITH PASSWORD=N'M#158;Åj¤z(�-ÊÅÌºàÀÛ#155;½r#158;ÊæSÍþ#139;Fø', DEFAULT_DATABASE=[CX3], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
ALTER LOGIN [cx3] DISABLE

When I use the ScriptingOptions object to change the target server to SQL 2000 I get this:


/* To avoid disclosure of passwords, the password is generated in script. */
declare @idx as int
declare @randomPwd as nvarchar(64)
declare @rnd as float
select @idx = 0
select @randomPwd = N''
select @rnd = rand((@@CPU_BUSY % 100) + ((@@IDLE % 100) * 100) + 
       (DATEPART(ss, GETDATE()) * 10000) + ((cast(DATEPART(ms, GETDATE()) as int) % 100) * 1000000))
while @idx < 64
begin
   select @randomPwd = @randomPwd + char((cast((@rnd * 83) as int) + 43))
   select @idx = @idx + 1
select @rnd = rand()
end
EXEC master.dbo.sp_addlogin @loginame = N'cx3', @passwd = @randomPwd, @defdb = N'CX3', @deflanguage = N'us_english'

So, is there any way to get SMO to script out create statements for logins with the passwords in encrypted form? Or am I now going to have to manually select from syslogins and generate my own statements?

- Eric

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources