Obtaining server roles when logging in via a windows group

  • Whenever a user logs into SQL Server 2008 via a Windows group how can I determine which server roles are applicable to that user when only the windows group is listed in sys.server_principals

    The user and suser functions return information to the user's windows login not the group that they entered SQL Server by.

    Help appreciated.

  • You can get the list of Active Directory users for every Group create in SQL Server.

    Regards

    SNM

    Try Free Microsoft SQL Server Data Collector & Performance Monitor.

    http://www.analyticsperformance.com[/url]

    @nalyticsperformance - Microsoft SQL Server & Windows Server Free Data Collector

  • Thanks for the advice. So it looks like I've got to:

    1) Create a linked server to OLE DB Provider for Microsoft Directory Services

    2) Find out which Windows Group(s) the user belongs to

    3) Determine the superset of server-roles that are applicable to the Windows Groups returned in step 2.

    A piece of cake considering my LDAP is very rusty :crazy:

  • I also posted this on the SQL Server 2005 Security forum and got the answer to use IS_SVRROLEMEMBER which solves my problem.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply