April 13, 2010 at 4:01 am
Can anyone shed any light? Working with one of our devs to try and use default schemas to allow a stored proc to access multiple data sets (same object names but multiple schemas). But we could not get this to work.
Further investigation reveals the dev seems to be running as user "dbo" rather than his own login, even though it shows as his login under sp_who and on object explorer in SSMS.
Further investigation (trying execute as and xp_logininfo) returns
Could not obtain information about Windows NT group/user 'Domain\xxxxxxx', error code 0x6e.
I am thinking that this means our SQL 2005 box is NOT capable of authenticating the windows logins and I haven't a clue why! And if it cannot, then why default the user to dbo?!?? Massive security hole and massive headache all in one!
Please enlighten me asap! :w00t:
April 13, 2010 at 5:08 am
The user was tied into BUILTIN\Administrators as he is in the local admin group on the domain. I took sysadmin off of BUILTIN\Administrators and it allowed him to work. I have now taken him out of the local admin group and this works.
However - He needs to be in a local admin group so thinking about getting the network guys to set up a new group with equivalent privileges? Would that avoid tying into BUILTIN\Administrators?
As a side issue though - I'm still confused as to why our SQL 2005 servers are having trouble authenticating windows users...Would love advice on that please!
April 26, 2010 at 5:57 am
Going back to this one.
It turns out the machine isn't liking users from our other domain. Our support guys are adiment that the trust is set up properly. I can only get this domain to authenticate properly if I run the SQL service as a user from that actual domain.
Because the trust is in place, they are suggesting that the sql box isn't set up properly.... :rolleyes:
April 27, 2010 at 9:03 am
I'm not sure this helps but here we go:
1 Stored procs owned by dbo is OK.
2 When a user has rights to run the stored proc, if all the objects in the proc have dbo as owner...then the procedure is OK. (Other objects such as tables, views, etc) You have a complete ownership chain.
3 If the other objects are owned by other owners....then you have to give the user rights to each object.
4. Life is much easier if everything is owned by dbo. The good reason to not have stuff owned by dbo is for distinct securtity reasone.
5. The owner dbo is morphed into a schema in recent SQL servers. 2005, 2008. I think it still works the same.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply