SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Understanding Object Ownership


Understanding Object Ownership

Author
Message
Richlion-690360
Richlion-690360
SSC Journeyman
SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)

Group: General Forum Members
Points: 99 Visits: 44
Hi All,

I ran across this article and it is nice. Howerver, I am still confused and need a bit of more information - to be honest, I am a bit of a beginner with SQL Server (normally I work with Oracle).

My question is related to the fact that I am asked to revoke access for the developers to login into the SQL Server and use the SA account. They use the Remote Desktop and login with their domain accounts, but I don't think this is a good solution. My question is - can this be avoided?

So I started testing a Client configuration, installed the Client part of MS SQL, so that a developer uses Query Analyzer on his PC, he can login to the databases using his domain account (without using Remote Desktop). I don't know if this is the right way to do it, but I searched and googled for at past two days and I cannot find a short explanation. How should this be done in a Domain environment, where developers should not have access to the server? What are the standards to work with a SQL Server?

I don't suppose we should login from a client as SA or DBO. I use a domain test Windows account - let's call is tom.bear
Now - tom.bear starts the query analyzer and uses Windows authentication. He is a member of db_owner, so he can create a table as DBO:
create table dbo.new_table ( col int)

And this works. But is this the way it should be set up? What is your experience?

Thanks for any suggestions.

(I edited this post because I missed something and it didn't work the first time)

Regards,
Richard
Greg Charles
Greg Charles
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11213 Visits: 5985
Hi Richard,

Developers in my shop use the client tools (SQL Server Management Studio/Enterprise Manager, Query Analyzer) on their workstations to login to the dev SQL Server using Windows authentication. We (the DBAs) grant them access to the instance and databases.

Developers should never logon as SA because it's the administrative login that can do anything in SQL Server. Likewise nobody but DBAs are members of the Sysadmin server role. We've not found a good reason to make developers DBO or members of db_owner. Normally, they get membership in the fixed database roles db_datareader, db_datawriter, and db_ddladmin(allows dbo. object creation) in the databases they work on.

Greg
Kathi Kellenberger
Kathi Kellenberger
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2425 Visits: 347
You know, it really depends on the shop -- how job duties are divided. In small shops, sometimes the developer and DBA are the same person.

The best practice would be for developers to ask the DBA to create the new objects and restrict the rights of the developers.

Aunt Kathi
Linchpin People Teammate
SQL Server MVP
Author of Expert T-SQL Window Functions
SQL ORACLE
SQL ORACLE
SSCertifiable
SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)

Group: General Forum Members
Points: 6637 Visits: 1314
It has nothing wrong for a user to access SQL Server using a domain account if the server is not a production server. You can set permission to his/her account according to your security policy.
Normally, a user can only be a member of application role in a production server.
In either case, a user shoiuld not have SA. Otherwise, our DBAs will lose our jobs.
Hehe
Richlion-690360
Richlion-690360
SSC Journeyman
SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)

Group: General Forum Members
Points: 99 Visits: 44
Greg Charles (5/16/2008)
Hi Richard,

Developers in my shop use the client tools (SQL Server Management Studio/Enterprise Manager, Query Analyzer) on their workstations to login to the dev SQL Server using Windows authentication. We (the DBAs) grant them access to the instance and databases.

Developers should never logon as SA because it's the administrative login that can do anything in SQL Server. Likewise nobody but DBAs are members of the Sysadmin server role. We've not found a good reason to make developers DBO or members of db_owner. Normally, they get membership in the fixed database roles db_datareader, db_datawriter, and db_ddladmin(allows dbo. object creation) in the databases they work on.


Thanks Greg,

very valuable info for me.

Regards,
Richard.
Richlion-690360
Richlion-690360
SSC Journeyman
SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)

Group: General Forum Members
Points: 99 Visits: 44
Kathi Kellenberger (5/19/2008)
You know, it really depends on the shop -- how job duties are divided. In small shops, sometimes the developer and DBA are the same person.

The best practice would be for developers to ask the DBA to create the new objects and restrict the rights of the developers.



Thanks Kathi,

well, sure everything depends. I understand your point.

In my environment there is a change control system in place, developers create scripts, that are tested, including table creation. Then those scripts are passed to people who are not experienced DBA's, but put things into a test and then production environment. They do not have the full knowledge of a DBA.

Of course, when in doubts the developer comes and asks a DBA for help, but in my environment there is only one DBA who normally has a lot of his own work apart from DBA stuff to do and there are too many developers, so a DBA creating objects could be a small bottleneck to the development.

Also in a test environment I think it is better and quicker for the developer to create the objects, so that he knows how to do it, changes the structure anytime he wants to until a final version is ready, then creates the scripts and this way he also takes part and shares the responsibility for the consistency in the structure of the database. After all he is the one who works as the designer and builds the application.
Apart from that if only a DBA is to create objects, any mistakes are blamed only on a DBA and a developer will tend to say - "I did not create that table, it was the DBA".

Thanks again,
Richard
Richlion-690360
Richlion-690360
SSC Journeyman
SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)

Group: General Forum Members
Points: 99 Visits: 44
SQL ORACLE (5/19/2008)
It has nothing wrong for a user to access SQL Server using a domain account if the server is not a production server. You can set permission to his/her account according to your security policy.
Normally, a user can only be a member of application role in a production server.
In either case, a user shoiuld not have SA. Otherwise, our DBAs will lose our jobs.
Hehe


Well, you are right, access to SA should be restricted and that is what I am trying to do, because the application structure was created a long time ago by an external company and there was no DBA involved at that time who would say - NO ACCESS TO SA. Nobody really bothered about the security measures.

As to the access to a test system, I tend to keep my test and production systems the same, so no SA access in a test system too. Otherwise the test system becomes a mess and it doesn't make sense to maintain two systems in order to know, what should be in the test system that should not be in the production system - unless it's a new funtionality. Not to mention testing new development environments Smile

Cheers,
Richard
rajankjohn
rajankjohn
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1989 Visits: 575
For individual logins, strictly use windows authentication; never give shared logins. For code tracking, make use of some version control system. Also, see of schemas can be helpful for your environment.

http://rajanjohn.blogspot.com
Richlion
Richlion
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 5
Rajan John (5/20/2008)
For individual logins, strictly use windows authentication; never give shared logins. For code tracking, make use of some version control system. Also, see of schemas can be helpful for your environment.

http://rajanjohn.blogspot.com


Thanks, interesting SQL Server can do this.
We do have a change control system, howerever, this SQL Server application is not changed very often and it is done by an external company.
Yet I was considering implementing this in my Oracle databases.

Richard
Robert Criswell
Robert Criswell
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 11
Just wanted to say thank you for this well written article. As an accidental DBA I now understand Object Ownership much better than I did before.

Bob
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search