SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


I'm baffled - Msg 15022 The specified user name is already aliased


I'm baffled - Msg 15022 The specified user name is already aliased

Author
Message
Bjarne Rasmussen
Bjarne Rasmussen
SSC Rookie
SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)

Group: General Forum Members
Points: 44 Visits: 122
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
Bjarne Rasmussen
Bjarne Rasmussen
SSC Rookie
SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)

Group: General Forum Members
Points: 44 Visits: 122
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 '\'.


Lowell
Lowell
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28508 Visits: 39977
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

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Robert Davis
Robert Davis
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2808 Visits: 1623
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 MVP
Database Engineer at BlueMountain Capital Management
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search