January 30, 2008 at 4:01 am
Have you tried changing the owner to the Group instead of the individual and leaving it that way? You might not get the same error. In fact, it might be an Active Directory issue more than it is a SQL Server issue (now that I think about it).
January 30, 2008 at 4:08 am
It doesnt let me add the owner as a group, i tried the group i am in (wilmslow\#DBA) and the builtin\administrators and both failed with the error The specified '@owner_login_name' is invalid (valid values are returned by sp_helplogins [excluding Windows NT groups]). (.Net SqlClient Data Provider)
January 30, 2008 at 4:18 am
I'm a little bit confused, then. How can the group have sysAdmin access if the group doesn't have a SQL Server Login?
Check both the server security section and sp_helplogins to see what you have listed as the group name. Maybe it's not the group that has sysAdmin access. Maybe it's individual members of that group. There is a difference.
January 30, 2008 at 4:25 am
The group wilmslow\#DBA is a sysadmin, my user account is not explicity granted access. The error message thats coming up does say that sp_helplogins wont return windows groups. I have never tried setting up a group as the owner, i always specify a user. Would the changeowner SP maybe work do you think?
January 30, 2008 at 4:53 am
I've never had to do that. I've always been able to change the owner via the SSMS GUI.
Just to clarify, what I've always been able to do (because the group itself exists under the Security folder in SSMS) is go to the Job Properties, click on the ellipses to the right of owner. A box pops up. Click on Browse. The group should show up in the list in that box. Check the group name and click OK three times. The job should automatically switch owners.
I've had trouble entering the group name before in just the box that pops up right after clicking the ellipses, even when I put square brackets on either side of the name, but usually when I click browse, it allows me to choose the group as the owner of the job.
What happens when you follow those steps precisely?
January 30, 2008 at 5:02 am
Following those steps i dont see any groups at all when i browse, i only see explicit logins (wilmslow\syssql and NTAUTHORITY\system) there are no groups at all. They are however all under the security tab on SSMS :angry:
January 30, 2008 at 5:09 am
Triple check all the login properties of the group. Make sure the Server Roles really does have SysAdmin checked. If not, check it. If so, then see below.
Test this first on a non-production system (but there should be no reason it wouldn't work on Prod unless something is seriously screwed up). Temporarily give your own individual login SysAdmin access, make then remove and re-add the group, clicking SysAdmin access again. You might have to verify the group doesn't own a schema or has any weird user mappings on it first.
After re-adding the group, try changing the job owner again.
January 30, 2008 at 5:34 am
ok ive done a little more looking around. The wilmslow\#DBA group is definately a sysadmin in the security logins. Even looking at the server roles tab shows the group. There is no other way i could log in and have the access i have unless this group was working.
I have looked on my local system and a test system and neither of them show the groups when i browse for an owner. Im wondering if its something in the AD setup, but as i can see the group when im setting up a new login it leads me to believe it should be ok.
I had another thought that for some odd reason it may have been the # in the group name, so i have just created a test group (wilmslow\testgroup), given it sysadmin rights and i cant see that either when i browse for a new owner.
Really appreciate your time helping me out with this, i know how hard it is when something should be so obvious and it drags on and on! 🙁
Its just one of those days today, nothing is working and i feel coming into work has been less effective than not coming in at all!!
January 30, 2008 at 5:43 am
AD shouldn't affect what you see in SQL Server.
Can you give me a list of all the settings you've got in SQL on that particular group login? Is the SQL Server in the same domain as this login? Or is the login from a different domain?
January 30, 2008 at 6:00 am
ok, on the general tab the name is wilmslow\#DBA and the default DB is master
Server roles public and sysadmin
user mappings - none specified ** will come to this in a second!
Securables - none specified
Status - Granted permissions and login enabled
I have just noticed that the user mappings tab was ticked for master and msdb, not sure when this was ticked, but the user only had public ticked for those two databases, not dbo! When i saved the changes the two jobs i didnt change the user of (two test jobs that still have wilmslow\johnmo as the owner) now work! Not sure why these mappings were there without the dbo ticked, but i always thought that the sysadmin permissions would overwrite that anyway?? Or does it just go in as dbo IF there is no explicit mapping made?
After all that, the groups still dont show up in the change owner browser.
January 30, 2008 at 6:20 am
DB_Owner shouldn't matter. You are correct in assuming that SysAdmin will override that. I was worried about possible DENY permissions getting into the mix. Maybe there are DENY issues on some of the individual logins that prevented that particular login from being able to own & run the job until the Group had DB_Owner access. Hmm.
I'm leaning towards this being a Domain issue, but still not positive. Have you verified what domain the Server was installed in?
When you created the Test Group, did you create it as a Domain Account in AD first? Or did you create this group as a SQL login? If the former, how many Domain Controllers do you have? It might take a while for the new group to replicate out properly.
January 30, 2008 at 6:37 am
sorry i missed that bit out. we only have a single domain and the server is a member of that domain. There are 3 domain controllers in total.
The ad settings for the group (test and the #DBA) are as follows...
Its a global group and the type is a security group. 2 members.
The only deny permissions i can think of and i am looking into it now, is the sp_add_job is denied for a group called Wilmslow\#ReplAdmins (i am not a member of that group). They have permissions to stop and start jobs using the built in msdb role SQLAgentOperatorRole, but i didnt want them adding jobs as well. it was done this way because they needed to be able to see all the replication jobs, and be able to stop and start them, but i didnt want them adding new jobs to the server.
January 30, 2008 at 7:27 am
Hmm. I just went and looked at the Group name that we're using for our jobs. In MSDB, this group has Public, RSExecRole, SQLAgentOperatorRole, SQLAgentReaderRole and SQLAgentUserRole all checked. See if this does anything for you.
RSExecRole, BTW, is related to SSRS so you might not see it in your list.
January 30, 2008 at 9:06 am
Hi H,
Don't know if you got this sorted, but here's a post on the subject og Builtin\Adimins and some good help/advice from K. Brian Kelley
http://www.sqlservercentral.com/Forums/Topic448888-146-1.aspx
HTH
February 3, 2008 at 1:25 pm
Another post i have found about this bizarre issue
http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=2490708&SiteID=17
Shows some kind of work around, definately some weird combination of service packs and OS that creates this error. As per the user in this post we are on Win 2k3 SP1 and SQL 2k5 SP2. I am going to try the whole uppercase lowercase thing tomorrow.
This post also mentions about not being able to have groups as owners. Im not sure how you get to use group owners brandie, its beyond me 😛
Viewing 15 posts - 16 through 30 (of 43 total)
You must be logged in to reply to this topic. Login to reply