Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

I'm baffled - Msg 15022 The specified user name is already aliased Expand / Collapse
Author
Message
Posted Wednesday, November 20, 2013 1:43 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 2:38 AM
Points: 8, Visits: 90
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
Post #1516188
Posted Wednesday, November 20, 2013 1:59 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 2:38 AM
Points: 8, Visits: 90
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 '\'.

Post #1516195
Posted Wednesday, November 20, 2013 2:53 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 7:44 AM
Points: 12,910, Visits: 32,015
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1516229
Posted Sunday, November 24, 2013 1:35 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 12:57 PM
Points: 1,616, Visits: 1,543
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.




My blog: SQL Soldier
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring
Microsoft Certified Master: SQL Server 2008
Principal DBA: Outerwall, Inc.
Also available for consulting: SQL DBA Master
Post #1517116
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse