Stored procedure to loop through db items change name and ownership

Whenever I create export tables from dev to production they are named tbl_my_table(firstname.lastname). They also don't have appropriate permissions for the web server. What I normally run is this stored procedure:

ALTER PROCEDURE dbo.granting
/*
      ()
*/
AS
EXEC('sp_changeobjectowner @objname = ''tbl_my_table'', @newowner = dbo')
Grant  update,select,insert On tbl_my_table To web_serv

      RETURN

/* Available permissions: execute,update,select,insert */

However, I have to manually put in the table name. How can I change this stored proc so that it loops through all objects and changes them if need be. I've tried a number of the scripts out there, but I can't seem to get them to work. I don't know if it is because the username has a period between it or not. I was working on this problem earlier, but was taken away for personal reasons, but I'm back!

I know when people ask these 'code for me' questions in coldfusion I get annoyed, but I thank you so much! I really need to learn SQL!
LVL 5
jtreherAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

HillwaaaCommented:
Hi jtreher,

One way would be through a cursor (put this inside your procedure):

declare @sql_string nvarchar(4000)

-- get all tables starting with tbl_my_table...
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


Cheers!
0
jtreherAuthor Commented:
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
0
HillwaaaCommented:
the line:

"declare @mycursor cursor for"

should just read:

"declare mycursor cursor for"

Cheers,
Hillwaaa
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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

Incorrect syntax near 'mycursor'
0
jtreherAuthor Commented:
Of course, I don't know which mycursor is causing the error!
0
HillwaaaCommented:
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
0
jtreherAuthor Commented:
hi Hillwaaa

Same thing, incorrect syntax near 'mycursor'
0
jtreherAuthor Commented:
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
0
HillwaaaCommented:
Hmm are you running this on sql 2000 from query analyzer?  (sp creates and runs fine for me)
0
doobdaveCommented:
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)
0
jtreherAuthor Commented:
Hilwaa

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

Cheers, Justin
0
doobdaveCommented:
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
0
HillwaaaCommented:
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
0
doobdaveCommented:
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?
0
jtreherAuthor Commented:
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 '''.

0
jtreherAuthor Commented:
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.
0
jtreherAuthor Commented:
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.
0
HillwaaaCommented:
Hey jtreher - using the following will not process any tables that are already owned by dbo:

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%'
and uid <> (select uid from sysusers where name = 'dbo')

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
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jtreherAuthor Commented:
Thanks so much!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.