April 28, 2004 at 10:49 am
I'm cleaning up SQLserver accounts (SQL2K SP3). I've created a group 'TdatabaseWriters' and moved individual user accounts into the group. Now I want to delete the individual user accounts but the accounts own objects in the T database so it won't allow account deletion even though the account still has the same privs via the group. Any suggestions? A way to change ownership of the objects away from the user and then back to the user after I delete the account?
Thx,
Al
April 28, 2004 at 6:57 pm
Al,
You cannot drop a user that owns an object. You will need to change the object owner to another user, delete the first user, then change the owner again.
This is a good reason to have 'sa' as the owner of all objects.
To find out who owns what objects try running :
SELECT sysobjects.[name], sysusers.[name]
FROM sysobjects
INNER JOIN sysusers ON sysobjects.uid = sysusers.uid
Cheers,
Angela
April 29, 2004 at 12:35 am
You will have to alter the object-owner !
this may help out :
Select 'print '' object : ' + u.name + '.' + o.name + ''' ' + char(13) + ' go '+ char(13)
+ 'Sp_changeobjectowner ''' + u.name + '.' + o.name + ' '', ''NewSchema'' ' + char(13) + ' go '
from sysobjects o
inner join sysusers u
on (o.uid = u.uid)
where o.xtype in ('p', 'u', 'v') -- p = procedures, u = usertables , v = userviews
and u.name like 'oldschema%'
and o.name not in ('dtproperties')
order by u.name, o.name
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply