Post reply

SQLAgentUserRole and Domain/Group as Login

  • September 29, 2009 at 12:53 pm

    #385461

    I have a database that has one login (Domain/group1)

    Domain/group1 is assigned to SQLAgentUserRole.

    Domain/group1 has 2 users (Domain\user1 and Domain\user2).

    Now. Domain\user1 logs into the Server and creates an agent job (TestJob1)

    The owner of TestJob1 is labeled as Domain\user1 instead of Domain\group1.

    Just to validate that there is an issue Domain\user2 logs into sql server and cannot see Testjob1 at all. let alone execute

    Although there is only one login on the server and that login is a group. The owner of the agent job is still a user within the group....and access is restricted to other users within the group.

    Any thoughts anybody? This seems like some sort of bug to me.

    If I :

    1.go to an agent job through SSMS.

    2.Open it and navigate to the 'General' tab.

    3.Click owner

    4.And click where it says 'examples'

    It shows an example of entering a group, however, I cannot.

    This does not seem very 'Enterprise'

    Any help would be greatly appreciated.

  • September 30, 2009 at 11:59 am

    #1060109

    nobody else has experienced this?

  • September 30, 2009 at 4:10 pm

    #1060218

    I think you have to make it a sql agent operator instead of sql agent user.

  • October 1, 2009 at 4:53 am

    #1060368

    Thanks for the reply. That would really defeat my purpose though.

    I need a group to be able to be the owner of an agent job.

    So imagine there are two groups Group1 and Group2....and there are 2 agent jobs JobA and JobB.

    I need Group1 to own JobA.

    I need Group2 to own JobB.

    I do not want Group1 to be able to execute JobB.

    If I increased the job agent permissions I would not be able to accomplish that.

    Furtherm, I am still not sure that allows a group to own the job.

  • October 1, 2009 at 1:39 pm

    #1060664

    Am I communicating my problem ineffectively or are we all legitimately stumped? Somehow I thought there would be more interest in this topic, since it pertains to enterprise scalability.

  • October 1, 2009 at 2:18 pm

    #1060686

    A domain group cannot own a job. That's why, in your first example, Domain\user1 was listed as the owner of the job he created. Group membership allows an individual to connect to SQL Server, but any ownership (object, database, job, etc.) is mapped to an individual.

    Greg

  • October 1, 2009 at 4:49 pm

    #1060743

    That sounds reasonale, however, if you look back to one of my earlier posts I explain how there is an example that clearly displays a group being entered as an owner.

    Also, woldnt that kind of defeat the purpose of allowing group administration in sql server??

  • October 1, 2009 at 5:35 pm

    #1060748

    I can't speak to the example shown in the Select Login box. All I know is that when I click the browse button, I don't see any groups, only individual logins.

    Like I said, groups allow connecting to SQL Server without specifying individuals, but I know from experience that a group cannot own anything.

    Greg

  • October 2, 2009 at 4:52 am

    #1060840

    cool. thanks for the help man.

    I find that a little frustrating that a group can be defined as a login in sql server, but then that login can't be used for ownership of an object. Seems pointless.

    I am going to complain to Microsoft about this one.

    Thanks again.

Viewing 9 posts - 1 through 8 (of 8 total)

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