恢复数据库后,恢复原来数据用户的权限
引自http://www.cnblogs.com/coolstr/archive/2007/04/19/719799.html
新建一个存储过程
执行这个存储过程
执行完后,就可以访问了。
或者也可以直接这样做:EXEC sp_change_users_login 'Auto_Fix','xxxxxx',null,'yyyyyy';
其中:'xxxxxx' 为Login
'yyyyy'为其Password
新建一个存储过程
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [sz_dvp_restore_login_user]
--INPUT
@DBName nvarchar(50),
@UserName nvarchar(50)
AS
Exec sp_configure 'allow updates',1
RECONFIGURE WITH OVERRIDE
Declare @ExecStr nvarchar(4000)
Select @ExecStr ='Declare @b varbinary(85) ' +
'Use Master '
+ 'Select @b = sid From syslogins Where Name =''' + @UserName + ''''
+ ' Use ' + @DBName
+ ' Update sysusers Set sid = @b Where name =''' + @UserName + ''''
--Print @ExecStr
Exec(@ExecStr)
Exec sp_configure 'allow updates',0
RECONFIGURE WITH OVERRIDE
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [sz_dvp_restore_login_user]
--INPUT
@DBName nvarchar(50),
@UserName nvarchar(50)
AS
Exec sp_configure 'allow updates',1
RECONFIGURE WITH OVERRIDE
Declare @ExecStr nvarchar(4000)
Select @ExecStr ='Declare @b varbinary(85) ' +
'Use Master '
+ 'Select @b = sid From syslogins Where Name =''' + @UserName + ''''
+ ' Use ' + @DBName
+ ' Update sysusers Set sid = @b Where name =''' + @UserName + ''''
--Print @ExecStr
Exec(@ExecStr)
Exec sp_configure 'allow updates',0
RECONFIGURE WITH OVERRIDE
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
执行这个存储过程
sz_dvp_restore_login_user 'dbName','username'
执行完后,就可以访问了。
或者也可以直接这样做:EXEC sp_change_users_login 'Auto_Fix','xxxxxx',null,'yyyyyy';
其中:'xxxxxx' 为Login
'yyyyy'为其Password