Thursday, March 8, 2012

DB Owner Problem after restore on a new PC

I am using SQL2000. I took a backup of the database on the live server to setup the database on my new development computer. When I restored the data base it created 2 sets of tables for each table. One set owned by dbo and the other set by indiankarma. So I have a table that is indiankarma.ikuserinfo and a table right below it that is dbo.ikuserinfo. All the site data is in the indiankarma owned tables. (see below 1.1)1.1
The problem is that the 'indiankarma' database user has no login name. (See below 1.2).1.21.2

Also, the security login 'indiankarma' does not have access to the database (see below 1.3) and when I try to give it access, I get this error 'User 'Indiankarma' already exists.' (see below 1.4)1.3
1.31.4
1.4I need to know what I can do to get database user 'indiankara' a login name and how to permit security login 'indiankarma' database access with the user 'indiankarma'. So it looks like the figure below (see below 1.5) but when I click ok, it does not give me the error. I have tried sp_changedbowner 'indiankarma' which does give the security login name indiankarma database access but it makes the user dbo not indiankarma. Please help!!! Sorry for all the images, I am bad at explaining problems thought they might help!1.51.5

There are two permissions in SQL Server one for the server under security in the management section of Enterprise Manager and the database permission, you may have created the database permissions and not the server login. If that is not your problem then you have a problem which is a known issue with restore called orphaned permissions where the permission is in the Master but not the database you created it for, solution delete the restore and start fresh. Hope this helps.|||

FYI, here is a stored procedure to check orphaned users:

CREATE PROC dbo.ShowOrphanUsers
AS
BEGIN
CREATE TABLE #Results
(
[Database Name] sysname COLLATE Latin1_General_CI_AS,
[Orphaned User] sysname COLLATE Latin1_General_CI_AS
)

SET NOCOUNT ON

DECLARE @.DBName sysname, @.Qry nvarchar(4000)

SET @.Qry = ''
SET @.DBName = ''

WHILE @.DBName IS NOT NULL
BEGIN
SET @.DBName =
(
SELECT MIN(name)
FROM master..sysdatabases
WHERE name NOT IN
(
'master', 'model', 'tempdb', 'msdb',
'distribution', 'pubs', 'northwind'
)
AND DATABASEPROPERTY(name, 'IsOffline') = 0
AND DATABASEPROPERTY(name, 'IsSuspect') = 0
AND name > @.DBName
)

IF @.DBName IS NULL BREAK

SET @.Qry = ' SELECT ''' + @.DBName + ''' AS [Database Name],
CAST(name AS sysname) COLLATE Latin1_General_CI_AS AS [Orphaned User]
FROM ' + QUOTENAME(@.DBName) + '..sysusers su
WHERE su.islogin = 1
AND su.name <> ''guest''
AND NOT EXISTS
(
SELECT 1
FROM master..sysxlogins sl
WHERE su.sid = sl.sid
)'

INSERT INTO #Results EXEC (@.Qry)
END

SELECT *
FROM #Results
ORDER BY [Database Name], [Orphaned User]
END

|||Or you could look inside the Master verifiy the account and edit it. But it is faster to rerestore I have done both.

No comments:

Post a Comment