[Resolved] Problem with Login.Script Method to re-create the login account

Dec 16, 2011 at 2:53 PM
Edited Dec 16, 2011 at 2:58 PM

Hi all,

I have a question about the Login.Script Method which re-create the login account.

If the login is a SQL login, the Login.Script Method generates the sys.sp_addsrvrolemember commands to assign roles to the login.

But if the login is a windows Login, the Login.Script Method doesn't generate the sys.sp_addsrvrolemember commands.

Do you know why ? is it a bug ?

 

thanks for your help

regards

Coordinator
Dec 16, 2011 at 4:04 PM

I hadn't noticed this before, but sure enough SQL logins script out server role membership while Windows logins do not. In looking at the scriptingoptions class there isn't a setting to override this behavior. Additionally when SQL logins are scripted they are set to disabled and their password  is auto generated--neither of which is very useful in recreating logins.

Here's a test script I ran:

 

get-sqllogin -sqlserver "Win7boot\sql1" | ?{@('Win7boot\u00','isaa') -contains $_.name} | get-sqlscripter�

 

And here's the output:

/* For security reasons the login is created disabled and with a random password. */

CREATE LOGIN [isaa] WITH PASSWORD=N'hÆksÕòU/ˆÓ.T3m‘9Bd†ã>Õ>íJ ', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFFEXEC sys.sp_addsrvrolemember @loginame = N'isaa', @rolename = N'securityadmin'

ALTER LOGIN [isaa] DISABLECREATE LOGIN [Win7boot\u00] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]

Workarounds:

I tend to use sp_help_revlogin to transfer logins. It's been around a while and also takes care of keeping the same password. Here's a link to it:

http://support.microsoft.com/kb/918992

For scripting server role membership, you could roll your own as follows:

get-sqllogin -sqlserver "Win7boot\sql1" | foreach { $login = $_ ; foreach ($member in $login.ListMembers()) { Write-Output $("EXEC sys.sp_addsrvrolemember @loginname = N'{0}', @rolename = N'{1}';" -f $login.name, $member)} }

This produces the following output:

EXEC sys.sp_addsrvrolemember @loginname = N'isaa', @rolename = N'securityadmin';
EXEC sys.sp_addsrvrolemember @loginname = N'NT AUTHORITY\SYSTEM', @rolename = N'sysadmin';
EXEC sys.sp_addsrvrolemember @loginname = N'NT SERVICE\MSSQL$SQL1', @rolename = N'sysadmin';
EXEC sys.sp_addsrvrolemember @loginname = N'NT SERVICE\SQLAgent$SQL1', @rolename = N'sysadmin';
EXEC sys.sp_addsrvrolemember @loginname = N'sa', @rolename = N'sysadmin';
EXEC sys.sp_addsrvrolemember @loginname = N'secadmin', @rolename = N'securityadmin';
EXEC sys.sp_addsrvrolemember @loginname = N'Win7boot\DBAGroup', @rolename = N'sysadmin';
EXEC sys.sp_addsrvrolemember @loginname = N'Win7boot\u00', @rolename = N'sysadmin';

Dec 19, 2011 at 9:12 AM

 

Thanks a lot !!!!!!!!!!!!!!!!!!