June 27, 2011 at 10:08 am
KKK17 (6/26/2011)
but my point is to a specific database.I dont want to transfer all the logins to the secondary server.
Something like this would be easier for you, just be sure to substitute YOURDB for the database name you wish to script SQL Server login accounts for. This script will work for SQL Server 2005 onwards
SELECT 'CREATE LOGIN ' + QUOTENAME(dp.name) + ' WITH PASSWORD = ' +
master.sys.fn_varbintohexstr(sl.password_hash) + ' HASHED, SID = ' +
master.sys.fn_varbintohexstr(dp.sid) + ', DEFAULT_DATABASE = ' +
QUOTENAME(sl.default_database_name) + ', DEFAULT_LANGUAGE = ' + sl.default_language_name +
', CHECK_EXPIRATION = ' +
CASE
WHEN sl.is_expiration_checked = 0 THEN 'OFF'
ELSE 'ON'
END + ', CHECK_POLICY = ' +
CASE
WHEN sl.is_policy_checked = 0 THEN 'OFF'
ELSE 'ON'
END
FROM YOURDB.sys.database_principals dp INNER JOIN sys.sql_logins sl
ON dp.sid = sl.sid
WHERE dp.type = 'S' AND dp.principal_id > 4
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
June 30, 2011 at 2:11 am
Hi All,
Thanks a lot for the replies..
I have found a script which creates logins on the secondary server using linked server. This script runs on secondary server and can be scheduled to run in a job which looks for any new login and creates the same in secondary with the password.
But while trying to test it on Test environment, I have found that, only sql logins are getting transferred not the windows login 🙁
I am not that much good at writing/editing the script as per the requirement..Can anyone help me in editing the attached script. The script needs to create sql as well as windows also on secondary server using open rowset and linked server.
Please let me know if you have any questions.
Your help in this regard is greatly appreciated.
Thanks,
KKK
July 4, 2011 at 4:40 am
KKK17 (6/30/2011)
Hi All,Thanks a lot for the replies..
I have found a script which creates logins on the secondary server using linked server. This script runs on secondary server and can be scheduled to run in a job which looks for any new login and creates the same in secondary with the password.
But while trying to test it on Test environment, I have found that, only sql logins are getting transferred not the windows login 🙁
I am not that much good at writing/editing the script as per the requirement..Can anyone help me in editing the attached script. The script needs to create sql as well as windows also on secondary server using open rowset and linked server.
Please let me know if you have any questions.
Your help in this regard is greatly appreciated.
Thanks,
KKK
This section of code in the script is pulling SQL Server accounts only
WHERE
type = ''''S'''' AND
You'll need to play around with the login types you want to extract and also modify the script to handle the NULL values for the Windows account passwords. The SIDs for the Windows account are not used in account creation so you'll need to filter that too
HTH
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 7, 2011 at 2:50 am
Hi,
I Tested this script which runs on a specific database but it generates the script of only SQL Logins..Not the windows logins..
Could you please modify this to have both SQL and Windows logins extracted in the output.
Your help is highly appreciated.
~KKK
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply