| Author |
 Topic  |
|
|
g.c.benjamin
Starting Member
3 Posts |
 Posted - 11/14/2012 : 23:53:24
|
Through a bug I created, users on my site weren't being automatically added to the sites default roles. I fixed the problem but now there are users who don't belong to all the roles they should. I want to right a script that will find which users this affected and add them to the right roles.
The 3 tables I think I need to use are
ROLES which contains a WebsiteId and RoleId and AutoAssign.
USERROLES which contains a UserId and a RoleId
USERWEBSITE which contains a WebsiteId and a UserId
So what I'm trying to do but failing at miserably, is to add a record to the USERROLES table where the user belongs to website but the role that should have been auto assigned wasn't. |
|
|
chadmat
The Chadinator
USA
1958 Posts |
Posted - 11/15/2012 : 02:16:29
|
I'll take a stab:
CREATE TABLE #Roles (WebsiteID int, RoleID int, AutoAssign bit)
CREATE TABLE #UserRoles (UserID int, RoleID int)
CREATE TABLE #UserWebsite(WebsiteID int, UserID int)
INSERT INTO #Roles Values (1, 1, 1)
INSERT INTO #Roles Values (1, 2, 0)
INSERT INTO #Roles Values (2, 3, 1)
INSERT INTO #Roles Values (2, 4, 1)
INSERT INTO #Roles Values (2, 5, 0)
INSERT INTO #UserWebsite Values(1, 1)
INSERT INTO #UserWebsite Values(2, 1)
INSERT INTO #UserWebsite Values(1, 2)
INSERT INTO #UserWebsite Values(1, 3)
INSERT INTO #UserRoles Values(1, 1)
INSERT INTO #UserRoles Values(1, 3)
INSERT INTO #UserRoles Values(1, 4)
--Commenting this one out because I want it to be missing
--INSERT INTO #UserRoles Values(2, 1)
INSERT INTO #UserRoles Values(3, 1)
INSERT INTO #UserRoles Values(3, 2)
--Select Missing Roles (Expecting 1 row):
SELECT uw.UserID, r.RoleID FROM #Roles r
JOIN #UserWebsite uw ON r.WebsiteID = uw.WebsiteID
LEFT JOIN #UserRoles ur ON ur.RoleID = r.RoleID AND ur.UserID = uw.UserID
WHERE r.AutoAssign = 1
AND ur.RoleID IS NULL
INSERT INTO #UserRoles
SELECT uw.UserID, r.RoleID FROM #Roles r
JOIN #UserWebsite uw ON r.WebsiteID = uw.WebsiteID
LEFT JOIN #UserRoles ur ON ur.RoleID = r.RoleID AND ur.UserID = uw.UserID
WHERE r.AutoAssign = 1
AND ur.RoleID IS NULL
--Select Missing Roles (Expecting 0 Rows):
SELECT uw.UserID, r.RoleID FROM #Roles r
JOIN #UserWebsite uw ON r.WebsiteID = uw.WebsiteID
LEFT JOIN #UserRoles ur ON ur.RoleID = r.RoleID AND ur.UserID = uw.UserID
WHERE r.AutoAssign = 1
AND ur.RoleID IS NULL
DROP TABLE #Roles
DROP TABLE #UserRoles
DROP TABLE #UserWebsite
GO
-Chad |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47146 Posts |
Posted - 11/15/2012 : 10:30:39
|
INSERT INTO UserRoles
SELECT uw.UserId,r.RoleId
FROM USERWEBSITE uw
INNER JOIN ROLES r
ON r.WebsiteId = uw.WebsiteId
WHERE NOT EXISTS (SELECT 1
FROM USERROLES
WHERE UserId = uw.UserId
AND RoleId = r.RoleId)
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
chadmat
The Chadinator
USA
1958 Posts |
Posted - 11/15/2012 : 10:39:08
|
quote:
Originally posted by visakh16
INSERT INTO UserRoles
SELECT uw.UserId,r.RoleId
FROM USERWEBSITE uw
INNER JOIN ROLES r
ON r.WebsiteId = uw.WebsiteId
WHERE NOT EXISTS (SELECT 1
FROM USERROLES
WHERE UserId = uw.UserId
AND RoleId = r.RoleId)
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
I tried that first, but it wasn't working for some reason (Maybe I typed something wrong), so I went with the outer join, which works.
-Chad |
|
|
|
g.c.benjamin
Starting Member
3 Posts |
Posted - 11/15/2012 : 20:00:24
|
Thanks, much better than my solution! One thing though, running the following works when inserting into the UserRoles table:
INSERT INTO UserRoles (UserID, RoleID)
VALUES (@UserID, @RoleID)
But running your solution I get this error:
Column name or number of supplied values does not match table definition.
There are other columns in the UserRoles table, but these are all nullable. Do I have to specify the nullable values in the query? |
|
|
|
g.c.benjamin
Starting Member
3 Posts |
Posted - 11/15/2012 : 20:37:35
|
| No need to answer last question, I just supplied the null columns in the query and worked. Thanks! |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47146 Posts |
Posted - 11/15/2012 : 22:14:33
|
quote:
Originally posted by g.c.benjamin
No need to answer last question, I just supplied the null columns in the query and worked. Thanks!
it shouldnt be a problem so far as you provide column list with insert
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
|
|
Fill in missing records
