4

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
  • 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
1

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
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.