I have a routine in my front-end that calls the following stored procedure and examines the resulting recordset to see to which groups the current user belongs. A few controls in the app are then enabled or not, depending on group membership.
Ths procedure used to work just fine, until our IT department decided to move the entire museum into a domain (previously it was just workgroups). They're pretty understanding about it all and willing to work with me, so I asked them to move just the server into the domain first. They did, and I still have admin privileges on it, but this routine quit working when they did that.
If I log in as sa and execute the procedure as is, it works fine, but the app can't run it - get an error message that execute permission was denied on xp_logininfo.
If I uncomment the line 'WITH EXECUTE AS OWNER', I don't get an error, but I also don't get any results. Both as sa, running directly on the server via remote desktop, and from the app, executing it as an ODBC-accessible stored procedure, I get back an empty recordset.
What happens when going to a domain and what do I need to do to make this work?
CREATE PROCEDURE [dbo].[spClenSkupiny]
@Skupina as varchar(100)
--WITH EXECUTE AS OWNER
SET NOCOUNT ON;
declare @Server_Skupina varchar(100)
set @Server_Skupina = 'PALEO-SERVER\' + @Skupina
EXEC master.dbo.xp_logininfo @Server_Skupina, 'members'