February 4, 2005 at 5:06 pm
I am trying to debug this error that is happening intermittently with a SQL job that I did not build.
Msg 15008, Sev 16: User '<username>' does not exist in the current database. [SQLSTATE 42000]
What the job is doing is running daily to load users and logins from a user table to give them DB permissions. The job runs this script:
use <databasename>
declare cUsers cursor for
select user_id, password from users
where user_id not like '%''%'
and user_id not like '% %'
declare @cID varchar(16),
@cPass varchar(6),
@cSQL varchar(128),
@uID int
open cUsers
fetch cUsers into @cID, @cPASS
while @@fetch_status=0
begin
select @uid = uid from sysusers where name = @cID
set @cid = rtrim(@cid)
set @cpass = rtrim(@cpass)
--if statement added to wrap sp_addlogin so this won't throw errors
--basically, if the user exists we do not attempt to add them
if not exists(select * from master.dbo.syslogins where loginname = @cID)
begin
select @csql='sp_addlogin '+@cID+','''+@cPass+''''
exec(@cSQL)
end
select @csql='sp_password null,'''+@cPass+''','+@cID
print @csql
exec(@cSQL)
--if statement added so that we don't try to drop a user that owns any objects, as it will fail if we do.
if not exists (select uid from sysobjects where uid = @uid)
and not exists (select uid from systypes where uid = @uid)
and not exists (select grantor from syspermissions where grantor = @uid)
and not exists (select altuid from sysusers where altuid = @uid)
begin
select @csql='sp_dropuser '+@cID
exec(@cSQL)
select @csql='sp_adduser '+@cID
exec(@cSQL)
end
fetch cUsers into @cID, @cPASS
end
close cUsers
deallocate cUsers
The problem is that the SQL job seems to fail most of the time but will succeed on the second or third re-run. I have been outputing the job results to a text file and there does not seem to be any consistency on where it is breaking.
I have seen a couple of mentions of this error in the forums but it always seems to be associated with one specific user. Not a floating intermitent failure like I am seeing.
If anyone can offer any help it would be appreciated,
Chris
February 4, 2005 at 10:26 pm
The line that drops users is trying to drop users that do not exist in the database yet. You are checking to see if they own any objects, but not if the account actually exists. The error doesn't stop the proc from continuing, so the account is added. The next time you run it with the same data, the user is there so no error.
Another problem is the location of this line:
select @uid = uid from sysusers where name = @cID
It is way too early in the proc to do any good for new users! For new users it is staying set at the same value as the last existing user.
I also think you are doing some extra work you don't have to do. Why are you dropping the users in the first place just to add them right back?
Here is how I would write the proc:
declare cUsers cursor for
select user_id, password from users
where isnull(rtrim(user_id),'') <> ''
declare @cID varchar(16),
@cPass varchar(6),
@cSQL varchar(128)
open cUsers
fetch cUsers into @cID, @cPASS
while @@fetch_status=0
begin
set @cid = rtrim(@cid)
set @cpass = rtrim(@cpass)
--if statement added to wrap sp_addlogin so this won't throw errors
--basically, if the user exists we do not attempt to add them
if not exists(select * from master.dbo.syslogins where loginname = @cID)
begin
exec sp_addlogin @cID,@cPass
end
if not exists(select uid from sysusers where name = @cID)
begin
exec sp_adduser @cID
end
fetch cUsers into @cID, @cPASS
end
close cUsers
deallocate cUsers
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
February 7, 2005 at 9:42 am
Hi Kathi,
Thanks for the feedback.
Another question for you:
The SQL job is set through EM to quit on failure. If the script throws an error when dropping the users wouldn't this prevent SQL from continuing on to the next step (which is to add users) and roll back the transaction for that user?
February 17, 2005 at 9:33 am
As Kathi stated, the reason for failure is that the script checks to make sure users do not own objects in the DB before it tries to drop them but does not check whether the users actually exist. But I do need the sp_dropusers in there right now because he job takes users and logins from a table called "users" and gives them permissions to a reporting DB after it has been restored from a .BAK file every night (I think we should use transactional SQL Repl, but that is not going to happen right now). So, teh users become orphaned due to the SIDs not mathcing up with sysxlogins. Unfortunately I cannot use sp_change_user_login because that does not work with NT accounts.
So, my new question is on how to manipulate the cursor return set data for what I want. In the script at the beginning of the thread you will see that the sp_dropuser is being passed the variable @cID. It is failing on this step because @cID contains users that do not exist in the newly restored reporting DB and fails. So what I need to pass to sp_dropuser is a new variable:
where sysuers.name = @cID
That way it will not try to drop new users. As you can see in the script I need the original @cID to pass to sp_addlogin and sp_adduser. I have been researching this on BOL and the internet and have been trying to create the new variable but something in my syntax is screwy, and I know it is from me not completely understanding the cursor syntax.
Can someone offer guidance on how to add the variable to pass to sp_dropuser?
February 17, 2005 at 9:47 am
>>> Unfortunately I cannot use sp_change_user_login because that does not work with NT accounts. <<<
You can use Mapsids.exe for the task!
HTH
* Noel
February 17, 2005 at 2:06 pm
Yeah, I had read about that tool some. I will play around with it on my machine and see about installing it on the server. Thanks.
I know another way I can do it is by setting up a job to copy over the Master DB to get the SIDs to match.
But, for my own education on the use of cursors though, can anyone answer the question on how I would add that variable I need in the script?
February 17, 2005 at 3:36 pm
sp_change_users_login doesn't work for NT logins because it shouldn't have to. NT logins get their SIDS from NT, so they should not be orphaned.
Also, you can pretty easily fix your problem permanently, meaning you can restore the db all day long and not have orphaned logins. All you need to do is create the logins on the reporting server such that the SIDs are the same as on the production server. I'm including a stored proc which will create the scripts to do that. Run it on the production server, then cut and paste the output into Query Analyzer on the reporting server (look it over and make sure there aren't any logins that you don't want to transfer, it'll transfer them all).
There are 2 procedures. The first one is required for copying the passwords.
Steve
USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
@binvalue varbinary(256),
@hexvalue varchar(256) OUTPUT
AS
DECLARE @charvalue varchar(255)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = '0123456789ABCDEF'
WHILE (@i <= @length)
BEGIN
DECLARE @tempint int
DECLARE @firstint int
DECLARE @secondint int
SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
SELECT @firstint = FLOOR(@tempint/16)
SELECT @secondint = @tempint - (@firstint*16)
SELECT @charvalue = @charvalue +
SUBSTRING(@hexstring, @firstint+1, 1) +
SUBSTRING(@hexstring, @secondint+1, 1)
SELECT @i = @i + 1
END
SELECT @hexvalue = @charvalue
GO
-- af_RevLogins_II
USE afDBA
IF EXISTS
(SELECT *
FROM sysobjects
WHERE id = object_id(N'[dbo].[af_RevLogins_II]')
AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[af_RevLogins_II]
GO
CREATE PROCEDURE af_RevLogins_II
AS
SET NOCOUNT ON
SET ANSI_NULLS ON
/********************************************************************
*****
***** Module Name: af_RevLogins_II
*****
***** Parameters:
***** none
*****
***** Description:
***** Procedure to reverse engineer logins, both NT and SQL,
***** maintaining the sid and password for SQL logins. This is
***** especially useful in log shipping scenarios, and also when
***** copying production databases to QA and Development servers.
*****
***** NOTE: Some of the logic in this script has been directly copied
***** from Microsoft's sp_help_revlogin procedure, some modified, and
***** some original.
*****
***** Written By: Steve Phelps
***** Date: November 18, 2004
*****
***** Modified :
***** Date:
*****
***** Usage:
***** Exec af_RevLogins_II
********************************************************************/
DECLARE
@name sysname,
@xstatus int,
@binpwd varbinary (256),
@txtpwd sysname,
@txt varchar (500),
@command varchar (1000),
@SID_varbinary varbinary(85),
@prod_SID_varbinary varbinary(85),
@SID_string varchar(256),
@nt_sql char(1),
@nt_perm char(1),
@pwd sysname,
@encrypt varchar(20),
@dbname sysname,
@prod_server sysname,
@asterisks char(80)
SET @asterisks = '****************************************************************************************************************'
SET @txtpwd = ''
SET @txt = 'IF EXISTS(SELECT name from master.dbo.sysxlogins WHERE name = @name) EXEC sp_droplogin ''' + @name + ''''
PRINT @asterisks
PRINT 'af_RevLogins_II'
PRINT @asterisks
-- PRINT @asterisks
-- PRINT '
-- Do NOT run the script produced by this procedure
-- without first saving login attributes, permissions and database users!
-- Execute af_RevLoginAttrib, af_RevPermissions, and af_RevDBUsers.
--
-- '
-- PRINT @asterisks
CREATE TABLE #users (#id int identity, #user sysname)
-----------------------------------------------------------------------
-- Pull login information for all logins on the server,
-- and create logic to replicate on standby server if necessary.
DECLARE user_cur CURSOR FOR
SELECT name FROM master.dbo.syslogins
WHERE name <> 'sa'
ORDER BY name
OPEN user_cur
FETCH NEXT FROM user_cur INTO @name
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
PRINT 'IF EXISTS(SELECT name from master.dbo.sysxlogins WHERE name = ''' + @name + ''') EXEC sp_droplogin ''' + @name + '''
GO'
SELECT
@SID_varbinary = sid,
@xstatus = xstatus,
@binpwd = [password]
FROM master.dbo.sysxlogins
WHERE srvid IS NULL
AND [name] = @name
IF (@xstatus & 4) = 4
BEGIN -- NT authenticated account/group
IF (@xstatus & 1) = 1
BEGIN -- NT login is denied access
SET @command = 'EXEC master..sp_denylogin ''' + @name + ''''
-- PRINT @command
END -- IF (@xstatus & 1) = 1
ELSE BEGIN -- NT login has access
SET @command = 'EXEC master..sp_grantlogin ''' + @name + ''''
-- PRINT @command
END -- IF (@xstatus & 1) = 1
END -- IF (@xstatus & 4) = 4
ELSE BEGIN -- SQL Server authentication
IF (@binpwd IS NOT NULL)
BEGIN -- Non-null password
SET @command = 'DECLARE @pwd sysname '
EXEC sp_hexadecimal @binpwd, @txtpwd OUT
IF (@xstatus & 2048) = 2048
SET @command = @command + 'SET @pwd = CONVERT (varchar(256), ' + @txtpwd + ') '
ELSE -- IF (@xstatus & 2048) = 2048
BEGIN
SET @command = @command + 'SET @pwd = CONVERT (varbinary(256), ' + @txtpwd + ') '
-- PRINT @command
END -- IF (@xstatus & 2048) = 2048
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
SET @command = @command + 'EXEC master..sp_addlogin ''' + @name
+ ''', @pwd, @sid = ' + @SID_string + ', @encryptopt = '
END -- IF (@binpwd IS NOT NULL)
ELSE BEGIN
-- Null password
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
SET @command = 'EXEC master..sp_addlogin ''' + @name
+ ''', NULL, @sid = ' + @SID_string + ', @encryptopt = '
END -- IF (@binpwd IS NOT NULL) ELSE CLAUSE
IF (@xstatus & 2048) = 2048
-- login upgraded from 6.5
SET @command = @command + '''skip_encryption_old'''
ELSE BEGIN -- IF (@xstatus & 2048) = 2048
SET @command = @command + '''skip_encryption'''
-- PRINT @command
END -- IF (@xstatus & 2048) = 2048 ELSE CLAUSE
END -- IF (@xstatus & 4) = 4
END -- IF (@@fetch_status <> -2)
SET @command = @command + '
GO'
PRINT @command
getnext:
FETCH NEXT FROM user_cur INTO @name
END -- WHILE (@@fetch_status <> -1)
cleanup:
CLOSE user_cur
DEALLOCATE user_cur
DROP TABLE #users
-- PRINT @asterisks
-- EXEC af_RevLoginAttrib
-- PRINT @asterisks
-- EXEC af_RevDBUsers
-- PRINT @asterisks
-- EXEC af_RevPermissions
-------------------------------------------------------------------------
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply