Map login to users

Question

Post reply

Map login to users

LelloMellow

SSC Rookie

Points: 27
More actions
March 10, 2014 at 9:41 am

#290745

Hello, I am migrating a database TESTDB from SQL 2008R2 to a new server running SQL Server 2012.
Management has decided the current sql users should have "better" user names. So the login and username "BadUsername" on the old server should be called "GoodUserName". Goodusername should have the same permissions as Badusername.
I have now restored a backup from the old server to the new server.
I used the following script for creating the login:
CREATE LOGIN [GoodUserName] WITH PASSWORD=N'difficultpassword', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
Then I run the following script:
ALTER USER Badusername WITH LOGIN = GoodUserName, NAME = GoodUserName
But the results are not what I wanted. I now have two database users: Badusername and GoodUserName. I would have preferred if BadUserName was "replaced" by GoodUserName, but it won't be a problem if I have to delete badusername manually. Worser is that GoodUserName have NOT "inherited" any permissions from Badusername.
Is there an easy way to transfer permissions or do I need to loop through the permissions of badusername and apply those to badusername?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Robert Klimes SSCoach Points: 17865 More actions · Answer 1

what you did do should have worked. just tested and it worked find on my machine.

you can check the associated user and logins to see if the goodusername user is mapped to the correct login.

select a.name as loginname ,a.sid as loginsid,b.name as username,b.sid as usersid

from sys.server_principals a

inner join sys.database_principals b

on a.sid = b.sid

where b.name = 'GoodUserName'

or a.name = 'GoodUserName';

Bob
-----------------------------------------------------------------------------
How to post to get the best help[/url]

HanShi SSC-Dedicated Points: 33507 More actions · Answer 2

I would suggest to script out all users (and logins) with their permissions. You can find many script to do that on the internet and on this forum (http://www.sqlservercentral.com/search/?q=permissions&t=s&sort=relevance). Use a find/replace to change all the "badusername"'s to "goodusername"'s in the generated script. Run the script and remove all logins/users with "badusername"'s.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

webrunner SSC-Dedicated Points: 31870 More actions · Answer 3

I found this script useful:

http://weblogs.sqlteam.com/billg/archive/2010/07/08/Scripting-out-SQL-Server-Logins.aspx

I'm not sure if it will solve your exact problem in its given form, but I suggest that you review it for ideas. And of course as others have said, there are many similar scripts out there.

Good luck.

- webrunner

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 4

HanShi (3/10/2014)
I would suggest to script out all users (and logins) with their permissions. You can find many script to do that on the internet and on this forum (http://www.sqlservercentral.com/search/?q=permissions&t=s&sort=relevance). Use a find/replace to change all the "badusername"'s to "goodusername"'s in the generated script. Run the script and remove all logins/users with "badusername"'s.

I would prefer this method. Either you can drop badusername users or remap them to the goodusername logins.

--

SQLBuddy