February 10, 2014 at 8:14 am
Hi,
I'm restoring to a remote DB for our DR testing.
After the restore I need to create come user mappings to certain DB's. For example we have a DB called HRMS_SITE. I need for the user [WSIDR\CARSHO] to be mapped to DB HRMS_SITE as a 'db_owner'.
So I have this script - but it doesn't work. It runs successfully, but when I check [WSIDR\CARSHO] the user is not mapped to that DB.
USE HRMS_SITE
EXEC sp_addrolemember N'db_owner', N'WSIDR\CARSHO'
GO
February 10, 2014 at 2:01 pm
krypto69 (2/10/2014)
Hi,I'm restoring to a remote DB for our DR testing.
After the restore I need to create come user mappings to certain DB's. For example we have a DB called HRMS_SITE. I need for the user [WSIDR\CARSHO] to be mapped to DB HRMS_SITE as a 'db_owner'.
So I have this script - but it doesn't work. It runs successfully, but when I check [WSIDR\CARSHO] the user is not mapped to that DB.
USE HRMS_SITEEXEC sp_addrolemember N'db_owner', N'WSIDR\CARSHO'
GO
Did you create the user in the database before adding in the role membership?
USE HRMS_SITE
GO
CREATE USER [WSIDR\CARSHO] FOR LOGIN [WSIDR\CARSHO]
GO
USE HRMS_SITE
GO
EXEC sp_addrolemember N'db_owner', N'WSIDR\CARSHO'
GO
Remember that a database LOGIN for the server is not the same thing as the database user for the database. Your reported behavior exactly matches trying to add a login to a role where no database user exists.
Thanks
John.
February 10, 2014 at 2:19 pm
Does it succeed if you add this user to the 'db_owner' role using SSMS? First try scripting out your actions in the GUI and run the output. If that doesn't succeed perform all actions through SSMS.
See if this will get the desired results or it could give you some errors.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply