Monday, August 5, 2013

ASP.NET MVC 4 SimpleMembershipProvider, Web Security Roles Add User To Role SQL, Remove User From Role SQL

Using SQL Profiler, below is the SQL that is executed when the ASP.NET MVC 4 SimpleMembershipProvider's System.Web.Security.Roles.AddUserToRole() method and RemoveUserFromRole() method are called:

Text below in bold is dynamic and represents the UserName and RoleName property passed into the AddUserToRole()  method (or the UserId, RoleId associated with the UserName, RoleName).


Roles.AddUserToRole("UserName", "RoleName");


exec sp_executesql N'SELECT [UserId] FROM [UserProfile] WHERE (UPPER([UserName]) = @0)',N'@0 nvarchar(25)',@0=N'USERNAME'


exec sp_executesql N'SELECT RoleId FROM webpages_Roles WHERE (RoleName = @0)',N'@0 nvarchar(5)',@0=N'RoleName'


exec sp_executesql N'SELECT COUNT(*) FROM [UserProfile] u, webpages_UsersInRoles ur, webpages_Roles r Where (u.[UserName] = @0 and r.RoleName = @1 and ur.RoleId = r.RoleId and ur.UserId = u.[UserId])',N'@0 nvarchar(8),@1 nvarchar(8)',@0=N'UserName',@1=N'RoleName'


INSERT INTO webpages_UsersInRoles VALUES (1,1)


Roles.RemoveUserFromRole("UserName", "RoleName");


exec sp_executesql N'SELECT RoleId FROM webpages_Roles WHERE (RoleName = @0)',N'@0 nvarchar(8)',@0=N'RoleName'


exec sp_executesql N'SELECT COUNT(*) FROM [UserProfile] u, webpages_UsersInRoles ur, webpages_Roles r Where (u.[UserName] = @0 and r.RoleName = @1 and ur.RoleId = r.RoleId and ur.UserId = u.[UserId])',N'@0 nvarchar(8),@1 nvarchar(8)',@0=N'UserName',@1=N'RoleName'


exec sp_executesql N'SELECT [UserId] FROM [UserProfile] WHERE (UPPER([UserName]) = @0)',N'@0 nvarchar(25)',@0=N'USERNAME'


exec sp_executesql N'SELECT RoleId FROM webpages_Roles WHERE (RoleName = @0)',N'@0 nvarchar(8)',@0=N'RoleName'


DELETE FROM webpages_UsersInRoles WHERE UserId = 1 and RoleId = 1


Post a Comment