November 20, 2013 at 1:43 pm
I'm unable to create user for database.
USE [AfregnDB]
GO
CREATE USER [SW] FOR LOGIN [SW]
GO
I get this error:
Msg 15022, Level 16, State 1, Line 1
The specified user name is already aliased.
If I select from dbo.sysusers - the user does not exist
select * from dbo.sysusers
where [name] = 'sw'
However if I run this:
EXEC sp_helplogins @LoginNamePattern='SW'
I get result for the login and the user - It does however look wrong as i has a backslash i front of the name:
LoginName SID DefDBName DefLangName AUser ARemote
--------- ------------------------------------- ------------ -------------- ----- -------
SW 0x296CEBB34B0C5C43AD603AABE607F77F master us_english yes no
(1 row(s) affected)
LoginName DBName UserName UserOrAlias
--------- ---------------- -------- -----------
SW AfregnDB \SW User
(1 row(s) affected)
I have tried many different thing to find a solution, but nothing helps:
Droped and recreated the login
Run sp_change_users_login 'report' - shows nothing
Tried using sp_dropuser 'SW' - Msg 15008 User 'SW' does not exist in the current database
Tried EXEC sp_dropuser '\SW' - Msg 15151 Cannot drop the user '\SW', because it does not exist or you do not have permission. (I'm sysadmin)
Anyone - any ideas?
Regards Rasser
November 20, 2013 at 1:59 pm
I just found this:
use afregndb
go
select * from dbo.sysusers
where [name] = '\SW'
Returns a result - however it's not aliased.
If I try to create a login with the same SID I get this:
Use master
go
CREATE LOGIN \SW WITH password = 'Pasword!1', sid = 0x296CEBB34B0C5C43AD603AABE607F77F;
GO
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '\'.
November 20, 2013 at 2:53 pm
the issue here is an orphaned user: that is, you have a SQL login [sw] , but you restored the DATABASE from another server....that local.sw <> restoreddb.sw , based on the sids stored in sys.server_principals and the databases database_principals.
the database user sw DOES exist, but it's the"wrong" sw;
it's like my "bob" in my office is not the same "bob" in your office.
the easy fix is this command to fix the orphan:
ALTER USER swWITH LOGIN = sw;
Lowell
November 24, 2013 at 1:35 pm
Actually, the issue is that the login is already mapped to a different user. In this case, the user is called "\SW".
Drop the database user \SW and then you can create the new user mapped to the login.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply