June 11, 2009 at 3:11 pm
Hi,
I want to give our 'domain\Domain Users' group execute on a SP, I don't want to allow them to see the base tables.
I created a login for domain\Domain Users and created a database user and granted execute on the SP. The SP has WITH EXECUTE AS OWNER, the owner is dbo.
This fails with the following
Could not obtain information about Windows NT group/user '
What is the easiest way to give domain users execute on a SP?
Thanks
Mike
June 11, 2009 at 8:39 pm
Hello Mike,
That sounds like a reasonable method to me.
Can you post the full error that you get, especially the Error Number and State?
BTW – Do you get the error when you assign Execute Permission or when the user runs the SP?
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
June 12, 2009 at 2:01 am
Hi John,
This is the error I get when I execute the SP from my script
C:\GetLocation.vbs(24, 1) Microsoft OLE DB Provider for SQL Server: Could not obtain information about Windows NT group/user 'FM\FlemimG', error code 0x2.
I get this even when I connect to the db usign a SQL level account. I dont get the error when I take out the WITH EXECUTE AS OWNER from the SP definition.
PS: the SQL service is running as local system, will this cause a problem querying for domain information?
I am geussing I need to change the owner of the SP from dbo, as dbo does not map to an accoutn with the ability to query the domain?
Thanks
Mike
June 12, 2009 at 4:38 pm
Hello Mike,
It may be difficult to diagnose without the State part of the Error Message. Do you get a corresponding entry in either the Event Log or SQL Server Error Log? (Preferbaly which includes the State). Also do you get any “Failure Audit” errors at the same time?
It could be that the Local System account does not have enough privileges to properly interact with the Domain Controller. As MS anyway recommends running SQL Server under a Windows Domain account (rather than Local System), you should probably change this regardless.
Another possible cause is the Owner of the DB does not have sufficient permissions. If that is the case then try changing the Owner to SA.
One other thought – does the SP try to interact with anything outside of the DB e.g. send an e-mail or work with a table in another DB?
Regards,
John
www.sql.lu
SQL Server Luxembourg User Group
June 12, 2009 at 5:02 pm
mike.fleming (6/12/2009)
Hi John,This is the error I get when I execute the SP from my script
C:\GetLocation.vbs(24, 1) Microsoft OLE DB Provider for SQL Server: Could not obtain information about Windows NT group/user 'FM\FlemimG', error code 0x2.
I get this even when I connect to the db usign a SQL level account. I dont get the error when I take out the WITH EXECUTE AS OWNER from the SP definition.
PS: the SQL service is running as local system, will this cause a problem querying for domain information?
I am guessing I need to change the owner of the SP from dbo, as dbo does not map to an account with the ability to query the domain?
Thanks
Mike
might be wrong here but is 'FM\FlemimG' the owner of the database? If this windows user is not explicitly defined to SQL you would get this error. In which case either change the owner of the SP or probably better change the owner of the database. I would guess that fleminG is only the db owner because that id was used to create or restore the database.
This link http://msdn.microsoft.com/en-us/library/ms178106.aspx would suggest that if you change the owner of the SP the new owner would need correct permissions on all referenced objects, hence better to change dbowner
---------------------------------------------------------------------
June 12, 2009 at 5:06 pm
...another thing, is execute as owner really necessary?
---------------------------------------------------------------------
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply