Delete Orphanes users from multiple databases- Challeging to change teh authorization to DBO

Question

Post reply

Delete Orphanes users from multiple databases- Challeging to change teh authorization to DBO

erishi

SSC Journeyman

Points: 97
More actions
October 21, 2015 at 2:14 pm

#301666

I have a requirement to delete all the orphans users for the databases. The issue I am having is with when database principal owns a schema in the DB, User cannt be dropped.
How do I transfer it to DBO in case I am looping multiple databases. This is what I got so far .
declare @is_read_only nvarchar (200)
Select @is_read_only = is_read_only from master.sys.databases where name='test' /* This should be a parameter value */
IF @IS_READ_ONLY= 0
BEGIN
Declare @sql as varchar (200)
Declare @DatabaseName varchar (200)
Declare @LoginID varchar (100)
Set @DatabaseName= 'test' /* This should be a parameter value */
Set @LoginID= 'test' /* This should be a parameter value */
Set @sql = 'Use ' + @DatabaseName + ' Drop User [' + @LoginID + ']'
Print(@sql)
--Exec (@sql)
End
ELSE
IF @IS_READ_ONLY=1
BEGIN
Set @DatabaseName= 'test' /* This should be a parameter value */
Set @LoginID= 'test' /* This should be a parameter value */
Set @sql =
'Use Master
Alter Database '
+ @DatabaseName + ' SET READ_WRITE WITH NO_WAIT' + ' ' +
'
Use [' + @DatabaseName + '] Drop User [' + @LoginID + ']' +
'
Use Master
Alter Database '
+ @DatabaseName + ' SET READ_ONLY WITH NO_WAIT'
Print(@sql)
--Exec (@sql)
END

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

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

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 1

For changing ownership, take a look at:

https://msdn.microsoft.com/en-us/library/ms173423.aspx

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

erishi SSC Journeyman Points: 97 More actions · Answer 2

I was looking for possible changes t hat I had to do like below. How do it do that if I have to put it in a script ?

SELECT name FROM sys.schemas WHERE principal_id = USER_ID('myUser')

ALTER AUTHORIZATION ON SCHEMA::SchemaName TO dbo

GO

DROP USER myUser

codykonior SSCommitted Points: 1591 More actions · Answer 3

That's how you'd do it. But your process is a bit off.

Any time someone asks you to do something like this the first thing you should be doing is creating a way to roll it back. Are you scripting out the logins, schema ownership, and permissions so that you can record it somewhere in case it breaks something down the line and has to be investigated or rolled back?

Don't do anything without it. It's not just "best practice" it also prevents resume generating events. (Yeah you could just restore backups of the databases but those don't get retained forever).

erishi SSC Journeyman Points: 97 More actions · Answer 4

Thanks Cody for your advice. The account that we will be deleting are the termed account only. The account that does not exsist in AD but that are still there on any of the databases. We are trying to address that one.

I was trying to see if I can get help with for script part to add the alter authorization to DBO in the way I was doing.

Thanks you once again. let me know your thoughts.