Sql-server – SQL Server 2012 – login mapping to user

sql-server-2012

I'm still struggling a bit in moving from Oracle to SQL Server, especially with the login-vs-user thing. I've restored a database from a client site so we can have a development copy. I want to set up a login with that new database as the default db, and map it to a user of that database. But everything I've tried results in a "user or role already exists" error. I'd like for this login to only have access to this one particular database. I've read about users and logins, but I'm obviously missing something here. Can the login have the same name as the database user? What I've read seems to imply it can, but I get an error.

Harry

Best Answer

It is pretty simple, you just do the following two steps

1st: in sql server, create a new login using the user name in that database, like

use master;

create login [MyUserName] with password='mypassowrd123$', check_policy=off;
go

2nd: map the login with the user name inside that particular database, let's call it [xyz]

use [xyz]
alter user [MyUserName] with login = [MyUserName];
go
Related Question