Re: A User wants DBO on a production db
From: Dan Guzman (danguzman@nospam-earthlink.net)
Date: 06/09/02
- Next message: Mary Chipman: "Re: Web Service using Windows Authentication"
- Previous message: tomoe: "A User wants DBO on a production db"
- In reply to: tomoe: "A User wants DBO on a production db"
- Next in thread: kevin: "Re: A User wants DBO on a production db"
- Reply: kevin: "Re: A User wants DBO on a production db"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
From: "Dan Guzman" <danguzman@nospam-earthlink.net> Date: Sun, 9 Jun 2002 11:24:40 -0500
Has the outside organization provided details as to exactly what
statements are failing even with the object permissions in place?
AFAIK, only statement permissions (DDL) would fail if the user has the
needed object permissions. If all objects have the same owner (e.g.
dbo) and data access is only through stored procedures, permissions on
the referenced objects are not needed, with the exception of dynamically
generated SQL statements.
I believe your situation is not at all uncommon. One of the roles of a
production DBA is to ensure the stability and integrity of the
production environment. If database objects or data are changed outside
a formal release process or the application, the integrity of the
production environment can't be maintained. The gatekeeper role of a
prod DBA is at odds with the ad-hoc changes to the production database.
However, if the application is not yet live, then you might consider
this as more of a development database rather than a production one. In
this case, you could relax security until the application is officially
released with the understanding that developer permissions will be
revoked after implementation. Whether or not this is appropriate
depends on the actual application state (development, QA, UAT or
production).
Adding the development users to the db_owner role will not compromise
security of other databases on the same sever or other servers on the
network. This assumes the database is owned by a non-production login
and, if you have linked servers, these are secure.
Hope this helps.
Dan Guzman
SQL Server MVP
-----------------------
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
-----------------------
"tomoe" <tomoe@rowanhouse.org.uk> wrote in message
news:b58601c20fbe$a9742800$a5e62ecf@tkmsftngxa07...
> Can anyone advise?
> I am administering a network with several Development,
> Test and Live databases. One application is developed by
> an outside organisation and there are two security issues:
>
> 1) They have insisted on a dial up connection with dbo
> access to the db. I am against this but am under great
> pressure to get this system live now and I am seen as
> the 'bottleneck'! I have set-up full read, write and
> delete access on all the tables (against my better
> judgement) but they say they still need more as some of
> the things they are trying are failing (on permissions).
>
> 2) It was agreed that the application would use stored
> procedures. I set-up an SQL user and gave execute on all
> stored procedures. They say there other users give them
> dbo to the app user and have no problems. I don't see why
> they should need this if everything is done by stored
> procedures...?
>
> Does giving dbo compromise any of the many other SQL
> servers on our network? If so, please give me examples of
> how - and is there any way I can secure everything but
> still allow them dbo access (the application user and the
> dial in user)?
>
> Any thoughts appreciated.
>
> Tomoe
>
- Next message: Mary Chipman: "Re: Web Service using Windows Authentication"
- Previous message: tomoe: "A User wants DBO on a production db"
- In reply to: tomoe: "A User wants DBO on a production db"
- Next in thread: kevin: "Re: A User wants DBO on a production db"
- Reply: kevin: "Re: A User wants DBO on a production db"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Relevant Pages
|
