Tuesday, March 20, 2012

Changing Table ownership

Hi,
Just want to ask that if it is possible to change a table ownership?
thanksBooks Online:

sp_changeobjectowner
Changes the owner of an object in the current database.

Syntax
sp_changeobjectowner [ @.objname = ] 'object' , [ @.newowner = ] 'owner'

Arguments
[@.objname =] 'object'

Is the name of an existing table, view, or stored procedure in the current database. object is nvarchar(517), with no default. object can be qualified with the existing object owner, in the form existing_owner.object.

[@.newowner =] 'owner'

Is the name of the security account that will be the new owner of the object. owner is sysname, with no default. owner must be a valid Microsoft SQL Server user or role, or Microsoft Windows NT user or group in the current database. When specifying Windows NT users or groups, specify the name the Windows NT user or group is known by in the database (added using sp_grantdbaccess).|||yes there certainly is and you can find the procedure by going to Books Online \ Contents \ Transact SQL Reference \ System Stored Procedures\. it is in there somewhere. Can you find the right one?

Damn it. Sniped. By the way, it's best to keep it simple and have everything owned by dbo.|||Yes, except when you don't quite trust your SQL programmers with the sa password.

I like to know who writes bad code without passing it by me first. ;)|||Yes, except when you don't quite trust your SQL programmers with the sa password.
What to you mean by that? Nobody except DBA gets the sa password, or any server roles for that matter. Programmers do get to be db_owner in their own db in the development environment. Which, as the name says, is enough to make objects owned by dbo.|||I've worked with programmers that scream bloody murder if they don't get the sa password and will go to the IT director when they don't get it.

Some times, you are at the mercy of people who make bad decisions.|||everyone maintains a seperate dev environments locally that recieves code and db changes from the latest successful build on demand. all code has to be checked into source control to make the build.

they do not have sa in the QA or prod environments. just on their locals.|||That's the right way to do things, but it was set-up the wrong way before I got there and it wasn't allowed to be changed.

Not to mention that some vendors require the use of the sa account.|||I wonder why the previous bozo didn't allow access to SQL Server directly from the web. That's about the only thing missing from this disaster waiting to happen!

Developers with sa password = bad
Developers pusing bad code into production = double bad
Vendors with sa password = accident waiting to happen|||Not to mention that some vendors require the use of the sa account.
Vendors NEVER get the sa password. It may be needed that sa is required with installation (DBA will help with this and supervise what's going on) but there's absolutely no need for an application to use the sa-account or have the sysadmin role. If there is, it's doing something we don't want it to do :)

No comments:

Post a Comment