Stored procedure to loop through db items change name and ownership
Posted on 2006-11-20
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
EXEC('sp_changeobjectowner @objname = ''tbl_my_table'', @newowner = dbo')
Grant update,select,insert On tbl_my_table To web_serv
/* 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!