Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
vdavid70
Yak Posting Veteran
67 Posts |
 Posted - 2005-08-23 : 07:28:03
|
| I am trying to update a login (Sun)in a sql server 2000 database with dbo as the user.This database was restored from a backup taken from another server, but the same database. The source database also has a user called dbo with a login called sun. I have tried using the stored procedure EXEC sp_change_users_login 'Update_One', 'Nest', 'Nest' but no luck. It comes up with error sayingServer: Msg 15287, Level 16, State 1, Procedure sp_change_users_login, Line 38Terminating this procedure. 'dbo' is a forbidden value for the login name parameter in this procedure.What am i doing wrong please help |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2005-08-23 : 13:22:34
|
| Are you sure that error is going with that command? Because you don't have 'dbo' in the command you are showing.And I think you have some confusion over who 'dbo' is. dbo is a special identification referencing the database owner. Also, when someone logs in with sysadmin privileges, they are known from some perspectives as 'dbo'.Are you really having problems with user logins not matching up, or are you just trying to change the owner of the database? If the latter, then use sp_changedbowner.---------------------------EmeraldCityDomains.com |
 |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2005-08-23 : 14:39:41
|
try change the database owner to SA and re-running your sp_change_users_login again. I am wondering if your Nest user is already set as the dbo.sp_changedbowner 'sa' should do it.-ec |
|
|
|
vdavid70
Yak Posting Veteran
67 Posts |
Posted - 2005-08-25 : 07:38:04
|
i actually meant to write EXEC sp_change_users_login 'Update_One', 'dbo', 'Nest' quote:
Originally posted by AjarnMark
Are you sure that error is going with that command? Because you don't have 'dbo' in the command you are showing.And I think you have some confusion over who 'dbo' is. dbo is a special identification referencing the database owner. Also, when someone logs in with sysadmin privileges, they are known from some perspectives as 'dbo'.Are you really having problems with user logins not matching up, or are you just trying to change the owner of the database? If the latter, then use sp_changedbowner.---------------------------EmeraldCityDomains.com
|
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2005-08-25 : 12:18:44
|
| Like I said, 'dbo' is not a real login. dbo is a special username inside SQL Server. If you want Nest to appear as dbo, then use the sp_changedbowner 'Nest' command or assign Nest to the db_owner or sysadmin role (depending on how much freedom he/she is allowed).---------------------------EmeraldCityDomains.com |
|
|
|
kimirose
Starting Member
1 Post |
Posted - 2008-10-06 : 13:46:24
|
| Hi,sp_change_users_login does not allow modifications to dbo, INFORMATION_SCHEMA, or sys logins. Try adding this code to your sp_change_users_login proc:USE DBNAMEGO/* Script to fix orphans when moving db to another server. Note: Auto_fix option will create a SQL login id if necessary. Note: Update_one option will NOT create a SQL login. Note: Report option reports on sql login vs user login mismatches.*/SET NOCOUNT ONDECLARE @UserName nvarchar(128) ,@MissingUsers varchar(4000) ,@cmd varchar(1000)-- Re-sync user ids to master databaseSET @UserName = ''WHILE @UserName IS NOT NULLBEGIN IF @UserName IS NOT NULL BEGIN SELECT @UserName = min(a.name) FROM sysusers AS a WHERE a.name > @UserName and IsSQLRole = 0 AND name NOT IN('dbo','guest', 'INFORMATION_SCHEMA', 'sys') -- Add this check for domain accounts -- They do not need synced with the master database IF (SELECT CHARINDEX('\',@UserName) )= 0 BEGIN SET @cmd = 'sp_change_users_login ''update_one'', ''' + @UserName + ''', ''' + @UserName + '''' EXEC(@cmd) END |
|
|
|
|
|
|
|
|
sp_change_users_login