September 12, 2011 at 3:18 am
I have two server (Primary and secondary). Database SAMPLE is configured for log shipping and in secondary server it is in standby mode. I executed the below query in primary after log shipping is done.
USE [master]
GO
CREATE LOGIN [sample] WITH PASSWORD=N'sample', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [SAMPLE]
GO
CREATE USER [sample] FOR LOGIN [sample]
GO
USE [SAMPLE]
GO
EXEC sp_addrolemember N'db_owner', N'sample'
GO
Immediately, I ran this in secondary
USE [master]
GO
CREATE LOGIN [sample] WITH PASSWORD=N'sample', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
When I check the seconadry after sometime, the user sample gets created in SAMPLE database in secondary server but the user is not mapped to the login sample which was already created.
Can anyone explain this?
Ryan
//All our dreams can come true, if we have the courage to pursue them//
September 12, 2011 at 3:45 am
WHen you create the login on the secondary server you should use the SID option to create the login with the same sid as on the primary server.
-- get sid from primary server
select sid from sys.server_principals
where name = 'Sample'
-- Create new login
CREATE LOGIN [sample] WITH SID= 'sid guid', PASSWORD=N'sample', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
[font="Verdana"]Markus Bohse[/font]
September 12, 2011 at 6:17 am
Hi MarkusB, thank you very much.
Ryan
//All our dreams can come true, if we have the courage to pursue them//
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply