Hi,
I try to run the query below to add the users into the correct role as
before after restore db from production. I have hundred of databases need to
restore and the trick is to keep the their loginid and their access
permissions are the same as orginal. Your help is greatly appreciate.
IF OBJECT_ID('Tempdb.dbo.#LoginName', 'u') IS NOT NULL
DROP TABLE #LoginName
GO
CREATE TABLE #LoginName
(
RoleName VARCHAR(25) NULL,
members VARCHAR(35) NULL,
UserId INT NULL
)
GO
-- DELETE #LoginName
INSERT INTO #LoginName VALUES('db_owner', ' ', NULL) INSERT INTO #LoginName
(RoleName, members, UserId) VALUES('', 'MyDomain\AdminPerson ', 1) INSERT
INTO #LoginName (RoleName, members, UserId) VALUES('', 'SQLAdmin ', 2)
INSERT INTO #LoginName VALUES('db_datareader', ' ', NULL) INSERT INTO
INSERT INTO #LoginName (RoleName, members, UserId) VALUES('',
'MyDomain\Lisa', 5) INSERT INTO #LoginName (RoleName, members, UserId)
VALUES('', 'AppUser1', 9) INSERT INTO #LoginName (RoleName, members, UserId)
VALUES('', 'MyDomain\John', 7) INSERT INTO #LoginName (RoleName, members,
UserId) VALUES('', 'MyDomain\Peter', 8)
-----------
INSERT INTO #LoginName VALUES('db_datawriter', ' ', NULL) INSERT INTO
INTO #LoginName (RoleName, members, UserId) VALUES('', 'MyDomain\edituser', 4)
INSERT INTO #LoginName (RoleName, members, UserId) VALUES('', 'SQLBTS', 3)
INSERT INTO #LoginName (RoleName, members, UserId) VALUES('',
'MyDomain\John', 7) INSERT INTO #LoginName (RoleName, members, UserId)
VALUES('', 'MyDomain\Peter', 8) INSERT INTO #LoginName (RoleName, members,
UserId) VALUES('', 'AppUser1', 9) GO
SELECT *
FROM #LoginName
GO
RoleName members UserId
------------------------- ----------------------------------- -----------
db_owner NULL
MyDomain\AdminPerson 1
SQLAdmin 2
db_datareader NULL
SQLBTS 3
MyDomain\edituser 4
MyDomain\Lisa 5
AppUser1 9
MyDomain\John 7
MyDomain\Peter 8
db_datawriter NULL
MyDomain\Lisa 5
MyDomain\edituser 4
SQLBTS 3
MyDomain\John 7
MyDomain\Peter 8
AppUser1 9
-- How can I get the result want showing below.
-- Testing...
SELECT 'EXECute sp_addrolemember ''' + RoleName + ''', ''' + members
+ ''''
FROM #LoginName
ORDER BY 1 ASC
GO
-- Result want:
EXECute sp_addrolemember 'db_datareader', 'AppUser1'
EXECute sp_addrolemember 'db_datawriter', 'AppUser1'
EXECute sp_addrolemember 'db_owner', 'MyDomain\AdminPerson'
EXECute sp_addrolemember 'db_datareader', 'MyDomain\edituser'
EXECute sp_addrolemember 'db_datawriter', 'MyDomain\edituser'
EXECute sp_addrolemember 'db_datareader', 'MyDomain\John'
EXECute sp_addrolemember 'db_datawriter', 'MyDomain\John'
EXECute sp_addrolemember 'db_datareader', 'MyDomain\Lisa'
EXECute sp_addrolemember 'db_datawriter', 'MyDomain\Lisa'
EXECute sp_addrolemember 'db_datareader', 'MyDomain\Peter'
EXECute sp_addrolemember 'db_datawriter', 'MyDomain\Peter'
EXECute sp_addrolemember 'db_owner', 'SQLAdmin'
EXECute sp_addrolemember 'db_datareader', 'SQLBTS'
EXECute sp_addrolemember 'db_datawriter', 'SQLBTS' |