It sounds like there might be a disconnect between the Login, at the SQL Server level, and the User, at the database level. Here is a template of a SQL script that we use to sync things back up after restoring a database:[code]-----------------------------------------------------------------------------
--- Generate and Execute SQL Statements
--- Description: Run from the current database, this script looks for Logins
--- and Users with the same name but different SID's. It generates, and
--- optionally runs, the code to bring the two entities into sync.
--- NOTE: Since it only runs on exceptions, this script can be run multiple times.
set nocount on;
declare
@PKey int = 1,
@MaxPKey int,
@sql nvarchar(max),
@error_number int,
@error_line int,
@error_message nvarchar(2048),
@Debug smallint = 1, --<<< SET TIHS VALUE !!!
-- 0 = Ignore
-- 1 = Print major events
-- 2 = Select major and minor events
@Execute bit = 1, --<<< SET TIHS VALUE !!!
-- 0 = Do NOT Execute
-- 1 = Execute SQL Generated
@UnionAll bit = 0 --<< SET THIS VALUE !!!
-- Used when the script generated are a series of SELECTs and you want one resultset
declare
@Template nvarchar(1000) = N'
use [<>]; --<>
ALTER USER [<>]
WITH
<<DEFAULT_SCHEMA>>
LOGIN = [<>];';
declare @tbl table (
pkey int not null identity(1, 1),
sql nvarchar(max) not null
)
--- Populate table
-- Application specific logic: Start
insert into @tbl (
sql
)
select --* ,
replace(
replace(
replace(
replace(
@Template,
'<>',
dp.Name
),
'<>',
db_name()
),
'<>',
@@servername
),
'<<DEFAULT_SCHEMA>>',
case
when dp.type in ('G', 'C', 'K') then ''
else 'DEFAULT_SCHEMA = dbo,'
end
) sqlStmt
--select *
from
sys.database_principals dp
inner join
sys.server_principals sp
on dp.name = sp.name
where
1=1
and dp.type not in ('A', 'R') -- A = Application Role, R = Database Role
and dp.sid <> sp.sid
--and dp.default_schema_name <> 'dbo'
--and s.name like 'HMS\e00%'
and dp.name not in (
'guest',
'public',
''
)
order by
dp.name;
-- Application specific logic: End
set @MaxPKey = @@RowCount
--/**/select * from @tbl
--- Loop through table entries: Calculate table row count
begin try
while (@PKey <= @MaxPKey)
begin
---------------------------------------
--- Get single table entry
---------------------------------------
select
@sql = sql
from
@tbl
where
PKey = @PKey
---------------------------------------
--- Save SQL statement
---------------------------------------
if @Debug & 1 > 0
print @sql
if @Debug & 2 > 0
select @sql
if @Execute = 1
exec sp_ExecuteSql @sql
---------------------------------------
--- End-of-loop processing
---------------------------------------
set @PKey += 1;
end
print '--Complete!'
end try
begin catch
select
@error_number = error_number(),
@error_line = error_line(),
@error_message = error_message()
select
@error_number ErrorNumber,
@error_line ErrorLine,
@error_message ErrorMessage
raiserror('ERROR: %s (%li) at line %lu', 16, 0, @error_message, @error_number, @error_line) with log;
end catch[/code]You may need to modify this a bit if your login name doesn't map to the user name.
The other issue might be that the Login doesn't exist for the User in the database that got restored. You would need to create the Login first, then run this script.
NOTE: If you set the @Execute = 0, the code gets generated but not run. You can then review and modify it before turning it loose.
HTH