May 22, 2008 at 5:05 am
I’m looking into the possibility of creating a group of SQL Server 2000 users who would have full data access across all databases on a number of servers, along with most of the permissions provided by the sysadmin server role – one definitely not required would be securityadmin.
Would this be possible through a combination of server roles alone? It is important that this group would have full data access and permissions on all databases across all servers without an administrator having to explicitly create datareader / datawriter database roles and grant execute permissions on discrete databases as and when required or when a database is created.
Essentially this would be a group of sysadmin users but without securityadmin permissions.
Grateful for any thoughts..
May 23, 2008 at 12:06 pm
What do you mean by "full data access"? Do you want this group to be able to select, update, delete data only or do you want them to be able to create objects like databases, tables, views, etc.? If you're talking about only the former, there aren't any fixed server roles that wouldn't be overkill.
You'd be better off adding the group to the Model database and granting database role membership there so they'd be included in every new database. As for execute permissions on stored procedures, there's no blanket way you can grant that without generating your own script in each database.
Greg
May 28, 2008 at 3:57 am
Thanks for the reply, Greg.
I do want them to be able to select, update and delete data and to be able to create objects like databases, tables, views etc. I also want them to have execute permissions.
I suppose what I'm asking is a fairly simple question - please excuse my ignorance: do server roles, other than sqladmin, allow full data access as outlined above (including execute permissions), or are they restricted to the tasks their names explicitly suggest i.e. database permissions need to be granted seperately?
I think I'm imagining two possible scenarios: -
1. individual database permissions (including execute permissions without the need for the running of a script everytime a new procedure is created) are granted to developers on specific databases as and when required, along with certain permissions granted by server roles such as setupadmin, processadmin and dbcreator
2. all permissions inherent in the sqladmin server role (including access to all databases and full execute permissions) are granted minus a few key ones such as serveradmin and security admin.
Hope that makes sense and is not a clear case of points missed through ignorance and/or stupidity!
Stephen
May 28, 2008 at 12:13 pm
The fixed server roles have only the permissions described in BookOnLine under "Roles" (you can also see them by right-clicking on the role in Enterprise Manager and selecting "properties"). Sysadmin is the only server role that can do everything you want and it sounds like you know it's not a good idea to make everybody a member of it. AFAIK, you can't create a custom server role.
As for fixed database roles, db_owner is the only one that would allow execution of all stored procedures. It's probably overkill for most developers also as it can do anything in a database, including dropping it.
Maybe if I describe how permissions are handled in my shop it will give you something to start with.
Developers are members of a Windows group which is granted connect permission to the SQL Server instance. The group login is a user in the Model database and is a member of the public, db_datareader, db_datawriter, db_securityadmin and db_ddladmin roles.
When a database is created, Model is used as a template and the group user is included. db_ddladmin allows them to create objects, db_securityadmin allows them to grant permissions on the objects, db_datareader and db_datawriter allow them to read and change data. Execute permissions can only be granted after a stored procedure is created.
Greg
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply