Problem with stored procedure with is_member function

rdobrich

Hi,
I have following situation:

On one developer server I have stored procedure like this:

CREATE PROCEDURE spG_Test
as
select is_member('server3\birin')
GO

Server3\birin is local Window group on that server.

I try to sync another database on another server. That server doesn't have that local group (it't ok, becouse I use that server as backup server).

When I try to sync I have that script:

/*
Script created by SQL Compare from Red Gate Software Ltd at 12.12.2005 14:39:00
Run this script on bogibatina.birin to make it the same as server3.birin
Please back up your database before running this script
*/
SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#tmpErrors')) DROP TABLE #tmpErrors
GO
CREATE TABLE #tmpErrors (Error int)
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
IF NOT EXISTS (SELECT * FROM master.dbo.syslogins WHERE loginname = N'SERVER3\Birin_Uprava')
exec sp_grantlogin N'SERVER3\Birin_Uprava'
GO
sp_grantdbaccess N'SERVER3\Birin_Uprava', N'SERVER3\Birin_Uprava'
GO
BEGIN TRANSACTION
GO
PRINT N'Creating [dbo].[spG_Test]'
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE spG_Test
as

select is_member('server3\birin')
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF EXISTS (SELECT * FROM #tmpErrors) ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT>0 BEGIN
PRINT 'The database update succeeded'
COMMIT TRANSACTION
END
ELSE PRINT 'The database update failed'
GO
DROP TABLE #tmpErrors
GO

The following error message was returned from the SQL Server:

[15401] Windows NT user or group 'SERVER3\Birin_Uprava' not found. Check the name again.

The following SQL command caused the error:

IF NOT EXISTS (SELECT * FROM master.dbo.syslogins WHERE loginname = N'SERVER3\Birin_Uprava')
exec sp_grantlogin N'SERVER3\Birin_Uprava'

The following messages were returned from the SQL Server:

[5701] Changed database context to 'birin'.
[5703] Changed language setting to us_english.



My question is:
Why SQL compare generate this part:

IF NOT EXISTS (SELECT * FROM master.dbo.syslogins WHERE loginname = N'SERVER3\Birin_Uprava')
exec sp_grantlogin N'SERVER3\Birin_Uprava'
GO
sp_grantdbaccess N'SERVER3\Birin_Uprava', N'SERVER3\Birin_Uprava'
GO

Is_member ('server3\Uprava') returs NULL if 'server3\uprava' doesn't exists on server.

If I try to create Stored procedure through QA and put there just

CREATE PROCEDURE spG_Test
as
select is_member('server3\birin')
GO

It works (As I expected)

Radovan

Andras B

Hi Radovan,

this is rather interesting. Do I understand it right that you try to synchronize only this above stored procedure and the user is included as a dependent of this stored procedure? Could you check that the user is not explicitly selected for synchronization? Note that we create the user for that login if the user is selected explicitly or as a dependent (either as owner, or as a principal that has permissions on a particular object). The later can be surpressed by the "Ignore permissions" option.

Regards,

Andras

rdobrich

Hi Andras,
It is true.
In options I include:
Ignore permissions
Ignore user parmision and role membership
Ignore users properties in cimparision.

I Include manualy only one store procedure to sync.
After that (when it can't sync) I create that test procedure and try it again.
It failes.
I try it on SQL 2000.

Radovan