Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Obtaining server roles when logging in via a windows group Expand / Collapse
Author
Message
Posted Tuesday, November 24, 2009 9:22 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, October 2, 2013 1:49 AM
Points: 116, Visits: 289
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.
Post #823975
Posted Sunday, November 29, 2009 4:36 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, December 2, 2009 2:07 PM
Points: 70, Visits: 32

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

@nalyticsperformance - Microsoft SQL Server & Windows Server Free Data Collector
Post #825946
Posted Monday, November 30, 2009 10:51 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, October 2, 2013 1:49 AM
Points: 116, Visits: 289
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
Post #826389
Posted Wednesday, December 2, 2009 6:02 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, October 2, 2013 1:49 AM
Points: 116, Visits: 289
I also posted this on the SQL Server 2005 Security forum and got the answer to use IS_SVRROLEMEMBER which solves my problem.
Post #827373
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse