Sync NT Login ID change 1

[DrSql]

My SQL Server is using Windows Authntication and have around 30 Db's on this server. Recently the network team decide to change few NT Ids, is there any way to sync to my new Id rather than me changing manually.
If drop the old one and add new one I will lose the the prior security set on database. Any suggestions wouuld be great.

Example.
Domain/JDoe ==>Domain/JDoe02


Dr. Sql
vpathisseril@yahoo.com
Good Luck.

 

[Catadmin]

Look up "logins-SQL Server, changing passwords and user accounts" in Books Online. It tells you exactly how to do this.

I don't know if it keeps the permissions, though. Just remember you can use the sp_GrantLogin, sp_addsrvrolemember, and sp_addrolemember procs to help you out. And, if nothing else, you could create new server roles and new DB roles with the appropriate permissions and then assign those new NT accounts to the proper custom roles.





Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[mrdenny]

This is why NT Groups and SQL Roles should be used.

If the Usernames have been changed you'll have to remove the old logins, and grant the new logins access to the database.

I'd recommend creating roles in the SQL database than adding the NT accounts to the roles. Or better yet create a group on the domain then add the NT accounts to that group, and grant that group rights to the SQL Server, and put that group into the role. This way is the logins are changed again they will continue to have the same access as with the old username.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[SQLBill]

CatAdmins suggestion won't work. That is for SERVICES not for actual users. Microsoft's wording of the title is poorly chosen.

You can't change a user's login and must create a new one.

-SQLBill

Posting advice: FAQ481-4875

 

[DrSql]

Thank you all for the suggestions..
But Now what can I do to go forward.
Is it possible to extract an users permissons on differnt database, then I can modify that script to grant back the same permisson. Some how I want to get the user permissions before I drop the old ID, else it will be hard task. There are multiple users access mutliple database with different access previllages. I dont think I would have enough time to go down to all the databases manully verfiy the permissions. Any alternate suggestions than moving them to groups at this stage.



Dr. Sql
vpathisseril@yahoo.com
Good Luck.

 

[mrdenny]

You can use the sysprotects table to find this info out.

This code will generate the script needed to regrant the permissions. Once the new script is generated just do a find and replace to change the username.

This code only handles select, insert, update, delete and exec (for procs, functions, etc). Any create permissions aren't covered but can be added.

select 
	case when protecttype = 204 then
		'Grant with Grant'
	when protecttype =205 then
		'Grant'
	when protecttype = 206 then
		'Revoke'
	end + ' ' + 
	case when action = 193 then
		'select'
	when action = 195 then
		'insert'
	when action = 196 then
		'delete'
	when action = 197 then
		'update'
	when action = 224 then	
		'exec'
	end + ' on ' + sysobjects.name + ' to ' + sysusers.name as 'Command'
from sysprotects
join sysusers on sysprotects.uid = sysusers.uid
join sysobjects on sysprotects.id = sysobjects.id

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[mrdenny]

You'll have to excuse us all. Every once and a while we forgot that after the tirade we need to actually put something helpful in.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[DrSql]

Denny, Thank you for the suggestion,
As by your suggestion I was able to run and obtain the output script, but it shows only the groups i want the output on user level.
Currently I was able to get invalid logins by EXEC sp_validatelogins. So I can take the output and run towars your script where sysusers.name=output of (EXEC sp_validatelogins).


Dr. Sql
vpathisseril@yahoo.com
Good Luck.