Solved

Stored procedure to loop through db items change name and ownership

Posted on 2006-11-20
19
966 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
  • 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
 
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

758 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now