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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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 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
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
You should link your accounts. – Aaron Bertrand Feb 15 '12 at 0:24

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)

RETURNS @dbprincipals TABLE (
    name sysname NOT NULL,
    type char(1) NOT NULL
    INSERT @dbprincipals
    SELECT name, type FROM sys.database_principals;

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

    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

share|improve this answer

Your Answer


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