August 28, 2008 at 1:56 pm
I have a sql 2005 db where I need to drop all the users(around 200) who own around 200 views. Hence I need to drop the 200 views first. Please suggest
August 28, 2008 at 2:13 pm
why not change all the view ownerships using sp_changeobjectowner,
once done then drop the users 😎
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 28, 2008 at 2:22 pm
The issue is I need to drop view belonging to users, except 3 users , say a,b and c. These users come to around 200.There are 200 views per user. So, I need to identify these views first. But I am a lil stuck on this.
August 28, 2008 at 2:29 pm
your original post above indicates you need to drop users who own views??
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 28, 2008 at 2:32 pm
Yes I need to, but not all the users. I need to keep 5 users , and remove the rest. To drop these users, I need to first drop the views. I can do it manually but that i tedious. I am basically searching for a n automated way
August 28, 2008 at 3:13 pm
OK, first execute this command (you have to edit it for your excluded user names)
Select U.Name as [Owner], U.TYPE_DESC as [OwnerType], S.Name as [SchemaName], O.*
From sys.objects o
Join sys.schemas S ON S.Schema_ID = O.schema_id
Join sys.database_principals u
ON Coalesce(o.principal_id, S.Principal_ID) = U.principal_id
Where o.TYPE_DESC = 'VIEW'
And u.TYPE_DESC <> 'DATABASE_ROLE'
AND u.Name NOT IN ('dbo', 'jim', 'george')
Check the output and make sure that it is listing the Views that you actually want to Drop. Once it is right, make the same edits to this command and execute it:
Declare @sql nvarchar(MAX)
Set @sql = ''
Select @sql = @sql + 'DROP VIEW ['+SchemaName+'].['+[name]+'];
'
From (Select U.Name as [Owner], U.TYPE_DESC as [OwnerType], S.Name as [SchemaName], O.*
From sys.objects o
Join sys.schemas S ON S.Schema_ID = O.schema_id
Join sys.database_principals u
ON Coalesce(o.principal_id, S.Principal_ID) = U.principal_id
Where o.TYPE_DESC = 'VIEW'
And u.TYPE_DESC <> 'DATABASE_ROLE'
) V
Where Owner NOT IN ('dbo', 'jim', 'george')
Print @sql
EXEC @sql
If you want you can Comment out the EXEC first until you are sure that it is right, and then uncomment it to actually Drop all of the listed Views.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 28, 2008 at 3:18 pm
Waseem Jaleel (8/28/2008)
Yes I need to, but not all the users. I need to keep 5 users , and remove the rest. To drop these users, I need to first drop the views. I can do it manually but that i tedious. I am basically searching for a n automated way
thats just it you dont need to drop the views, change the view ownerships to a user who you will retain and then drop the remaining users after that. The following script will change object ownerships for views. just add in the user ids you want to keep into the "where" clause
select 'exec sp_changeobjectowner '''+user_name(Uid)+'.'+name+''',''user1''' /*owner to assign to
from sysobjects
where user_name(uid) <> 'dbo' and user_name(uid) <> 'user1' and etc etc and xtype = 'V'
order by name
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 28, 2008 at 3:27 pm
I am not sure that I would want to retain the personal Views of expired or departed users. If they have production value they should not have been personally owned.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 28, 2008 at 3:33 pm
in an ideal world 😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 28, 2008 at 4:08 pm
Thank u all for ur help.. I have been able to accomplish what I needed to 🙂
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply