SQL Server - Query help please.

Asked By LamNguye

02-Jun-08 05:40 PM

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'

04-Jun-08 09:19 PM

Have you tried:

SELECT
'EXECute sp_addrolemember ''' + Role_Name + ''', ''' + members +
''' '
FROM
WHERE
UserID IS NOT NULL

...is that all you need?

Hope that helps!

02-Jun-08 08:36 PM

Change your data as the following and your query will work:
INSERT INTO #LoginName (RoleName, members, UserId) VALUES('db_owner',
'MyDomain\AdminPerson ', 1) INSERT
INTO #LoginName (RoleName, members, UserId) VALUES('db_owner', 'SQLAdmin ', 2)

--INSERT INTO #LoginName VALUES('db_datareader', ' ', NULL)
INSERT INTO #LoginName (RoleName, members, UserId) VALUES('db_datareader',
'SQLBTS', 3)
INSERT INTO #LoginName (RoleName, members, UserId) VALUES('db_datareader',
'MyDomain\edituser', 4)
INSERT INTO #LoginName (RoleName, members, UserId) VALUES('db_datareader',
'MyDomain\Lisa', 5)
INSERT INTO #LoginName (RoleName, members, UserId) VALUES('db_datareader',
'AppUser1', 9)
INSERT INTO #LoginName (RoleName, members, UserId) VALUES('db_datareader',
'MyDomain\John', 7)
INSERT INTO #LoginName (RoleName, members, UserId) VALUES('db_datareader',
'MyDomain\Peter', 8)

-----------

--INSERT INTO #LoginName VALUES('db_datawriter', ' ', NULL)
INSERT INTO #LoginName (RoleName, members, UserId) VALUES('db_datawriter',
'MyDomain\Lisa', 5)
INSERT INTO #LoginName (RoleName, members, UserId) VALUES('db_datawriter',
'MyDomain\edituser', 4)
INSERT INTO #LoginName (RoleName, members, UserId) VALUES('db_datawriter',
'SQLBTS', 3)
INSERT INTO #LoginName (RoleName, members, UserId) VALUES('db_datawriter',
'MyDomain\John', 7)
INSERT INTO #LoginName (RoleName, members, UserId) VALUES('db_datawriter',
'MyDomain\Peter', 8)
INSERT INTO #LoginName (RoleName, members, UserId) VALUES('db_datawriter',
'AppUser1', 9)

Q

02-Jun-08 08:42 PM

Eric,

I try to execute this query and it did not work.

EXECute sp_addrolemember '', 'MyDomain\AdminPerson '
EXECute sp_addrolemember '', 'SQLAdmin '
EXECute sp_addrolemember '', 'SQLBTS'
EXECute sp_addrolemember '', 'MyDomain\edituser'
EXECute sp_addrolemember '', 'MyDomain\Lisa'
EXECute sp_addrolemember '', 'AppUser1'
EXECute sp_addrolemember '', 'MyDomain\John'
EXECute sp_addrolemember '', 'MyDomain\Peter'
EXECute sp_addrolemember '', 'MyDomain\Lisa'
EXECute sp_addrolemember '', 'MyDomain\edituser'
EXECute sp_addrolemember '', 'SQLBTS'
EXECute sp_addrolemember '', 'MyDomain\John'
EXECute sp_addrolemember '', 'MyDomain\Peter'
EXECute sp_addrolemember '', 'AppUser1'

04-Jun-08 09:19 PM

Yes, I assumed your role_name was populated in #LoginName.  If you
follow Q's advice below on how to populate it, it should work.

02-Jun-08 11:55 PM

I am try to run this queries below populate data into temp table and how can
I run an update correspond to the right group.

SELECT CASE
WHEN (GROUPING(groupuid) = 1 AND GROUPING(memberuid) = 1 )
THEN ' '
WHEN (GROUPING(groupuid) = 0 AND GROUPING(memberuid) = 1 )
THEN CAST(user_name(groupuid) AS VARCHAR(25))
WHEN (GROUPING (groupuid) = 0 AND GROUPING(memberuid) = 0)
THEN ' '
END AS 'Role',
CASE
WHEN (GROUPING (groupuid) = 1 AND GROUPING (memberuid) = 1)
THEN ' '
WHEN (GROUPING (groupuid) = 0 AND GROUPING (memberuid) = 1)
THEN ' '
WHEN (GROUPING (groupuid) = 0 AND grouping(memberuid) = 0)
THEN CAST(user_name(memberuid) AS NVARCHAR(35))
END AS 'members',
CASE
WHEN (GROUPING (groupuid) = 1 AND GROUPING (memberuid) = 1)
THEN NULL
WHEN (GROUPING (groupuid) = 0 AND GROUPING (memberuid) = 1)
THEN NULL
WHEN (GROUPING (groupuid) = 0 AND GROUPING (memberuid) = 0)
THEN (SELECT sid FROM sysusers WHERE uid = memberuid)
END AS 'sid'
FROM sysmembers
GROUP BY groupuid, memberuid
WITH ROLLUP
ORDER BY groupuid ASC, memberuid ASC
go

/*
Role                      members
------------------------- -----------------------------------

db_owner
dbo
db_datareader
Mydomain\Finance
Mydomain\Cristine
HOME\Peter
John
db_datawriter
Mydomain\Cristine
HOME\Peter
John
Mydomain\Finance

*/