[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Change Login/User in MS SQL

Posted on 2014-04-04
5
Medium Priority
?
1,551 Views
Last Modified: 2014-04-08
Several of our SQL logins have been dropped.  When they were created, they were in domain 'xxxx'.  Those individuals are now in domain 'yyy'

I am going through manually and adding a new login for each one dropped (I had a list of all logins as well as which dbs they had access to, but had not yet generated a list of roles in each db they were assigned to.)

I'm wondering if there is some way I can generate a script in T-SQL that for each database, would drop the 'xxxx\oldusername' and then add 'yyy\newusername', assigning any previously held roles?

I hope this is relatively clear!
0
Comment
Question by:Eric Hays-Strom
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 40

Expert Comment

by:lcohan
ID: 39978461
Why not just RENAME existing user in each database - hopefully you dropped them only from SQL Server Security level not from Database Security and then I suggest use ALTER USER .... so you don't need to touch permissions, roles, ownership, etc...

"Renames a database user or changes its default schema."
http://technet.microsoft.com/en-us/library/ms176060(v=sql.100).aspx


You could easily write one line command to suite your needs then use MS_FOREACHDB to run that command passing user name as parameter.

http://weblogs.sqlteam.com/joew/archive/2008/08/27/60700.aspx


You could use the command below to find each orphan (old xxxx\username) logins in each database on that server that need to be renamed to yyyy\username:

EXEC sp_MSforeachdb
@command1='?.dbo.sp_change_users_login ''Report'''
0
 

Author Comment

by:Eric Hays-Strom
ID: 39978582
I think that would have been the way to go if the login had been renamed.  Since it was dropped, and the newer one added, I get the error:

Msg 15098, Level 16, State 1, Line 1
The name change cannot be performed because the SID of the new name does not match the old SID of the principal.
0
 
LVL 40

Accepted Solution

by:
lcohan earned 1500 total points
ID: 39978631
Do you have many users like that? It would be a pain if there are a lot and you could use the script from link below to get and save all their rights so you can add them back after they are recreated.

http://gallery.technet.microsoft.com/scriptcenter/Get-logins-databases-816f66b2


basically a scrip like below where you can add a WHERE username = 'xxx\username' clause:

SELECT Us.name AS username, Obj.name AS object, dp.permission_name AS permission
FROM sys.database_permissions dp
      JOIN sys.sysusers Us
ON dp.grantee_principal_id = Us.uid
      JOIN sys.sysobjects Obj
ON dp.major_id = Obj.id
WHERE Us.name =  'xxx\username'
ORDER BY 2,3;

To avoid such things in the future you should never have users permissions but a Database Role and grant all necessary permissions then ALL needed for a SQL user (NT or SQL login) to access any object in that database is to be ADDED/REMOVED from that role.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39979437
And the big question is why are you adding individual Windows users to SQL Server?  That has to be painful if that is more than a handful.  Why not just create a Windows Group for each role and add that to SQL Server.  You can then let your network department handle security by adding and subtracting users to those Windows Groups as people come and go.
0
 

Author Comment

by:Eric Hays-Strom
ID: 39986096
Hi everyone,

I apologize for not responding sooner.

The Windows Group you mention, Anthony, would be... and will be... the ideal way to go.  Left to me, that's how it would be done.  I am new with this company, and inherited what I inherited.  I need to fix this fast.

Because there is governmental bureaucracy involved, it's going to take a longer period of time to get sign off from all the agencies and contractors involved on a new Windows group than it did to fix this problem this time.

But we are planning to go the group route!

Thank you to everyone who weighed in on this!

Eric
0

Featured Post

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question