January 24, 2015 at 7:12 am
i have created a new login in primary server and provided dbowner permission to primary db.
how do i transfer this login to secondary server and assign the same permission to secondary db ?
Thanks
January 24, 2015 at 10:45 am
Try this:
SELECT
'create login [' + p.name + '] ' +
case when p.type in('U','G') then 'from windows ' else '' end +
'with ' +
case when p.type = 'S' then 'password = ' + master.sys.fn_varbintohexstr(l.password_hash) + ' hashed, ' +
'sid = ' + master.sys.fn_varbintohexstr(l.sid) +
', check_expiration = ' + case when l.is_expiration_checked > 0 then 'ON, ' else 'OFF, ' end +
'check_policy = ' + case when l.is_policy_checked > 0 then 'ON, ' else 'OFF, ' end +
case when l.credential_id > 0 then 'credential = ' + c.name + ', ' else '' end
else '' end +
'default_database = ' + p.default_database_name +
case when len(p.default_language_name) > 0 then ', default_language = ' + p.default_language_name else '' end
FROM sys.server_principals p
LEFT JOIN sys.sql_logins l ON p.principal_id = l.principal_id
LEFT JOIN sys.credentials c ON l.credential_id = c.credential_id
WHERE p.type in('S','U','G')
AND p.name <> 'sa'
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 25, 2015 at 1:10 pm
You're going to have to do this manually. I would suggest that you find a better way to do this moving forward. Kevin's script is good, but if you have some script for creating logins, like a PoSh script, then you can add a duplicate in there to ensure that when you add a login on the primary instance/database, you also add it to the secondary.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply