Minimum permissions for SQL2005 development?

Hi
I'm trying to get started developing on this 2005 box, but I'm running
into big security problems, because this is one of those shops where
getting the most minimal level of access to the dev server (even as a
developer who should supposedly be doing something productive for the
daily rate) is harder than getting Tony Blair to tell you the Trident
launch codes.
So at the moment even Management Studio won't work. And the security
administrators are not SQL DBAs - so it's basically up to me to work
out what access I need (db_owner/sa? For a developer, on the dev
box? Forget it!), tell them this, and then go through whatever
paperwork/string-pulling/bribery/black ops are necessary to actually
get it granted.
If it was 2000 I'd have some idea, but I'm relatively new to 2005 -
developed on a "friendly" box as sa before, but never run into this.
Anyone know what permissions I'd need to:
- Create/drop/execute objects in a DB (obviously, db_datawriter and
db_datareader as well)
- Execute system stored procedures (especially sp_executesql)
- Develop, test and run SS Integration Services?
I suspect that what I'm running into at the moment is lack of
permissions in master and msdb - but it's hard to work out, when my
effective server role is something like "Unauthenticated hacker".
Apologies for my ignorance - I've never run into this level of
paranoia before.
thanks for any ideas.
seb> Minimum permissions for SQL2005 development?
The obvious answer is that you need the minimal level of permissions need to
do your job :-)

> And the security
> administrators are not SQL DBAs - so it's basically up to me to work
> out what access I need (db_owner/sa?
One would think the security admins would need to know something about SQL
security if managing SQL security is part of their job. You should only
need to tell them what you need to do and they ought to be able to know
enough to give you those permissions and nothing more.
Sysadmin role members have full control over the entire SQL Server and will
provide full control over all databases. If you don't need to change server
configuration options, install service packs and that sort of thing,
sysadmin is probably overkill. db_owner is also a powerful role but may be
appropriate if you need *complete* control over some, but not all, databases
on the server.

> Anyone know what permissions I'd need to:
> - Create/drop/execute objects in a DB (obviously, db_datawriter and
> db_datareader as well)
You'll need CREATE permissions on the target object type and ALTER on the
object's schema. If you have CONTROL on the schema, you won't need
db_datawriter and db_datareader membership to data access in that schema.

> - Execute system stored procedures (especially sp_executesql)
You should already have execute permissions on sp_executesql because execute
permissions default to public and you access master via the guest user
context. If you don't have execute permissions, the default SQL Server
permissions were changed and permissions will need to be granted via other
means.

> - Develop, test and run SS Integration Services?
Like any application, you'll SQL permissions to access the objects used by
the package. If you use msdb as the package store, you'll need to be a
member of the appropriate role (db_dtsadmin or db_dtsltduser).
Hope this helps.
Dan Guzman
SQL Server MVP
"sebt" <sebthirlway@.hotmail.com> wrote in message
news:1175679014.583549.224630@.b75g2000hsg.googlegroups.com...
> Hi
> I'm trying to get started developing on this 2005 box, but I'm running
> into big security problems, because this is one of those shops where
> getting the most minimal level of access to the dev server (even as a
> developer who should supposedly be doing something productive for the
> daily rate) is harder than getting Tony Blair to tell you the Trident
> launch codes.
> So at the moment even Management Studio won't work. And the security
> administrators are not SQL DBAs - so it's basically up to me to work
> out what access I need (db_owner/sa? For a developer, on the dev
> box? Forget it!), tell them this, and then go through whatever
> paperwork/string-pulling/bribery/black ops are necessary to actually
> get it granted.
> If it was 2000 I'd have some idea, but I'm relatively new to 2005 -
> developed on a "friendly" box as sa before, but never run into this.
> Anyone know what permissions I'd need to:
> - Create/drop/execute objects in a DB (obviously, db_datawriter and
> db_datareader as well)
> - Execute system stored procedures (especially sp_executesql)
> - Develop, test and run SS Integration Services?
> I suspect that what I'm running into at the moment is lack of
> permissions in master and msdb - but it's hard to work out, when my
> effective server role is something like "Unauthenticated hacker".
> Apologies for my ignorance - I've never run into this level of
> paranoia before.
> thanks for any ideas.
>
> seb
>|||Hello Seb,
You need minimum level permission of db_ddladmin fixed database role so that
you will able to create objects in respective database, and always create
objects as DBO so that it won't result into broken ownership chain.
Goodluck!
Cheers,
MB
"sebt" <sebthirlway@.hotmail.com> wrote in message
news:1175679014.583549.224630@.b75g2000hsg.googlegroups.com...
> Hi
> I'm trying to get started developing on this 2005 box, but I'm running
> into big security problems, because this is one of those shops where
> getting the most minimal level of access to the dev server (even as a
> developer who should supposedly be doing something productive for the
> daily rate) is harder than getting Tony Blair to tell you the Trident
> launch codes.
> So at the moment even Management Studio won't work. And the security
> administrators are not SQL DBAs - so it's basically up to me to work
> out what access I need (db_owner/sa? For a developer, on the dev
> box? Forget it!), tell them this, and then go through whatever
> paperwork/string-pulling/bribery/black ops are necessary to actually
> get it granted.
> If it was 2000 I'd have some idea, but I'm relatively new to 2005 -
> developed on a "friendly" box as sa before, but never run into this.
> Anyone know what permissions I'd need to:
> - Create/drop/execute objects in a DB (obviously, db_datawriter and
> db_datareader as well)
> - Execute system stored procedures (especially sp_executesql)
> - Develop, test and run SS Integration Services?
> I suspect that what I'm running into at the moment is lack of
> permissions in master and msdb - but it's hard to work out, when my
> effective server role is something like "Unauthenticated hacker".
> Apologies for my ignorance - I've never run into this level of
> paranoia before.
> thanks for any ideas.
>
> seb
>