Solved

Change Login/User in MS SQL

Posted on 2014-04-04
5
1,203 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
  • 2
  • 2
5 Comments
 
LVL 39

Expert Comment

by:lcohan
Comment Utility
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
Comment Utility
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 39

Accepted Solution

by:
lcohan earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

762 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now