Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
g.c.benjamin
Starting Member
10 Posts |
Posted - 2012-11-14 : 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 areROLES which contains a WebsiteId and RoleId and AutoAssign.USERROLES which contains a UserId and a RoleIdUSERWEBSITE which contains a WebsiteId and a UserIdSo 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
1974 Posts |
Posted - 2012-11-15 : 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 rJOIN #UserWebsite uw ON r.WebsiteID = uw.WebsiteIDLEFT JOIN #UserRoles ur ON ur.RoleID = r.RoleID AND ur.UserID = uw.UserIDWHERE r.AutoAssign = 1 AND ur.RoleID IS NULLINSERT INTO #UserRolesSELECT uw.UserID, r.RoleID FROM #Roles rJOIN #UserWebsite uw ON r.WebsiteID = uw.WebsiteIDLEFT JOIN #UserRoles ur ON ur.RoleID = r.RoleID AND ur.UserID = uw.UserIDWHERE r.AutoAssign = 1 AND ur.RoleID IS NULL--Select Missing Roles (Expecting 0 Rows):SELECT uw.UserID, r.RoleID FROM #Roles rJOIN #UserWebsite uw ON r.WebsiteID = uw.WebsiteIDLEFT JOIN #UserRoles ur ON ur.RoleID = r.RoleID AND ur.UserID = uw.UserIDWHERE r.AutoAssign = 1 AND ur.RoleID IS NULLDROP TABLE #RolesDROP TABLE #UserRolesDROP TABLE #UserWebsiteGO -Chad |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-11-15 : 10:30:39
|
[code]INSERT INTO UserRolesSELECT uw.UserId,r.RoleIdFROM USERWEBSITE uwINNER JOIN ROLES rON r.WebsiteId = uw.WebsiteIdWHERE NOT EXISTS (SELECT 1FROM USERROLESWHERE UserId = uw.UserIdAND RoleId = r.RoleId)[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2012-11-15 : 10:39:08
|
quote: Originally posted by visakh16
INSERT INTO UserRolesSELECT uw.UserId,r.RoleIdFROM USERWEBSITE uwINNER JOIN ROLES rON r.WebsiteId = uw.WebsiteIdWHERE NOT EXISTS (SELECT 1FROM USERROLESWHERE UserId = uw.UserIdAND RoleId = r.RoleId) ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://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
10 Posts |
Posted - 2012-11-15 : 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
10 Posts |
Posted - 2012-11-15 : 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
52326 Posts |
Posted - 2012-11-15 : 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 MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|