June 15, 2011 at 6:12 am
Has anyone ever experienced that user mappings are removed automatically somehow.. I reasently ran this script on a new server.
CREATE USER [xxx1] FOR LOGIN [xxx1]
GO
USE [xxxx]
GO
ALTER USER [xxx1] WITH DEFAULT_SCHEMA=[dbo]
GO
USE [xxxx]
GO
EXEC sp_addrolemember N'db_datareader', N'xxx1'
GO
USE [xxxx]
GO
EXEC sp_addrolemember N'db_datawriter', N'xxx1'
GO
June 15, 2011 at 6:24 am
Have you restored the database where the mapping is missing from another environment? If yes you need to fix the users.
EXEC sp_change_users_login 'update_one', 'username', 'username'
June 15, 2011 at 6:49 am
the database has been restored.. But theese users are new logins, that did not exist on the old server.
June 15, 2011 at 6:52 am
if looking at these through SSMS have you refreshed?
run sp_helpuser in the database to check current situation
---------------------------------------------------------------------
June 15, 2011 at 6:57 am
If you put a USE xxxx statement at the very beginning of your script, you'll find that this doesn't happen.
John
June 15, 2011 at 7:04 am
Thanks for all replies... I audited the database, and found out that someone was running a script that removed all user mappings for unknown users.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply