September 16, 2009 at 6:58 am
I am using SQL Server 2005 and i have created a login for a user (windows authentication login) and have mapped a database to it and given him read and write rights. everything works fine every day but every morning when he logs back in the login is there but the particular database is not mapped in, so i have to every morning map the particular login and give him read and write rights. so idea why does this happen? require assistance asap.
Thanks
September 16, 2009 at 7:00 am
September 16, 2009 at 7:01 am
Do you have a nightly restore of this particular database from another server?
If yes, you have to add this login to there.
September 16, 2009 at 7:02 am
Vishal Singh (9/16/2009)
Check what all jobs are running during night. Anything, related to Audit or users.
Or incase you have anightly restore, you can use the below script for an automated mapping:
DECLARE @command Varchar(1000)
SET @command =
'EXEC sp_change_users_login ''Report''
if object_id(''tempdb.dbo.#tempUsers'') is not null drop table #tempUsers
create table #tempUsers
(
UserNamenvarchar(50),
UserSIDvarbinary(85)
)
insert into #tempUsers
select name, sid from sysusers where
exists
(select 1 from master.dbo.syslogins m
where m.name = sysusers.name and sysusers.sid m.sid)
declare ucur cursor for select Username from #tempUsers where Username suser_sname()
declare @uname sysname
open ucur
fetch next from ucur into @uname
while @@FETCH_STATUS = 0
BEGIN
exec sp_change_users_login ''UPDATE_ONE'', @uname, @uname
fetch next from ucur into @uname
END
close ucur
deallocate ucur
drop table #tempUsers
if object_id(''tempdb.dbo.#tempUsers'') is not null drop table #tempUsers
EXEC sp_change_users_login ''Report'''
Exec SP_msforeachdb @command
September 16, 2009 at 7:08 am
yes i have a nightly restore of thidatabase, but what exact steps should i follow to add this login to this database. i need some exact steps since i am a newbie.
Thanks a lot.
September 16, 2009 at 7:21 am
So in the last step of the job that I have created has the below steps that will create user.
USE [IBM_PTC]
GO
CREATE USER [IBM\1001dpatric] FOR LOGIN [IBM\1001dpatric]
GO
USE [IBM_PTC]
GO
EXEC sp_addrolemember N'db_datareader', N'IBM\1001dpatric'
GO
USE [IBM_PTC]
GO
EXEC sp_addrolemember N'db_datawriter', N'IBM\1001dpatric'
GO
USE [IBM_PTC]
GO
EXEC sp_addrolemember N'db_ddladmin', N'IBM\1001dpatric'
GO
CREATE USER [IBM\1001csmith] FOR LOGIN [IBM\1001csmith]
GO
USE [IBM_PTC]
GO
EXEC sp_addrolemember N'db_datareader', N'IBM\1001csmith'
GO
USE [IBM_PTC]
GO
EXEC sp_addrolemember N'db_datawriter', N'IBM\1001csmith'
GO
USE [IBM_PTC]
GO
EXEC sp_addrolemember N'db_ddladmin', N'IBM\1001csmith'
GO
Now the new user that I created is not in the above statements, so can I create the step below and add in the above lines and that would work.
CREATE USER [IBM\1001dconroy] FOR LOGIN [IBM\1001dconroy]
GO
USE [IBM_PTC]
GO
EXEC sp_addrolemember N'db_datareader', N'IBM\1001dconroy'
GO
USE [IBM_PTC]
GO
EXEC sp_addrolemember N'db_datawriter', N'IBM\1001dconroy'
GO
USE [IBM_PTC]
GO
EXEC sp_addrolemember N'db_ddladmin', N'IBM\1001dconroy'
GO
And after adding the above statements my whole job would look like this :-
USE [IBM_PTC]
GO
CREATE USER [IBM\1001dpatric] FOR LOGIN [IBM\1001dpatric]
GO
USE [IBM_PTC]
GO
EXEC sp_addrolemember N'db_datareader', N'IBM\1001dpatric'
GO
USE [IBM_PTC]
GO
EXEC sp_addrolemember N'db_datawriter', N'IBM\1001dpatric'
GO
USE [IBM_PTC]
GO
EXEC sp_addrolemember N'db_ddladmin', N'IBM\1001dpatric'
GO
CREATE USER [IBM\1001csmith] FOR LOGIN [IBM\1001csmith]
GO
USE [IBM_PTC]
GO
EXEC sp_addrolemember N'db_datareader', N'IBM\1001csmith'
GO
USE [IBM_PTC]
GO
EXEC sp_addrolemember N'db_datawriter', N'IBM\1001csmith'
GO
USE [IBM_PTC]
GO
EXEC sp_addrolemember N'db_ddladmin', N'IBM\1001csmith'
GO
CREATE USER [IBM\1001dconroy] FOR LOGIN [IBM\1001dconroy]
GO
USE [IBM_PTC]
GO
EXEC sp_addrolemember N'db_datareader', N'IBM\1001dconroy'
GO
USE [IBM_PTC]
GO
EXEC sp_addrolemember N'db_datawriter', N'IBM\1001dconroy'
GO
USE [IBM_PTC]
GO
EXEC sp_addrolemember N'db_ddladmin', N'IBM\1001dconroy'
GO
September 16, 2009 at 7:36 am
can someone help me please??
September 16, 2009 at 12:09 pm
You don't need a separate job for logins mapping.
I believe you have a scheduled job for you nightly restore.
So, just add one more last step to this job to map users.
You may want to put into this step a script that was provided to you above.
Or you can hardcode your users, something like that with you specifics of course:
USE [yourDBname]
GO
CREATE USER [Domain\yourUser1] FOR LOGIN [Domain\yourUser1] WITH DEFAULT_SCHEMA=[dbo]
GO
....................................
CREATE USER [Domain\yourUser2] FOR LOGIN [Domain\yourUser2] WITH DEFAULT_SCHEMA=[dbo]
GO
September 16, 2009 at 10:37 pm
Sergey Vavinskiy (9/16/2009)
You don't need a separate job for logins mapping.I believe you have a scheduled job for you nightly restore.
So, just add one more last step to this job to map users.
You may want to put into this step a script that was provided to you above.
Or you can hardcode your users, something like that with you specifics of course:
USE [yourDBname]
GO
CREATE USER [Domain\yourUser1] FOR LOGIN [Domain\yourUser1] WITH DEFAULT_SCHEMA=[dbo]
GO
....................................
CREATE USER [Domain\yourUser2] FOR LOGIN [Domain\yourUser2] WITH DEFAULT_SCHEMA=[dbo]
GO
I suspect this as as creating a user after a DB restore will always successful or not. This can Probably cause you an error and thats why the approach should not be to create them but to map them.
I strongly recommend to map the users since you already have the login created.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply