| Author |
Topic |
|
MuadDBA
628 Posts |
 Posted - 2004-03-11 : 09:01:42
|
| All right, I have a user on my server that the application turnover area uses to create procedures, tables, etc on the production server. I just implemented this, as before they were using the SA login for this. The problem is that even though I have assigned their user the DB_OWNER role, all of the objects they create are not owned by DBO, but by their user.How do I set up their user so it creates the objects as DBO instead of the user? They have a few hundred scripts in the source control program, and they will not be happy if they have to check each of them out and explicitly place a DBO.[object_name] in front of them. |
|
|
MuadDBA
628 Posts |
Posted - 2004-03-11 : 09:18:33
|
| Damn, looks like they have to be set up as a sysadmin server role in order for that to work. That's dumb. |
 |
|
|
Sitka
Aged Yak Warrior
571 Posts |
Posted - 2004-03-11 : 09:35:51
|
| Since you mentioned until this time they were using SA then all the created objects to this point would be dbo.So multiname references would exist in the state of dbo ownership.I kind of have a similar setup and use a login for development which has readonly on some crucial databases that I don't want to slip up and screw with.Why would they have to check them out and change them? can't they just change them as they go.dbo. not a lot of work moving forward. And well worth it. |
|
|
|
MuadDBA
628 Posts |
Posted - 2004-03-11 : 09:40:35
|
| Yeah, they can change them as they go.The thing that annoys me is that when I script all the objects from SQL Server, it certainly uses ownership qualification to DROP the old ones. But it doesn't keep the ownership qualifications on the obejcts it is going to create. @#@$ |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-03-11 : 12:36:51
|
| You can change their name inside the database so that they are dbo. Granting db_owner role isn't enough. They need to be dbo inside database. To view the info easily in EM, go to logins. Double click on an id. Go to database access tab. You will want to change the User column to dbo.For Query Analyzer, sp_grantdbaccess will do the trick (@name_in_db variable).Tara |
|
|
|
MuadDBA
628 Posts |
Posted - 2004-03-11 : 13:24:42
|
You know, I tried doing that. It says "User 'dbo' already exists." I am assumning it is because SA is automatically aliased to dbo. The BOL says of the stored proceure:quote:
name_in_db must not already exist in the current database
|
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-03-11 : 13:31:44
|
| Oh yes, I forgot about that.Tara |
|
|
|
MuadDBA
628 Posts |
Posted - 2004-03-11 : 13:32:59
|
| It's one of the nice "features" we had in SQL 6.5 but apparently isn't necessary in later versions. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-03-11 : 13:42:50
|
| Right. The developers here must be prefacing everything with dbo. Or they are run sp_changeobjectowner. I'll have to ask.Tara |
|
|
|
MuadDBA
628 Posts |
Posted - 2004-03-11 : 13:48:28
|
| Well, apparently I might be wrong about the scripting behavior. I scripted several procs, tables, views, etc, and they all come with the ownership qualifier on them. I am using SQL 2000 now instead of SQL 7.0, so maybe that is the difference...but it still annoys me. |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2004-03-12 : 13:05:48
|
| Joe, you can get the default dbo ownership of objects by using sp_changedbowner and making them the actual db owner and not just a member of the db_owner role. But if you've got multiple users, then you're better off going with explicitly stating dbo in the create script.--------------------------------------------------------------Find more words of wisdom at [url]http://weblogs.sqlteam.com/markc[/url] |
|
|
|
MuadDBA
628 Posts |
Posted - 2004-03-12 : 17:03:15
|
| Tried that, too...it tells me the user already exists in the database. This is just damn annoying, when it used to be so damn easy. |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2004-03-12 : 19:58:14
|
| Sorry, forgot about that. You remove the user from the DB first, then make them theowner. I think if that user explicitlt owns any objects you have to change ownership of those too.--------------------------------------------------------------Find more words of wisdom at [url]http://weblogs.sqlteam.com/markc[/url] |
|
|
|
tfountain
Constraint Violating Yak Guru
491 Posts |
Posted - 2004-03-15 : 11:20:50
|
| Another simple solution to this problem is implementing a standard that all scripts that create objects prefix the owner explicitly as dbo. We've been doing this for years and it works like a charm. Out of 6 developers we may have only had a slip up maybe a 1/2 dozen times in the past 3 years. I'm not familiar with your situation but if you're able to implement this "discipline" it might go a long way.EDIT: oops, guess I should have read the entire thread to see that this is what everyone else is suggesting. But to add, we don't script anything from SQL Server if we don't have to. We create our scripts for our objects and store them in Visual SourceSafe. Our scripts go like so:USE <dbname>GOIF OBJECT_ID('dbo.<object>') IS NOT NULL DROP <object type> dbo.<object>GOCREATE <object type> dbo.<object>AS<code>GOGRANT ALL ON dbo.<object> TO PUBLIC GONote: the permissions piece is sort of a separate discussion. This only applies to objects where permissions are required (i.e. Not for triggers). We do this so our developers don't need to be worried about the permissions the DBA has setup. The DBA has a job that runs that sets the permissions to how they should be. And since most of the developers only deploy to our development environment this is good for us (we have a change process that moves things to other environments). |
|
|
|
|
Aliasing users in SQL2000