SQL Server Auto_fix_login_users

When the database is relocated, too many login and user needs to match, too cumbersome, deliberately write this script, each move the database just run down the script to match login and user.

Use [msdb]
GO

/****** object:storedprocedure [dbo].    [Usp_auto_fix_login_users] Script date:6/14/2013 2:06:32 PM ******/
SET ANSI_NULLS on
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
--Author: <SDS>
--Create DATE:2013/06/14
--Description: matching Login and Users
-- =============================================
CREATE PROCEDURE [dbo]. [Usp_auto_fix_login_users]
As
BEGIN
SET NOCOUNT on;

IF EXISTS (SELECT * from SYSOBJECTS WHERE ID = object_id (' dbo.databases ') and XTYPE = ' U ')
DROP TABLE dbo.databases

IF EXISTS (SELECT * from SYSOBJECTS WHERE ID = object_id (' Dbo.login ') and XTYPE = ' U ')
DROP TABLE Dbo.login

IF EXISTS (SELECT * from SYSOBJECTS WHERE ID = object_id (' dbo.users ') and XTYPE = ' U ')
DROP TABLE Dbo.users

IF EXISTS (SELECT * from SYSOBJECTS WHERE ID = object_id (' dbo. Result ') and XTYPE = ' U ')
DROP TABLE dbo. Result

CREATE TABLE [dbo]. [Databases] ([number] [int] null,[name] [nvarchar] () NULL) On [PRIMARY]
CREATE TABLE [dbo]. [Login] ([number] [int] null,[name] [nvarchar] () NULL) On [PRIMARY]
CREATE TABLE [dbo]. [Users] ([number] [int] null,[name] [nvarchar] () NULL) On [PRIMARY]
CREATE TABLE [dbo]. [Result] ([Database][nvarchar] (null,[login] [nvarchar] () NULL) On [PRIMARY]

DECLARE @l int
DECLARE @d int
DECLARE @lnumber int
DECLARE @dnumber int
Declare @login as nvarchar (50)
Declare @databasename as nvarchar (50)
Declare @strsql1 as nvarchar (max)
Declare @strsql2 as nvarchar (max)
Declare @strsql3 as nvarchar (max)
Declare @strsql as nvarchar (max)
DECLARE @ROWCOUNT as int

SET @l=1
SET @d=1
SET @strsql = ' '

DELETE from Dbo.databases
INSERT into dbo.databases
SELECT row_number () over (ORDER by name) as Number,name
From sys.databases
WHERE state_desc= ' ONLINE ' and DATABASEPROPERTYEX (name, ' updateability ') <> ' read_only '

DELETE from Dbo.login
INSERT into Dbo.login
SELECT row_number () over (ORDER by name) as Number,name
From Sys.syslogins
where Len (name) <=10

SELECT @dnumber =max (number) from dbo.databases
SELECT @lnumber =max (number) from Dbo.login

while (@d<[email protected])
BEGIN
SELECT @databasename =name from Dbo.databases WHERE[email protected]
SET @strsql1 = ' use '[email protected]+ '
while (@l<[email protected])
BEGIN
SELECT @login = name from Dbo.login WHERE[email protected]
EXEC (' DELETE from Dbo.users INSERT to dbo.users SELECT ' 1 ' as number,name from '[email protected]+ '. Dbo.sysusers where islogin= ' 1 ' and name= '[email protected]+ "")
SELECT @ROWCOUNT =count (*) from Dbo.users
IF @ROWCOUNT >0
BEGIN
INSERT into DBO. Result
SELECT @databasename as [Database], @login as [login]
SET @strsql2 = @strsql1 + ' EXEC sp_change_users_login ' auto_fix ', ' [email protected]+ ' '
SET @strsql = @strsql + @strsql2
PRINT (@strsql)
EXEC (@strsql)
END
SET @[email protected]+1
END
SET @[email protected]+1
SET @l=1
END

SELECT * FROM dbo. Result
DROP TABLE dbo.databases
DROP TABLE Dbo.login
DROP TABLE Dbo.users
DROP TABLE dbo. Result

END

GO

SQL Server Auto_fix_login_users