Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

In sql server 2008, the system views sys.database_role_members and sys.database_principals, only returns the users and group that the current account is allowed to view.

I need to check id a certain account exists, so I need to see all the existing sql account accounts, regardless the permissions of the current account, I mean, just like it used to be in previous versions.

Is there some option like 'EXECUTE AS OWNER' but for views?

Something like

create view dbo.vUsers select as owner as

select * from sys.database_principals
share|improve this question

migrated from stackoverflow.com Feb 14 '12 at 23:16

This question came from our site for professional and enthusiast programmers.

    
Unfortunately this is a "by design" kind of thing for security reasons. Look up "Metadata Visibility Configuration" in BOL for more info on how to work within these constraints. –  Mitch Schroeter Feb 14 '12 at 21:59
3  
Can't you use a stored procedure for this purpose, instead of giving people direct access to the view? The WHERE clause could be tied to ORIGINAL_LOGIN even if the procedure is marked as EXECUTE AS OWNER... –  Aaron Bertrand Feb 14 '12 at 22:06
    
Well, in fact I'm using a system account (something like domain\appService), and they don't have direct access to the view, I'm using it from a stored procedure. So far now I had to give db_securityadmin permissions to that account, because sys.* tables filter what is visible, but I'm trying to avoid it... –  opensas Feb 14 '12 at 22:31
    
Hey, didn't know about dba.stackexchange, I'll follow your advice (hey, I trust your reputation ;-) –  opensas Feb 14 '12 at 23:14
1  
You should link your accounts. –  Aaron Bertrand Feb 15 '12 at 0:24

2 Answers 2

Use a table valued function instead?

It has to be a multistatement function though.
In-line UDFS don't allow EXECUTE AS (I guess because they are simply macros like views)

CREATE FUNCTION dbo.vUsers ()
RETURNS @dbprincipals TABLE (
    name sysname NOT NULL,
    type char(1) NOT NULL
    )
WITH EXECUTE AS OWNER
AS
BEGIN
    INSERT @dbprincipals
    SELECT name, type FROM sys.database_principals;

    RETURN;
END
GO
share|improve this answer

The more transparent solution I can think so far is to create a couple of tables like sys_database_principals and sys_database_roles,

update the view to point at them

and then define a stored procedure, that will be executed as owner no matter the currently logged account, and woul fill the tables like this

create procedure dbo.tmp_fill with execute as owner as
begin

    if exists( select * from sysobjects where type = 'U' and name = 'sys_database_role_members' )
        drop table dbo.sys_database_role_members
    select * into sys_database_role_members from sys.database_role_members

    if exists( select * from sysobjects where type = 'U' and name = 'sys_database_principals' )
        drop table dbo.sys_database_principals
    select * into sys_database_principals from sys.database_principals

end
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.