A sql script for creating a LightSwitch user in the sql database.

Introduction

During  deployment, you have the option to create an application administrator. Very convenient!

Nonetheless, there can be situations where you want to handle this yourself via a script.

What the script is NOT doing, is creating an entry in the RolePermission table, which maps the application roles to permissions. For a typical security administrator role, this boils down to adding an entry for the role Admin and PermissionId = “Microsoft.LightSwitch.Security:SecurityAdministration”. Obviously for more application specific roles, this can be whole set of permissions.

How?

LightSwitch uses the well-known aspnet membership provider infrastructure, which is based on the lower level on a set of powerful sql server stored procedures.  We’ll simply call these stored procedures in our script. The main advantage of this approach is that this makes the script “idempotent”, in other words: you may run the script several times without getting an error. E.g., imagine you have already a role “admin” in your database and you are running the script for creating a new admin user. Well, … the script will not fail because the admin role is already there.

One security remark, only important when using forms authentication, I’m storing the password in plain text. (the password format = 0, meaning unencrypted).

The Script

DECLARE        @ApplicationNameParam nvarchar(256) 
DECLARE        @return_value int 
DECLARE                        @UserNameParam nvarchar(256) 
DECLARE                        @UserFullNameParam nvarchar(256) 
DECLARE                        @ProfilePropertyNameParam nvarchar(256) 
DECLARE                        @RoleParam nvarchar(256) 
DECLARE                        @UserId uniqueidentifier 
DECLARE                        @currentDate  DateTime 
DECLARE                        @PasswordParam nvarchar(256) 

SET @ApplicationNameParam = 'MyApp'   
SET @RoleParam = 'Administrator' 
SET @UserFullNameParam = 'Van Bladel Paul' 
SET @UserNameParam = 'Admin' 
SET @ProfilePropertyNameParam = 'FullName:S:0:' +LTRIM( STR(LEN(@UserFullNameParam))) 
SET @currentDate = GetDate() 
SET @PasswordParam = 'mysecreatpassword' 
EXEC        @return_value = [dbo].[aspnet_Roles_CreateRole] 
                @ApplicationName = @ApplicationNameParam,@RoleName = @RoleParam 
SELECT        'Return Value' = @return_value 

EXEC        @return_value = [dbo].[aspnet_Membership_CreateUser] 
                @ApplicationName = @ApplicationNameParam,@UserName = @UserNameParam,@Password = @PasswordParam,@PasswordSalt = N'dummy',@Email = N'.',@PasswordQuestion = NULL,@PasswordAnswer = N'.',@IsApproved = True,@CurrentTimeUtc = @currentDate, @CreateDate = @currentDate, @UniqueEmail = 0,@PasswordFormat = 0, @UserId = @UserId OUTPUT 
SELECT        @UserId as N'@UserId' 
SELECT        'Return Value' = @return_value 

EXEC        @return_value = [dbo].[aspnet_Profile_SetProperties] 
                @ApplicationName = @ApplicationNameParam,@PropertyNames = @ProfilePropertyNameParam, @PropertyValuesString = @UserFullNameParam,        @PropertyValuesBinary=0x,        @UserName = @UserNameParam, @IsUserAnonymous = False, @CurrentTimeUtc = @currentDate 
SELECT        'Return Value' = @return_value 

EXEC        @return_value = [dbo].[aspnet_UsersInRoles_AddUsersToRoles] 
                @ApplicationName = @ApplicationNameParam,@UserNames = @UserNameParam,@RoleNames = @RoleParam,@CurrentTimeUtc = @currentDate 
SELECT        'Return Value' = @return_value 
GO

Conclusion

Enjoy !