SQL table permissions for ASPNET user

I'm trying to run something like the following script from sql management studio...

--
--Set this to the name of your database
--
use TableProfileProviders
go

--
--grants on ASP.NET stored procedures and tables used by the custom providers
--
grant EXECUTE on dbo.aspnet_Applications_CreateApplication to [YOURMACHINENAME\ASPNET]
grant EXECUTE on dbo.aspnet_Users_CreateUser to [YOURMACHINENAME\ASPNET]
grant SELECT on dbo.aspnet_Users to [YOURMACHINENAME\ASPNET]
grant UPDATE on dbo.aspnet_Users(LastActivityDate) to [YOURMACHINENAME\ASPNET]
go

--drop table dbo.ProfileTable_1
--go

create table dbo.ProfileTable_1 (
UserId uniqueidentifier not null Primary Key,
FirstName nvarchar(50) null,
LastName nvarchar(50) null,
Age int null,
LastUpdatedDate datetime not null)
go

grant SELECT,INSERT,UPDATE,DELETE on dbo.ProfileTable_1 to [YOURMACHINENAME\ASPNET]
go

--
--Get stored procedure
--
--drop procedure getCustomProfileData
create procedure getCustomProfileData
@ApplicationName nvarchar(256),
@UserName nvarchar(256),
@FirstName nvarchar(50) OUTPUT,
@LastName nvarchar(50) OUTPUT,
@Age int OUTPUT
as

declare @ApplicationId uniqueidentifier
set @ApplicationId = NULL

--Get the appid
exec dbo.aspnet_Applications_CreateApplication @ApplicationName, @ApplicationId OUTPUT

--Return data for the requested user in the application
select @FirstName = FirstName,
@LastName = LastName,
@Age = Age
from dbo.ProfileTable_1 pt,
dbo.vw_aspnet_Users u
where u.ApplicationId = @ApplicationId
and u.UserName = @UserName
and u.UserId = pt.UserId
go

grant EXECUTE on dbo.getCustomProfileData to [YOURMACHINENAME\ASPNET]
go

--
--Set stored procedure
--
--drop procedure setCustomProfileData
create procedure setCustomProfileData
@ApplicationName nvarchar(256),
@UserName nvarchar(256),
@IsUserAnonymous bit,
@FirstName nvarchar(50),
@LastName nvarchar(50),
@Age int
as

declare @ApplicationId uniqueidentifier
set @ApplicationId = NULL

declare @CurrentUtcDate datetime
set @CurrentUtcDate = getutcdate()

--Get the appid
exec dbo.aspnet_Applications_CreateApplication @ApplicationName, @ApplicationId OUTPUT

--Create user if needed
declare @UserId uniqueidentifier

select @UserId = UserId
from dbo.vw_aspnet_Users
where ApplicationId = @ApplicationId
and LoweredUserName = LOWER(@UserName)

if(@UserId IS NULL)
exec dbo.aspnet_Users_CreateUser @ApplicationId, @UserName, @IsUserAnonymous, @CurrentUtcDate, @UserId OUTPUT

--Either insert a new row of data, or update a pre-existing row
if exists (select 1 from dbo.ProfileTable_1 where UserId = @UserId)
BEGIN
update dbo.ProfileTable_1
set FirstName = @FirstName,
LastName = @LastName,
Age = @Age,
LastUpdatedDate = @CurrentUtcDate
where UserId = @UserId
END
else
BEGIN
insert dbo.ProfileTable_1 (UserId, FirstName, LastName, Age, LastUpdatedDate)
values (@UserId, @FirstName, @LastName, @Age, @CurrentUtcDate)
END
go

grant EXECUTE on dbo.setCustomProfileData to [YOURMACHINENAME\ASPNET]
go






























I was told by support that I had to create a application role and grant permission to it. How can I get this all to work?

 

So I'd have to run the script for an existing DB user and then impersonate that user?

 

ASP.NET 2.0 stores it's profile information in a screwed up way that can be hard to query. I found a table profile provider (http://www.asp.net/downloads/sandbox/table-profile-provider-samples/) that replaces the way ASP.NET handles the profile info and makes it so you can build your own profile table and have it work with the existing membership tables. That script came with the provider. In order for it to work, the user (which is normally ASPNET_User) needs certain access to the custom profile table. So I guess if I can run that script for a SQL user and then have my ASP.NET application impersonate that user rather than the default ASPNET_User, it should work. Thoughts? Lost?

 

Our db server is completely separate from the web server. So the ASPNet user never touches the database or the tables that resides in it. All authentication is done using the db login.

rcp
DiscountASP.NET
www.DiscountASP.NET