Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Understanding Object Ownership Expand / Collapse
Author
Message
Posted Friday, May 16, 2008 4:31 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, September 01, 2008 1:57 AM
Points: 17, 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
Post #501881
Posted Friday, May 16, 2008 9:09 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 8:32 AM
Points: 4,056, Visits: 5,182
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
Post #502159
Posted Monday, May 19, 2008 12:08 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: 2 days ago @ 6:32 PM
Points: 769, Visits: 241
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
Microsoft
(Former SQL Server MVP)
Post #503043
Posted Monday, May 19, 2008 3:08 PM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Friday, April 05, 2013 4:43 PM
Points: 1,473, Visits: 1,314
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.
Post #503171
Posted Tuesday, May 20, 2008 7:06 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, September 01, 2008 1:57 AM
Points: 17, 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.
Post #503604
Posted Tuesday, May 20, 2008 7:21 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, September 01, 2008 1:57 AM
Points: 17, 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
Post #503627
Posted Tuesday, May 20, 2008 7:28 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, September 01, 2008 1:57 AM
Points: 17, 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.


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 :)

Cheers,
Richard

Post #503639
Posted Tuesday, May 20, 2008 7:39 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 10:36 AM
Points: 1,199, Visits: 574
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
Post #503652
Posted Tuesday, May 20, 2008 8:02 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 27, 2008 2:23 AM
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
Post #503686
Posted Friday, July 23, 2010 11:38 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, April 07, 2013 7:38 AM
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
Post #958140
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse