The site I am working works on SQL Server 2008, but is being adapted to work on SQLEXPRESS.
I am using the Microsoft ASPNETDB.mdf for membership profile roles and personalization, and the main data is stored in another SQLEXPRESS database file: MAINDB.mdf
in some stored procedures I need to check the roles of the current user - which is achieved on 2008 using cross database querying - I can't find a way to make this work in SQLEXPRESS.
CREATE FUNCTION [dbo].uf_GetAccessLevel (
@OwnerID UniqueIdentifier )
DECLARE @Access INT;
-- Users share a role
IF EXISTS (SELECT *
FROM aspnetDB.dbo.aspnet_UsersInRoles AS UIR
JOIN aspnetDB.dbo.aspnet_UsersInRoles AS R2 ON UIR.RoleId = R2.RoleId
WHERE UIR.UserId = @MyUserID
AND R2.UserId = @OwnerID )
SET @Access = 10;
ELSE -- public access only allowed
SET @Access = 0;
The function creates OK when connected but will not run:
Msg 208, Level 16, State 1, Line 1
Invalid object name 'aspnetDB.dbo.aspnet_UsersInRoles'.
My question is: Are cross-database queries allowed in SQLEXPRRESS? and if so, how do you reference one from the other, (both database files are in the same App_Data directory)