none
"Unable to use SQL Server because ASP.NET version 2.0 Session State is not installed on the SQL server" due to roles configuration issue

    Question

  • RE: "Unable to use SQL Server because ASP.NET version 2.0 Session State is not installed on the SQL server. Please install ASP.NET Session State SQL Server version 2.0 or above."

    I have been attempting to lock down the permissions of my SQL Server (after successfully installing the Session State to my server).
    Originally, when the SQL Server user is assigned the db_owner role, the error does not occur. Now I have created a different role and basically disabled "VIEW DEFINITION", table permissions and so on to make the role as minimalist as possible.

    My question is: does anyone know the required set of permissions for the Session State to work? I have granted my user the roles "aspnet_Membership_BasicAccess", "aspnet_Personalization_BasicAccess", "aspnet_Profile_BasicAccess", "aspnet_Roles_BasicAccess" but these roles are insufficient to remove the error message.

    Thanks,
    SC
    • Edited by SamCPP Tuesday, October 06, 2009 5:56 AM spelling
    • Moved by VMazur Tuesday, October 06, 2009 10:24 AM (From:ADO.NET Data Providers)
    Tuesday, October 06, 2009 5:56 AM

Answers

  • Here is a script I have written to generate a table containing the permissions I create for my new role:
    CREATE FUNCTION [dbo].[fnGenerateNewTDBWebUserRole]
    (
    	@rolename SYSNAME
    )
    RETURNS 
    @ReturnTable TABLE
    (
    	ID INT IDENTITY(1,1),
    	FieldValue NVARCHAR(4000)
    )
    AS
    BEGIN
    	DECLARE @bUserTables BIT
    	DECLARE @bTVF BIT -- TABLE VALUED FUNCTIONS
    	DECLARE @Execute NVARCHAR(4000)
    
    	INSERT INTO @ReturnTable
    	SELECT 'DROP ROLE ' + @rolename
    
    	INSERT INTO @ReturnTable
    	SELECT 'GO'
    
    	INSERT INTO @ReturnTable
    	SELECT 'CREATE ROLE ' + @rolename
    
    	INSERT INTO @ReturnTable
    	SELECT 'GO'
    
    	-- Database statement permissions
    	INSERT INTO @ReturnTable
    	SELECT 'DENY CREATE DEFAULT, CREATE PROCEDURE, CREATE RULE, CREATE TABLE, CREATE VIEW, BACKUP DATABASE, BACKUP LOG TO ' + @rolename
    
    	-- Try to lock off the systables
    	--INSERT INTO @TempTable
    	--SELECT 'DENY SELECT, VIEW DEFINITION, UPDATE ON sys.' + QUOTENAME(name) + ' TO ' + @rolename FROM sys.sys_objects
    	--WHERE type = 'S'
    
    	-- USER TABLES AND INLINE FUNCTIONS
    	INSERT INTO @ReturnTable
    	SELECT 'DENY ALTER, CONTROL, DELETE, INSERT, REFERENCES, SELECT, TAKE OWNERSHIP, UPDATE, VIEW DEFINITION ON ' + QUOTENAME(name) + ' TO ' + @rolename FROM sys.objects
    	WHERE (type = 'U' OR type = 'IF')
    	AND name NOT LIKE 'aspnet_%'
    	AND name NOT LIKE 'ASPStateTemp%'
    
    	-- STORED PROCS
    	INSERT INTO @ReturnTable
    	SELECT 'DENY VIEW DEFINITION, ALTER, TAKE OWNERSHIP ON ' + QUOTENAME(name) + ' TO ' + @rolename FROM sys.objects
    	WHERE type = 'P' AND name <> 'TempGetVersion'

    -- Now need to grant VIEW DEFINITION access to TempGetVersion for
    -- the session state server to work
    INSERT INTO @ReturnTable
    SELECT 'DENY ALTER, TAKE OWNERSHIP ON ' + QUOTENAME(name) + ' TO ' + @rolename FROM sys.objects
    WHERE type = 'P' AND name = 'TempGetVersion'

    INSERT INTO @ReturnTable
    SELECT 'GRANT VIEW DEFINITION ON ' + QUOTENAME(name) + ' TO ' + @rolename FROM sys.objects
    WHERE type = 'P' AND name = 'TempGetVersion'

    INSERT INTO @ReturnTable SELECT 'GRANT EXECUTE ON ' + QUOTENAME(name) + ' TO ' + @rolename FROM sys.objects WHERE type = 'P' INSERT INTO @ReturnTable SELECT 'DENY EXECUTE ON ' + QUOTENAME(name) + ' TO ' + @rolename FROM sys.objects WHERE type = 'P' -- VIEWS INSERT INTO @ReturnTable SELECT 'DENY ALTER, TAKE OWNERSHIP, VIEW DEFINITION, INSERT, UPDATE, DELETE, REFERENCES ON ' + QUOTENAME(name) + ' TO ' + @rolename FROM sys.objects WHERE type = 'V' AND name NOT LIKE 'vw_aspnet_%' INSERT INTO @ReturnTable SELECT 'GRANT SELECT ON ' + QUOTENAME(name) + ' TO ' + @rolename FROM sys.objects WHERE type = 'V' AND name NOT LIKE 'vw_aspnet_%' -- TABLE VALUED FUNCTIONS INSERT INTO @ReturnTable SELECT 'DENY ALTER, CONTROL, REFERENCES, SELECT, TAKE OWNERSHIP, VIEW DEFINITION ON ' + QUOTENAME(name) + ' TO ' + @rolename FROM sys.objects WHERE type = 'TF' -- SCALAR FUNCTIONS INSERT INTO @ReturnTable SELECT 'DENY ALTER, CONTROL, REFERENCES, EXECUTE, TAKE OWNERSHIP, VIEW DEFINITION ON ' + QUOTENAME(name) + ' TO ' + @rolename FROM sys.objects WHERE type = 'FN' INSERT INTO @ReturnTable SELECT 'GO' --SELECT * FROM @TempTable ORDER BY ID RETURN END
    • Marked as answer by SamCPP Thursday, October 08, 2009 3:16 AM
    • Unmarked as answer by SamCPP Thursday, October 08, 2009 3:29 AM
    • Marked as answer by SamCPP Thursday, October 29, 2009 1:24 AM
    • Edited by SamCPP Thursday, October 29, 2009 1:28 AM Fixing for TempGetVersion permissions for session state server
    Thursday, October 08, 2009 2:58 AM
  • Using SQL Profiler (which I should have done in the first place!), a query is run on login "Select name from sysobjects where type = 'P' and name = 'TempGetVersion'". So there is the culprit! Just need VIEW DEFINITION access to that SP and the role permissions are complete.
    • Marked as answer by SamCPP Thursday, October 08, 2009 10:19 PM
    Thursday, October 08, 2009 10:19 PM

All replies

  • No ideas?
    Thursday, October 08, 2009 2:38 AM
  • Here is a script I have written to generate a table containing the permissions I create for my new role:
    CREATE FUNCTION [dbo].[fnGenerateNewTDBWebUserRole]
    (
    	@rolename SYSNAME
    )
    RETURNS 
    @ReturnTable TABLE
    (
    	ID INT IDENTITY(1,1),
    	FieldValue NVARCHAR(4000)
    )
    AS
    BEGIN
    	DECLARE @bUserTables BIT
    	DECLARE @bTVF BIT -- TABLE VALUED FUNCTIONS
    	DECLARE @Execute NVARCHAR(4000)
    
    	INSERT INTO @ReturnTable
    	SELECT 'DROP ROLE ' + @rolename
    
    	INSERT INTO @ReturnTable
    	SELECT 'GO'
    
    	INSERT INTO @ReturnTable
    	SELECT 'CREATE ROLE ' + @rolename
    
    	INSERT INTO @ReturnTable
    	SELECT 'GO'
    
    	-- Database statement permissions
    	INSERT INTO @ReturnTable
    	SELECT 'DENY CREATE DEFAULT, CREATE PROCEDURE, CREATE RULE, CREATE TABLE, CREATE VIEW, BACKUP DATABASE, BACKUP LOG TO ' + @rolename
    
    	-- Try to lock off the systables
    	--INSERT INTO @TempTable
    	--SELECT 'DENY SELECT, VIEW DEFINITION, UPDATE ON sys.' + QUOTENAME(name) + ' TO ' + @rolename FROM sys.sys_objects
    	--WHERE type = 'S'
    
    	-- USER TABLES AND INLINE FUNCTIONS
    	INSERT INTO @ReturnTable
    	SELECT 'DENY ALTER, CONTROL, DELETE, INSERT, REFERENCES, SELECT, TAKE OWNERSHIP, UPDATE, VIEW DEFINITION ON ' + QUOTENAME(name) + ' TO ' + @rolename FROM sys.objects
    	WHERE (type = 'U' OR type = 'IF')
    	AND name NOT LIKE 'aspnet_%'
    	AND name NOT LIKE 'ASPStateTemp%'
    
    	-- STORED PROCS
    	INSERT INTO @ReturnTable
    	SELECT 'DENY VIEW DEFINITION, ALTER, TAKE OWNERSHIP ON ' + QUOTENAME(name) + ' TO ' + @rolename FROM sys.objects
    	WHERE type = 'P' AND name <> 'TempGetVersion'

    -- Now need to grant VIEW DEFINITION access to TempGetVersion for
    -- the session state server to work
    INSERT INTO @ReturnTable
    SELECT 'DENY ALTER, TAKE OWNERSHIP ON ' + QUOTENAME(name) + ' TO ' + @rolename FROM sys.objects
    WHERE type = 'P' AND name = 'TempGetVersion'

    INSERT INTO @ReturnTable
    SELECT 'GRANT VIEW DEFINITION ON ' + QUOTENAME(name) + ' TO ' + @rolename FROM sys.objects
    WHERE type = 'P' AND name = 'TempGetVersion'

    INSERT INTO @ReturnTable SELECT 'GRANT EXECUTE ON ' + QUOTENAME(name) + ' TO ' + @rolename FROM sys.objects WHERE type = 'P' INSERT INTO @ReturnTable SELECT 'DENY EXECUTE ON ' + QUOTENAME(name) + ' TO ' + @rolename FROM sys.objects WHERE type = 'P' -- VIEWS INSERT INTO @ReturnTable SELECT 'DENY ALTER, TAKE OWNERSHIP, VIEW DEFINITION, INSERT, UPDATE, DELETE, REFERENCES ON ' + QUOTENAME(name) + ' TO ' + @rolename FROM sys.objects WHERE type = 'V' AND name NOT LIKE 'vw_aspnet_%' INSERT INTO @ReturnTable SELECT 'GRANT SELECT ON ' + QUOTENAME(name) + ' TO ' + @rolename FROM sys.objects WHERE type = 'V' AND name NOT LIKE 'vw_aspnet_%' -- TABLE VALUED FUNCTIONS INSERT INTO @ReturnTable SELECT 'DENY ALTER, CONTROL, REFERENCES, SELECT, TAKE OWNERSHIP, VIEW DEFINITION ON ' + QUOTENAME(name) + ' TO ' + @rolename FROM sys.objects WHERE type = 'TF' -- SCALAR FUNCTIONS INSERT INTO @ReturnTable SELECT 'DENY ALTER, CONTROL, REFERENCES, EXECUTE, TAKE OWNERSHIP, VIEW DEFINITION ON ' + QUOTENAME(name) + ' TO ' + @rolename FROM sys.objects WHERE type = 'FN' INSERT INTO @ReturnTable SELECT 'GO' --SELECT * FROM @TempTable ORDER BY ID RETURN END
    • Marked as answer by SamCPP Thursday, October 08, 2009 3:16 AM
    • Unmarked as answer by SamCPP Thursday, October 08, 2009 3:29 AM
    • Marked as answer by SamCPP Thursday, October 29, 2009 1:24 AM
    • Edited by SamCPP Thursday, October 29, 2009 1:28 AM Fixing for TempGetVersion permissions for session state server
    Thursday, October 08, 2009 2:58 AM
  • Interestingly, that proc above should actually work in conjunction with the aspnet *basic_access roles. For reference, you need to grant access to the stored procs named "Temp..." as the aspnet roles do not grant such access. The role permission generator listed above will do this as it grants execute permission to all stored procs.

    My actual code had an additional constraint on the stored proc execute permission:
    INSERT INTO @ReturnTable
    	SELECT 'GRANT EXECUTE ON ' + QUOTENAME(name) + ' TO ' + @rolename FROM sys.objects
    	WHERE type = 'P' 		
    		AND (SUBSTRING(name,1,2) = 'sp' OR SUBSTRING(name,1,4) = 'Temp')
    My stored procs that I want accessible are prefixed with "sp" (note. I have not used "sp_" as the MSDN docs recommend against using that).
    • Marked as answer by SamCPP Thursday, October 08, 2009 3:25 AM
    • Unmarked as answer by SamCPP Thursday, October 08, 2009 3:29 AM
    Thursday, October 08, 2009 3:25 AM
  • Damn... that wasn't it. Republished the site and got the error again. Back to the drawing board!
    Thursday, October 08, 2009 3:30 AM
  • After a bit more investigation, it appears that one (or more) of the SPs "Temp..." needs to have VIEW DEFINITION access granted. I'll keep whittling the list down until I find the exact element.
    Thursday, October 08, 2009 5:31 AM
  • Using SQL Profiler (which I should have done in the first place!), a query is run on login "Select name from sysobjects where type = 'P' and name = 'TempGetVersion'". So there is the culprit! Just need VIEW DEFINITION access to that SP and the role permissions are complete.
    • Marked as answer by SamCPP Thursday, October 08, 2009 10:19 PM
    Thursday, October 08, 2009 10:19 PM
  • Granting DB_OWNER does the trick.

    Thursday, February 18, 2010 6:59 PM