Sql-server – sql 2008, select from a view as a different user, like owner

sql serversql-server-2008view

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

Best Answer

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