none
Ability to view fixed server role members;

    Question

  • hi We have a sql 2008 server running sp3/cu2, we need to have users with lower privilege able to run this sp so they can check if a login is memeber of a particular server role? Since they don't have elevated access its always returning incorrect info, any way around this?
    Thanks

    CREATE PROCEDURE dbo.usp_CheckServiceAccountPermission
    /*
    This Stored Procedure is to check if a service account is a member of fixed server role to be run by operator.
    */
    @ServiceAccount nvarchar(55)='domain\Account',
    @RoleMember nvarchar(30)='sysadmin'
    with Execute as self
    AS
    IF EXISTS(SELECT spl.name,spl2.name 
    FROM sys.server_role_members srm 
    INNER JOIN sys.server_principals spl
    ON spl.principal_id = srm.member_principal_id 
    INNER JOIN sys.server_principals spl2 
    ON spl2.principal_id = srm.role_principal_id
    WHERE spl.name=@ServiceAccount AND spl2.name=@RoleMember)
    BEGIN
    SELECT 1 as IsSA
    END
    ELSE
    BEGIN
    SELECT 0 as IsSA
    END
    GO


    Always returns 0

    Please let me know if any workaround available. Also tried using SELECT IS_SRVROLEMEMBER('sysadmin','DOMAIN\ACCT')
    Thanks

    Friday, August 02, 2013 7:15 PM

Answers

  • Tough requirement. VIEW DEFINITION ON LOGIN::<name> would let them see logins, but you would have to run that for every login.

    VIEW ANY DEFINITON would let them see logins, but they would be able to see all objects.

    So I think you need to use Surendra's suggestion of executing the procedure as a more privileged user.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    • Marked as answer by SQL_Jay Friday, August 02, 2013 10:05 PM
    Friday, August 02, 2013 9:16 PM

All replies