Solved

Stored procedure to loop through db items change name and ownership

Posted on 2006-11-20
19
970 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Query (lookup) 8 65
Server 2012 r2 and SQL 2014 6 34
Use SSRS to email customers? 4 30
SQL - Update field value based on two other fields in same table. 13 43
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

752 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