is there a command that can change a login role to db_owner in all the
tables, or do i have to use
{
USE table_name
EXEC sp_adduser 'login name'
EXEC sp_addrolemember 'db_owner', 'login name'
}
for each of the tables ?

thanks

Hi

Rather than adding the login to the db_owner role, why not changed the
object ownership using sp_changeobjectowner?

e.g. to get a list of commands

DECLARE @username sysname
SET @username = 'ABC'
select 'EXEC sp_changeobjectowner ''' + u.name + '.' + o.name + ''',
''dbo''' from sysobjects o
JOIN sysusers u on o.uid = u.uid
where u.name = @username
and o.type = 'U'

You could change this to a cursor and run each statement with EXEC. You will
need to watch out of dependencies and also make sure the correct owner
prefix is used wherever it is referenced.

If you want to change the owner or the database use sp_changedbowner.

The USE statement is for databases not tables.

John

<liorhal (AT) gmail (DOT) com> wrote