Why change user type to SQL user with login is disabled in SSMS?

Question

I backed up a database from a SQL Server 2008 and restored it to my local machine using SQL Server 2012, now I'm trying to login to the server with the copied database user account and I wasn't able to do so.

After googling the issue I found that I have to change the user type from SQL User Without Login to SQL User with Login but the drop-down list is disabled as you can see in the picture below, how can I fix this and is this is the best way of doing what I need to accomplish or do I need to add this user to the server level?

enter image description here

This is what is sometimes referred to as an "orphaned user". This is what I usally do to re-associate the db user with a SQL Server instance login: `USE DatabaseName ALTER USER UserName WITH LOGIN = LoginName` — Dave Mason, Aug 14 '14 at 14:57
You would need to log in to the SQL server where you originally created the db backup. (The SQL 2008 Server, right?) In SSMS, go to the database, find the db user, and open its properties window. It should show you the "login" the db user is linked to. If you don't have the same login on your SQL 2012 instance, you can script it out from the SQL 2008 instance, and create it in the SQL 2012 instance. — Dave Mason, Aug 14 '14 at 15:07
@Dave Mason's comment should be an answer and the accepted one! — F3L1X79, Jun 04 '21 at 09:32

score 5 · Accepted Answer · answered Aug 14 '14 at 17:20

5

I remember running into this before when doing backup / restore across servers. Basically it comes down to how SQL Server works. There's SQL Server users & there's database users (SQL Server users who are database users are represented via mappings). They are however not the same thing.

A SQL Server user belongs to the SQL Server, a database user ONLY belongs to the associated database. What happens when you have a database user, but not a SQL Server user? You can't login to SQL Server non-obviously.

Thereby what I do is after moving the database, I add the user I need to login as to SQL Server users using SSMS, remove the old database user (it's got dependencies associated w it that prevent mapping to it) & lastly make a new user on the database by mapping my SQL user to the database w appropriate permissions.

This approach is by no means elegant, but it works 100% of the time w no code needed, & you should consider a more permanent system if you have automated backup / restores happening. For the one off, this is how I've always done it.

answered Aug 14 '14 at 17:20

RandomUs1r

4,010
1
24
44

I would advise against dropping the db user and creating a new one. Unless you have a way to duplicate the old user's permissions to the new user. Re-associating the old db user to an existing SQL Server instance login seems much more logical to me. Besides, it's really simple. – Dave Mason Aug 14 '14 at 17:32
@DMason I'm 99% sure it doesn't work to map a "new" SQL user to an "existing" db user. Each user has a unique identifier associated with them which have to match. It's been a while since I've had to do it, I'd just try mapping new to existing & if it errors, delete the db user and map again. – RandomUs1r Aug 14 '14 at 18:18
1

Before we continue down this rabbit hole further, let's clear up some terminology first. A SQL Server instance has "logins". A database has "users". Can we agree on that? – Dave Mason Aug 14 '14 at 18:24
Ehhh... both are users, consider the scenario where you have a SQL server login that can't touch any data simply to administrate the SQL server as a non-trusted dba. In fact, database "users" would be the deceptively named one, it's more like I'm a windows user & I have permission to the files & folders on my computer (databases & data). In SQL Server those permissions are represented by database "users". Make sense? As far as our discussion goes I'm not disagreeing w you, I just remember mapping to existing db users not working, but don't have a SQL server that I can test this on handy – RandomUs1r Aug 14 '14 at 18:35
1

It sounds like you understand the distinction between a SQL login and a database user, as I do. To refer to them both collectively as "users" does a disservice to those who don't understand the difference between the two. – Dave Mason Aug 14 '14 at 18:41
Ultimately you are correct: http://stackoverflow.com/questions/1134319/difference-between-a-user-and-a-login-in-sql-server, though my example should at least make you consider the lack of logic in that naming convention, especially once you start considering "SA" a login (what type of user is it based on?). We're way off topic though & terminology does little for functionality, so OP should be able to follow either method for mapping, but will definitely need to create a SQL "login" :) – RandomUs1r Aug 14 '14 at 18:47
Will a new SQL login need to be created? That depends! I can think of a few scenarios where a new login isn't needed. 1) There may be an existing login that is not associated with any users on the restored database, which would be appropriate to associate with a user on the restored db. If that's not the case, then I agree--a new login would be needed. But you would only need to do this once. Which leads to the next scenario... 2) Say the OP backs up the db a 2nd time and restores to the 2nd server. The login is already there from the first restore. – Dave Mason Aug 14 '14 at 19:04
2

But if I can bring this full circle...getting back to my very first comment to this answer, I would not drop and recreate the database user. Leave it intact and associate it with a new or existing login. – Dave Mason Aug 14 '14 at 19:06
1

Have to agree with @DMason on this, associating the user with a login is *far* safer than removing. I just ran into the same situation and it was a lot easier to create a new login & associate the existing users with that rather than having to replicate all the permissions (our users have fine-grained control to each DB). – James Jun 16 '15 at 11:57

Why change user type to SQL user with login is disabled in SSMS?

1 Answers1