July 6, 2009 at 11:11 am
We have a vendor who created a view for their application using the catalog tables.
Here the syntax.
USE [WfcSuite]
GO
/****** Object: View [dbo].[vSY_SYSUSERS] Script Date: 07/06/2009 11:09:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE VIEW [dbo].[vSY_SYSUSERS] AS
/* -- THIS VIEW IS CREATED DYNAMICALLY IN BestIPMMCSrv->clsServerUtilities.cls
AND -- BiDBWiz->modDBRoutines.bas, ANY CHANGES MADE TO THIS VIEW NEED TO BE
APPLIED -- IN BOTH OF THOSE LOCATIONS */
SELECT dp.principal_id AS uid,
dp.name,
dp.sid,
gid = MIN(ISNULL(CASE WHEN dp.name = 'dbo' THEN 0
WHEN dp.type = 'S' THEN drm.role_principal_id
WHEN dp.is_fixed_role = 1 AND dp.type = 'R' THEN 0
WHEN dp.type = 'R' THEN dp.principal_id ELSE 0 END, 0)),
isntgroup = CASE WHEN dp.type = 'G' THEN 1 ELSE 0 END,
isntuser = CASE WHEN dp.type = 'U' THEN 1 ELSE 0 END,
issqluser = CASE WHEN dp.type = 'S' THEN 1 ELSE 0 END,
issqlrole = CASE WHEN dp.type = 'R' THEN 1 ELSE 0 END,
isapprole = CASE WHEN dp.type = 'A' THEN 1 ELSE 0 END
FROM sys.database_principals dp
LEFT JOIN sys.database_role_members drm ON
drm.member_principal_id = dp.principal_id
WHERE name NOT IN('INFORMATION_SCHEMA', 'sys') and
drm.role_principal_id >= 16400
GROUP BY dp.principal_id, dp.name, dp.sid, dp.type
UNION SELECT dp.principal_id AS uid,
dp.name,
dp.sid,
gid = MIN(ISNULL(CASE WHEN dp.name = 'dbo' THEN 0
WHEN dp.type = 'S' AND drm.role_principal_id BETWEEN 16384 AND 16399 THEN 0
WHEN dp.type = 'S' AND drm.role_principal_id IS NULL THEN 0
WHEN dp.type = 'S' THEN drm.role_principal_id
WHEN dp.is_fixed_role = 1 AND dp.type = 'R' THEN 0
WHEN dp.type = 'R' THEN dp.principal_id ELSE 0 END, 0)),
sntgroup = CASE WHEN dp.type = 'G' THEN 1 ELSE 0 END,
isntuser = CASE WHEN dp.type = 'U' THEN 1 ELSE 0 END,
issqluser = CASE WHEN dp.type = 'S' THEN 1 ELSE 0 END,
issqlrole = CASE WHEN dp.type = 'R' THEN 1 ELSE 0 END,
isapprole = CASE WHEN dp.type = 'A' THEN 1 ELSE 0 END
FROM sys.database_principals dp
LEFT JOIN sys.database_role_members drm ON
drm.member_principal_id = dp.principal_id
WHERE name NOT IN('INFORMATION_SCHEMA', 'sys') and
isnull(drm.role_principal_id,0) < 16400 and
not exists (SELECT sub.member_principal_id FROM sys.database_role_members sub WHERE sub.member_principal_id = dp.principal_id and sub.role_principal_id >= 16400) GROUP BY dp.principal_id, dp.name, dp.sid, dp.type UNION SELECT dp.principal_id AS uid, dp.name,dp.sid, gid = MIN(ISNULL(CASE WHEN dp.name = 'dbo' THEN 0 WHEN dp.type = 'S' THEN drm.role_principal_id WHEN dp.is_fixed_role = 1 AND dp.type = 'R' THEN 0 WHEN dp.type = 'R' THEN dp.principal_id ELSE 0 END, 0)), isntgroup = CASE WHEN dp.type = 'G' THEN 1 ELSE 0 END, isntuser = CASE WHEN dp.type = 'U' THEN 1 ELSE 0 END, issqluser = CASE WHEN dp.type = 'S' THEN 1 ELSE 0 END, issqlrole = CASE WHEN dp.type = 'R' THEN 1 ELSE 0 END, isapprole = CASE WHEN dp.type = 'A' THEN 1 ELSE 0 END FROM sys.database_principals dp LEFT JOIN sys.database_role_members drm ON drm.member_principal_id = dp.principal_id WHERE name NOT IN('INFORMATION_SCHEMA', 'sys') and dp.type = 'R' GROUP BY dp.principal_id, dp.name, dp.sid, dp.type
GO
GRANT SELECT ON [dbo].[vSY_SYSUSERS] TO [public]
Our developers can select from this view from the WfcSuite database or any of the systems databases and they get the results they expect but when they try and select from the view using a database we created they get 0 rows returned, no error but no rows and I can't see any differences in the public, guest or information_schema roles. Any help would be very much appreciated.
July 6, 2009 at 1:51 pm
When I run that in a fresh database, I get the expected data.
Break the query down into its component parts, and run each of those in the database that's not giving you rows. For example, try:
select *
FROM sys.database_principals dp
WHERE name NOT IN('INFORMATION_SCHEMA', 'sys') and
not exists
(SELECT *
FROM sys.database_role_members sub
WHERE sub.member_principal_id = dp.principal_id
and sub.role_principal_id >= 16400) ;
See if that gives you any rows.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 7, 2009 at 7:18 am
Still the same result. If the developers run the query from the vendor database they get rows back, if they run the query from the database we created no rows are returned. Sysadmins can run the query and get rows back so I was thinking that it was a setting somewhere that I missed, The guest and information_schema roles exist in both databases as well
July 7, 2009 at 7:23 am
That makes it a permissions issue, almost certainly.
Have them run it under elevated permissions, see what that does. (Take a look at Execute As in Books Online if you aren't sure how to do that.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 8, 2009 at 7:13 am
I found the problem. One of the joins in a view the vendor created uses the user_id() function. When you do a select from the view from a different database the user_id () function returns the uid for the database your running the query from, not the database the query is being run against. Thanks for your help.
July 8, 2009 at 7:17 am
That makes sense. Good job on tracking it down.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply