LightSwitch Intrinsic DB rights

Question

I am working on a VS Lightswitch Application (C# Desktop Client, SQL Server Database). My Question is now:

In the Application Database i have a role and the Windows User who executes this Client is member of that Role. But which rights does this User need in the Intrinsic DB (which is located on the same sql server). If he is db_owner there of course it works fine but that cannot be the solution? Does he have to be a member of a special role or something?

Any help is appreciated - Thank you!

Answer 1

Solution found! If you publish your solution you will find 2 SQL-Files in Publish-Folder (For execution with SQLCMD!)

• "your_applicatoin".sql
• CreateUser.sql

where CreateUser.sql is a template for adding users to intrinsic db:

:setvar DatabaseName ""
:setvar DatabaseUserName ""
GO

USE [$(DatabaseName)]

DECLARE @usercount int
SELECT @usercount=COUNT(name) FROM sys.database_principals WHERE name = '$(DatabaseUserName)'
IF @usercount = 0
    CREATE USER $(DatabaseUserName) FOR LOGIN $(DatabaseUserName)
GO

EXEC sp_addrolemember N'db_datareader', N'$(DatabaseUserName)'
EXEC sp_addrolemember N'db_datawriter', N'$(DatabaseUserName)'
EXEC sp_addrolemember N'aspnet_Membership_FullAccess', N'$(DatabaseUserName)'
EXEC sp_addrolemember N'aspnet_Roles_FullAccess', N'$(DatabaseUserName)'
EXEC sp_addrolemember N'aspnet_Profile_FullAccess', N'$(DatabaseUserName)'
GO

Just set values for DatabaseName and DatabaseUserName and execute - Works fine.