stupid security question

[fungus]

Ok, I'm trying to understand how the db user corelates to the SqL login. Can one db user have multiple logins attached to it (like dbo with sa and nt admins).

This is in reference to the issue of broken chains of ownership problems that pop up from multiple users creating tables in a single database. I would like to be able to make all the developers be the dbo user of a given database without making them a sysadmin of the entire server. Is this possible?

If not, is there a sp that fixes a db so that all ownership reverts to dbo?

Thanks in advance
fUngUs

[Dale Shaw]

1) >>> This is in reference to the issue of broken chains of ownership problems that pop up from multiple users creating tables in a single database. I would like to be able to make all the developers be the dbo user of a given database without making them a sysadmin of the entire server. Is this possible?

Any user that has 'create' permissions in the database can create objects and, as you are aware, by default they would normally be 'username.object'. If you give the developers 'db_owner' role membership, they can write all their scripts to explicitly specify DBO as the owner:

CREATE PROC dbo.getsalesstats AS ...

etc.

This solves the problem.

If they forget then you can use sp_changeobjectowner to fix this. It would be easy enough to write an SP that retrieved a list of non-DBO objects (from sysobjects) and renamed them all. Schedule this to run every few hours ... or train your developers!

2) As for the relationship between server login and database user ... This is a throwback to Sybase SQL server. I'm not sure of the original logic as to why this came about but I presume it has something to do with grouping users together for security reasons. Rather than define everyone's security seperately, I can login as 'fred' but be aliased to 'salesman' in the 'sales' database. 'salesman' is where I set the security....

With NT Groups and SQL Roles this is no longer an issue.


Dale

[fungus]

Your reply answered all my questions. Thank you.

I am always for 'training developers' but I don't like to rely on it Thank you for the sp info. I am somewhat new to sql admin and this will help me be able to fix a recurring problem.


------------
Dale Shaw at 3/11/01 5:14:48 PM

1) >>> This is in reference to the issue of broken chains of ownership problems that pop up from multiple users creating tables in a single database. I would like to be able to make all the developers be the dbo user of a given database without making them a sysadmin of the entire server. Is this possible?

Any user that has 'create' permissions in the database can create objects and, as you are aware, by default they would normally be 'username.object'. If you give the developers 'db_owner' role membership, they can write all their scripts to explicitly specify DBO as the owner:

CREATE PROC dbo.getsalesstats AS ...

etc.

This solves the problem.

If they forget then you can use sp_changeobjectowner to fix this. It would be easy enough to write an SP that retrieved a list of non-DBO objects (from sysobjects) and renamed them all. Schedule this to run every few hours ... or train your developers!

2) As for the relationship between server login and database user ... This is a throwback to Sybase SQL server. I'm not sure of the original logic as to why this came about but I presume it has something to do with grouping users together for security reasons. Rather than define everyone's security seperately, I can login as 'fred' but be aliased to 'salesman' in the 'sales' database. 'salesman' is where I set the security....

With NT Groups and SQL Roles this is no longer an issue.


Dale