Solved

Stored procedure to loop through db items change name and ownership

Posted on 2006-11-20
19
973 Views
Last Modified: 2012-06-21
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!
0
Comment
Question by:jtreher
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 6
  • 3
19 Comments
 
LVL 16

Expert Comment

by:Hillwaaa
ID: 17983292
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
 
LVL 5

Author Comment

by:jtreher
ID: 17983437
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
 
LVL 16

Expert Comment

by:Hillwaaa
ID: 17983453
the line:

"declare @mycursor cursor for"

should just read:

"declare mycursor cursor for"

Cheers,
Hillwaaa
0
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
LVL 5

Author Comment

by:jtreher
ID: 17983484
I actually added that in because I thought it was causing the original error:

Incorrect syntax near 'mycursor'
0
 
LVL 5

Author Comment

by:jtreher
ID: 17983492
Of course, I don't know which mycursor is causing the error!
0
 
LVL 16

Expert Comment

by:Hillwaaa
ID: 17983538
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
 
LVL 5

Author Comment

by:jtreher
ID: 17983569
hi Hillwaaa

Same thing, incorrect syntax near 'mycursor'
0
 
LVL 5

Author Comment

by:jtreher
ID: 17983573
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
 
LVL 16

Expert Comment

by:Hillwaaa
ID: 17983654
Hmm are you running this on sql 2000 from query analyzer?  (sp creates and runs fine for me)
0
 
LVL 8

Expert Comment

by:doobdave
ID: 17985948
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
 
LVL 5

Author Comment

by:jtreher
ID: 17987829
Hilwaa

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

Cheers, Justin
0
 
LVL 8

Expert Comment

by:doobdave
ID: 17988661
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
 
LVL 16

Expert Comment

by:Hillwaaa
ID: 17991947
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
 
LVL 8

Expert Comment

by:doobdave
ID: 17994236
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
 
LVL 5

Author Comment

by:jtreher
ID: 17998976
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
 
LVL 5

Author Comment

by:jtreher
ID: 17999144
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
 
LVL 5

Author Comment

by:jtreher
ID: 17999160
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
 
LVL 16

Accepted Solution

by:
Hillwaaa earned 350 total points
ID: 18000515
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
 
LVL 5

Author Comment

by:jtreher
ID: 18055306
Thanks so much!
0

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question