[Brain asplode] Best practices and advice on SQL Server 2008 permissions

[SiD the Turtle]

Hi chaps,

I'm no DBA but I've managed to be dumped with a project to set up a development server, databases and relevant permissions. This will be used for database development (tables, views, stored procedures etc)

Basically everyone wants sysadmin privileges and local admin rights to the server because they clearly need all these rights to create a few tables.

My expertise is in AD permissions, I don't know where to start with SQL Server.

What I really want to do is give enough permissions to 'sandbox' each database, so a user can log in, create, alter and drop tables, SPs etc, but can't add more people as users to the database, alter each other's permissions etc. I almost want DBOWNER but with no security altering permissions.

What's the best way about going about this? Give them DBOWNER but then deny security altering permissions? Set up a whole new set of permissions? Where's the best place of doing this- the role, the schema or somewhere else?

Cheers in advance!

 

[Sin_Chase]

You should likely set up your own groups and granulate the permissions as you see fit.

I am no DBA either but it's what I would do from a neatness perspective and one of keeping track of things.

If anyone needs to inherit the system/administration it's going to be a nightmare working out how you have modified the default permissions sets.

 

[m4cc45]

Depends what they need to do as you can be very granual with permissions. I wouldn't give them SA as this would be pointless as, normally, your Dev environment is almost a mirror of live.

Perhaps suggest a change system where by you make the additional tables and give them something like data reader? Depends how many changes they're thinking of as this may not be viable.



M.