Stored procedure to loop through db items change name and ownership

Thanks!

Here is what I get: Incorrect syntax near the word for, incorrect syntax near mycursor


ALTER PROCEDURE granting_all
as

declare @sql_string nvarchar(4000)

declare @mycursor cursor for
select name from sysobjects
where xtype = 'U'
and name like 'tbl_my_table%'

DECLARE @name varchar(40)
OPEN mycursor

FETCH NEXT FROM mycursor INTO @name
WHILE (@@fetch_status <> -1)
BEGIN
    IF (@@fetch_status <> -2)
    BEGIN
        set @sql_string = 'sp_changeobjectowner @objname = ''' + @name + ''', @newowner = ''dbo'''
        exec sp_executesql @sql_string
        set @sql_string = 'Grant  update,select,insert On ' + @name + ' To web_serv'
        exec sp_executesql @sql_string
    END
    FETCH NEXT FROM mycursor INTO @name
END

CLOSE mycursor
DEALLOCATE mycursor
GO

the line:

"declare @mycursor cursor for"

should just read:

"declare mycursor cursor for"

Cheers,
Hillwaaa

I actually added that in because I thought it was causing the original error:

Incorrect syntax near 'mycursor'

Of course, I don't know which mycursor is causing the error!

Hey jtreher, try:



ALTER PROCEDURE granting_all
as


declare @sql_string nvarchar(4000)

declare mycursor cursor for
select name from sysobjects
where xtype = 'U'
and name like 'tbl_my_table%'

DECLARE @name varchar(40)
OPEN mycursor

FETCH NEXT FROM mycursor INTO @name
WHILE (@@fetch_status <> -1)
BEGIN
    IF (@@fetch_status <> -2)
    BEGIN
        set @sql_string = 'sp_changeobjectowner @objname = ''' + @name + ''', @newowner = ''dbo'''
        exec sp_executesql @sql_string
        set @sql_string = 'Grant  update,select,insert On ' + @name + ' To web_serv'
        exec sp_executesql @sql_string
    END
    FETCH NEXT FROM mycursor INTO @name
END

CLOSE mycursor
DEALLOCATE mycursor
GO

hi Hillwaaa

Same thing, incorrect syntax near 'mycursor'

ALTER PROCEDURE granting_all
as


declare @sql_string nvarchar(4000)

declare mycursor cursor for
select name from sysobjects
where xtype = 'U'
and name like 'tbl_my_table%'

DECLARE @name varchar(40)
OPEN mycursor

FETCH NEXT FROM mycursor INTO @name
WHILE (@@fetch_status <> -1)
BEGIN
    IF (@@fetch_status <> -2)
    BEGIN
        set @sql_string = 'sp_changeobjectowner @objname = ''' + @name + ''', @newowner = ''dbo'''
        exec sp_executesql @sql_string
        set @sql_string = 'Grant  update,select,insert On ' + @name + ' To web_serv'
        exec sp_executesql @sql_string
    END
    FETCH NEXT FROM mycursor INTO @name
END

CLOSE mycursor
DEALLOCATE mycursor
GO

Hmm are you running this on sql 2000 from query analyzer?  (sp creates and runs fine for me)

Hi there,

I have 2 options for you, without using any cursor:

1) If you just need to change the owenership of tale objects ONLY, then something like the following should do the trick:
exec sp_msforeachtable 'exec sp_changeobjectowner ?, ''dbo'''

2) If you want to change the object owner for every object on thhe database, I have the following stored procedure:
*********************************************************************
CREATE proc [dbo].[chObjOwner]( @usrName varchar(20), @newUsrName varchar(50))
as
-- @usrName is the current user
-- @newUsrName is the new user

set nocount on
declare @uid int                   -- UID of the user
declare @objName varchar(50)       -- Object name owned by user
declare @currObjName varchar(50)   -- Checks for existing object owned by new user
declare @outStr varchar(256)       -- SQL command with 'sp_changeobjectowner'
set @uid = user_id(@usrName)

declare chObjOwnerCur cursor static
for
select name from sysobjects where uid = @uid

open chObjOwnerCur
if @@cursor_rows = 0
begin
  print 'Error: No objects owned by ' + @usrName
  close chObjOwnerCur
  deallocate chObjOwnerCur
  return 1
end

fetch next from chObjOwnerCur into @objName

while @@fetch_status = 0
begin
  set @currObjName = @newUsrName + "." + @objName
  if (object_id(@currObjName) > 0)
    print 'WARNING *** ' + @currObjName + ' already exists ***'
  set @outStr = "sp_changeobjectowner '" + @usrName + "." + @objName + "','" + @newUsrName + "'"
  print @outStr
  print 'go'
  fetch next from chObjOwnerCur into @objName
end

close chObjOwnerCur
deallocate chObjOwnerCur
set nocount off
return 0
*********************************************************************
Once that procedure is created, execute it as follows:
exec chobjowner 'oldusername', 'newusername'

That will generate the scripts in the output window.
Copy and paste that into Query analyzer, and execute it.
You will get some warnings, as it will try to change the ownership of things like Primary Keys, but it is safe to ignare those.


hope that helps!

David  :o)

Hilwaa

I'm actually using MS Access to view an ADP. Should have stated that earlier.

Cheers, Justin

Yes, that would've been useful!

Perhaps you should ask to have this moved into the MS Access area, by posting a question in Community Support.

I don't think sp_changeobjectowner exists in access though (does it even have stored procedures?) so I'm not sure when you say "What I normally run is this stored procedure..." if that's actually Access or SQL Server.... that was certainly what threw me!

Anyway, good luck with your problem.

Best Regards,

David

Hey Justin - I've never used an ADP against sql server...

If you can, use Query Analyzer to alter the stored procedure - then you should be able to call it from your ADP.  I'm assuming you have login details that you use for your ADP, which should work for Query Analyzer as well.

Alternatively, post a query to the MS Access area asking them how to convert the systax so that access will not throw up an error before it is run in the SQL server database.

Cheers,
Hillwaaa

Ah.. an ADP...I missed that part.

In that case, you should be able to use Query Analyzer against thhe SQL database in order to rename the objects... have you tried the solution I suggested?

Hi guys, after a bit of roughing it, we got sql server client tools installed on my machine and I'm learning how to use it.

MS Access does allow you to create stored procedures in ADPs, it's just that it seems to be messing up quite a bit.

The query runs with no errors in query analyzer, but it doesn't appear to have done anything as my table ownership is the same.

DoobDave, I cannot get yours to work properly I get errors. Please see that I changed the userName to be hard coded so I could run in SQL Analyzer:


CREATE proc chObjOwner
as

set nocount on
declare @usrName varchar(20)
declare @newUsrName varchar(50)
declare @uid int                   -- UID of the user
declare @objName varchar(50)       -- Object name owned by user
declare @currObjName varchar(50)   -- Checks for existing object owned by new user
declare @outStr varchar(256)       -- SQL command with 'sp_changeobjectowner'
set @usrName = 'firstname.lastname'
set @newUsrName = 'dbo'
set @uid = user_id(@usrName)


declare chObjOwnerCur cursor static
for
select name from sysobjects where uid = @uid

open chObjOwnerCur
if @@cursor_rows = 0
begin
  print 'Error: No objects owned by ' + @usrName
  close chObjOwnerCur
  deallocate chObjOwnerCur
  return 1
end

fetch next from chObjOwnerCur into @objName

while @@fetch_status = 0
begin
  set @currObjName = @newUsrName + "." + @objName --(line 33)
  if (object_id(@currObjName) > 0)
    print 'WARNING *** ' + @currObjName + ' already exists ***'
  set @outStr = "sp_changeobjectowner '" + @usrName + "." + @objName + "','" + @newUsrName + "'"
  print @outStr
  print 'go'
  fetch next from chObjOwnerCur into @objName
end

close chObjOwnerCur
deallocate chObjOwnerCur
set nocount off
return 0


------------------
Server: Msg 207, Level 16, State 3, Procedure chObjOwner, Line 33
Invalid column name '.'.
Server: Msg 207, Level 16, State 1, Procedure chObjOwner, Line 36
Invalid column name 'sp_changeobjectowner ''.
Server: Msg 207, Level 16, State 1, Procedure chObjOwner, Line 36
Invalid column name '.'.
Server: Msg 207, Level 16, State 1, Procedure chObjOwner, Line 36
Invalid column name '',''.
Server: Msg 207, Level 16, State 1, Procedure chObjOwner, Line 36
Invalid column name '''.

Awesome!

Hillwaa, I changed tbl_my_table in the set code to tbl (the prefix for my tables) and it worked! Thanks!

Now I just have to see if it is possible to export my tables without getting the dbo. put in front of them.

One question though, I think it would be useful if it would check if the object already has the ownership of dbo as that throws an error.