'User already exists' error after Restore--what? 2

[jsteph]

I know this is a common issue, but I still haven't been able to find a suitable solution.

The problem is the above error after a Restore, and my searching on this issue say's it's an 'Orphaned' user. So all the fixes I've seen say to do the below:

sp_change_users_login 'update_one', 'blah','blah'

This does indeed add that user back to the list of users, but it does not seem to add the permissions the user had.

So now when I try to do certain things logged on as that user, I get the 'permission denied' error.

Have I missed a step on this? Thanks,
--Jim

 

[ptheriault]

sp_change_users_login will correct SID mismatches. I will not correct permissions.

- Paul
- Database performance looks fine, it must be the Network!

 

[jsteph]

Ok...is there a way to fix the permissions other than manually, object by object?

I mean, it just seems that if you're restoring a database, that happens to be from another server, the permissions are database-level, and not server-level, so once the user is mapped to the login, that user should have the rights that were granted in the first place.

Does anyone know if there is a fix for this, or is this a manual thing? I would have to think with a problem this huge that there's something out there to address this,
Thanks,
--Jim

 

[ptheriault]

permissions would only be dropped if you are deleting the user from the database before you run sp_change_users_login. Here is a script that can script out user permissions for you. Just run the script it creates on the restored instance.

DECLARE @DatabaseUserName [sysname]
SET @DatabaseUserName = 'put your user here'

SET NOCOUNT ON
DECLARE
@errStatement [varchar](8000),
@msgStatement [varchar](8000),
@DatabaseUserID [smallint],
@ServerUserName [sysname],
@RoleName [varchar](8000),
@ObjectID [int],
@ObjectName [varchar](261)

SELECT
@DatabaseUserID = [sysusers].[uid],
@ServerUserName = [master].[dbo].[syslogins].[loginname]
FROM [dbo].[sysusers]
INNER JOIN [master].[dbo].[syslogins]
ON [sysusers].[sid] = [master].[dbo].[syslogins].[sid]
WHERE [sysusers].[name] = @DatabaseUserName
IF @DatabaseUserID IS NULL
BEGIN
SET @errStatement = 'User ' + @DatabaseUserName + ' does not exist in ' + DB_NAME() + CHAR(13) +
'Please provide the name of a current user in ' + DB_NAME() + ' you wish to script.'
RAISERROR(@errStatement, 16, 1)
END
ELSE
BEGIN
SET @msgStatement = '--Security creation script for user ' + @ServerUserName + CHAR(13) +
'--Created At: ' + CONVERT(varchar, GETDATE(), 112) + REPLACE(CONVERT(varchar, GETDATE(), 108), ':', '') + CHAR(13) +
'--Created By: ' + SUSER_NAME() + CHAR(13) +
'--Add User To Database' + CHAR(13) +
'USE [' + DB_NAME() + ']' + CHAR(13) +
'EXEC [sp_grantdbaccess]' + CHAR(13) +
CHAR(9) + '@loginame = ''' + @ServerUserName + ''',' + CHAR(13) +
CHAR(9) + '@name_in_db = ''' + @DatabaseUserName + '''' + CHAR(13) +
'GO' + CHAR(13) +
'--Add User To Roles'
PRINT @msgStatement
DECLARE _sysusers
CURSOR
LOCAL
FORWARD_ONLY
READ_ONLY
FOR
SELECT
[name]
FROM [dbo].[sysusers]
WHERE
[uid] IN
(
SELECT
[groupuid]
FROM [dbo].[sysmembers]
WHERE [memberuid] = @DatabaseUserID
)
OPEN _sysusers
FETCH
NEXT
FROM _sysusers
INTO @RoleName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @msgStatement = 'EXEC [sp_addrolemember]' + CHAR(13) +
CHAR(9) + '@rolename = ''' + @RoleName + ''',' + CHAR(13) +
CHAR(9) + '@membername = ''' + @DatabaseUserName + ''''
PRINT @msgStatement
FETCH
NEXT
FROM _sysusers
INTO @RoleName
END
SET @msgStatement = 'GO' + CHAR(13) +
'--Set Object Specific Permissions'
PRINT @msgStatement
DECLARE _sysobjects
CURSOR
LOCAL
FORWARD_ONLY
READ_ONLY
FOR
SELECT
DISTINCT([sysobjects].[id]),
'[' + USER_NAME([sysobjects].[uid]) + '].[' + [sysobjects].[name] + ']'
FROM [dbo].[sysprotects]
INNER JOIN [dbo].[sysobjects]
ON [sysprotects].[id] = [sysobjects].[id]
WHERE [sysprotects].[uid] = @DatabaseUserID
OPEN _sysobjects
FETCH
NEXT
FROM _sysobjects
INTO
@ObjectID,
@ObjectName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @msgStatement = ''
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 193 AND [protecttype] = 205)
SET @msgStatement = @msgStatement + 'SELECT,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 195 AND [protecttype] = 205)
SET @msgStatement = @msgStatement + 'INSERT,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 197 AND [protecttype] = 205)
SET @msgStatement = @msgStatement + 'UPDATE,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 196 AND [protecttype] = 205)
SET @msgStatement = @msgStatement + 'DELETE,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 224 AND [protecttype] = 205)
SET @msgStatement = @msgStatement + 'EXECUTE,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 26 AND [protecttype] = 205)
SET @msgStatement = @msgStatement + 'REFERENCES,'
IF LEN(@msgStatement) > 0
BEGIN
IF RIGHT(@msgStatement, 1) = ','
SET @msgStatement = LEFT(@msgStatement, LEN(@msgStatement) - 1)
SET @msgStatement = 'GRANT' + CHAR(13) +
CHAR(9) + @msgStatement + CHAR(13) +
CHAR(9) + 'ON ' + @ObjectName + CHAR(13) +
CHAR(9) + 'TO ' + @DatabaseUserName
PRINT @msgStatement
END
SET @msgStatement = ''
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 193 AND [protecttype] = 206)
SET @msgStatement = @msgStatement + 'SELECT,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 195 AND [protecttype] = 206)
SET @msgStatement = @msgStatement + 'INSERT,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 197 AND [protecttype] = 206)
SET @msgStatement = @msgStatement + 'UPDATE,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 196 AND [protecttype] = 206)
SET @msgStatement = @msgStatement + 'DELETE,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 224 AND [protecttype] = 206)
SET @msgStatement = @msgStatement + 'EXECUTE,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 26 AND [protecttype] = 206)
SET @msgStatement = @msgStatement + 'REFERENCES,'
IF LEN(@msgStatement) > 0
BEGIN
IF RIGHT(@msgStatement, 1) = ','
SET @msgStatement = LEFT(@msgStatement, LEN(@msgStatement) - 1)
SET @msgStatement = 'DENY' + CHAR(13) +
CHAR(9) + @msgStatement + CHAR(13) +
CHAR(9) + 'ON ' + @ObjectName + CHAR(13) +
CHAR(9) + 'TO ' + @DatabaseUserName
PRINT @msgStatement
END
FETCH
NEXT
FROM _sysobjects
INTO
@ObjectID,
@ObjectName
END
CLOSE _sysobjects
DEALLOCATE _sysobjects
PRINT 'GO'
END

- Paul
- Database performance looks fine, it must be the Network!

 

[mrdenny]

Wow, that's a long script. This is a little shorter.

select 
 user_name(p.grantor) as GRANTOR
 ,user_name(p.uid)  as GRANTEE
 ,db_name()    as TABLE_CATALOG
 ,user_name(o.uid)  as TABLE_SCHEMA
 ,o.name     as TABLE_NAME
 ,case p.action  
  when 26  then 'REFERENCES'
  when 193 then 'SELECT'
  when 195 then 'INSERT'
  when 196 then 'DELETE'
  when 197 then 'UPDATE'
  when 224 then 'EXECUTE'
	else 'test'
 end      as PRIVILEGE_TYPE,
	o.xtype
 ,case 
  when p.protecttype = 205 then 'NO'
  else 'YES'
 end      as IS_GRANTABLE,
	'grant ' + case p.action  
  when 26  then 'REFERENCES'
  when 193 then 'SELECT'
  when 195 then 'INSERT'
  when 196 then 'DELETE'
  when 197 then 'UPDATE'
  when 224 then 'EXECUTE'
	else 'test'
 end + ' on [' + user_name(o.uid) + '].[' + o.name + '] to [' + user_name(p.uid) + ']'
 from 
 sysprotects p, 
 sysobjects o
where  
  (p.protecttype = 204 or  /*grant exists without same grant with grant */
 (p.protecttype = 205
  and not exists(select * from sysprotects p2
    where p2.id = p.id and
    p2.uid = p.uid and 
    p2.action = p.action and 
    p2.columns = p.columns and
    p2.grantor = p.grantor and
    p2.protecttype = 204)))
  and p.action in (26,193,195,196,197,224)
  and p.id = o.id
-- and o.xtype in ('U', 'V', 'P')
	--and xtype = 'P'
  and 0 != (permissions(o.id) --&
	
order by --PRIVILEGE_TYPE

table_name

When you do a restore all the permissions would have come accross. The exception to that would be if the user is a member of a fixed server role in the source server, but not in the destination server.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[jsteph]

Thanks, I tried that, and although the sp said it completed, and listed a call to sp_addrolemember, it did not seem to solve the problem, I'll have to look into it further. All I know is that the user in question had all rights to everything, and after doing the sp_change_users_login and the above code, the user still gets 'access denied' errors on most objects.

Am I looking at this wrong? I mean, this is a bug, right? Or is this the way it's supposed to be? Shouldn't a user's rights persist through a restore? Ironically, I'd feel better knowing it's a bug, because if this is by design then I really have to question the security model--or at least my understanding of it. Can you confirm the status on this issue?
--Jim

 

[ptheriault]

Denny,
I like that script. I got mine from SSWUG. But I'm going to copy your...it is shorter.. : )

- Paul
- Database performance looks fine, it must be the Network!

 

[jsteph]

Mrdenny,
I had posted apparently while yours was posting and didn't see that, so I'm giving that script a try...

 

[mrdenny]

A restore should preserve the permissions. As long as the accounts are synced up correctly. Try running my script on the destination server and see what rights it shows the user as having.

Also make sure that you are in the correct database when running the sp_change_users_logins procedure.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[jsteph]

It's working now, thanks both of you. I think that I had jostled things around trying all sorts of different fixes, and I went back and started from scratch--doing a new restore, then did the change_logins thing, then the set permissions procedure, and it appears I have access now,
--Jim