April 22, 2010 at 6:32 am
I've inherited a production database. It has a ROLE called APPLICATION_USERS. When I scripted this role, I see it's AUTHORIZATION is [dbo]. (many individual UserIDs are attached to this Role) Here is the role scripted:
USE [myDB]
GO
CREATE ROLE [APPLICATION_USERS] AUTHORIZATION [dbo]
GO
Does this mean any UserID (SQL Login or Windows Login) in myDB that is within this Role retains dbo access?
How can I limit the access to this Role, APPLICATION_USERS, to Read/Write and EXECUTE procs only?
Also, If I want to create a Role called ConfigMgmt that is limited to Creating Objects (running DDL) but should not be able to Read/Write data OR Execute proc's, what permission(s) should I assign to this Role?
April 22, 2010 at 9:04 am
The AUTHORIZATION argument designates the owner of the role, not permissions. Since a role is a database object, it must be owned by a user or another role.
The role's permissions are set by it's membership in other database roles or by using GRANT and DENY.
So, to give the APPLICATION_USERS role read and writer permission to all tables in the database, you can make it a member of the db_datareader and db_datawriter fixed database roles. You can grant EXECUTE permission on stored procedures to the role just like you'd do with an individual user.
You can grant a role membership in db_ddladmin to allow it to run DDL commands.
Greg
April 22, 2010 at 9:22 am
thx for that clarification Greg.. also derived this info:
*** We can also include a custom created --> db_executor priveledge w/in these Roles (see definition below) which will set each Role with execute permissions on all existing stored procedures and scalar functions AND all subsequently created ones.
____________________________________________________________________
Database-level Roles: http://msdn.microsoft.com/en-us/library/ms189121.aspx
db_accessadmin - Members of the db_accessadmin fixed database role can add or remove access to the database for Windows logins, Windows groups, and SQL Server logins.
db_ddladmin - Members of the db_ddladmin fixed database role can run any Data Definition Language (DDL) command in a database.
db_datawriter - Members of the db_datawriter fixed database role can add, delete, or change data in all user tables.
db_datareader - Members of the db_datareader fixed database role can read all data from all user tables.
____________________________________________________________________
msdb Roles (req'd for SSIS Pkg promotion)
db_ssisadmin, db_ssisoperator, db_ssisltduser - Members of these database roles can administer and use SSIS. Instances of SQL Server that are upgraded from an earlier version might contain an older version of the role that was named using Data Transformation Services (DTS) instead of SSIS.
Caution/Important: Members of the db_ssisadmin role and the dc_admin role may be able to elevate their privileges to sysadmin. This elevation of privilege can occur because these roles can modify Integration Services packages and Integration Services packages can be executed by SQL Server using the sysadmin security context of SQL Server Agent. To guard against this elevation of privilege when running maintenance plans, data collection sets, and other Integration Services packages, configure SQL Server Agent jobs that run packages to use a proxy account with limited privileges or only add sysadmin members to the db_ssisadmin and dc_admin roles.
Review SSIS Roles here: http://msdn.microsoft.com/en-us/library/ms141053.aspx
SQL Server Integration Services includes the three fixed database-level roles, db_ssisadmin, db_ssisltduser, and db_ssisoperator, for controlling access to packages. Roles can be implemented only on packages that are saved to the msdb database in SQL Server. You assign roles to a package using SQL Server Management Studio. The role assignments are saved to the msdb database. (Our SSIS Pkg's are NOT saved to msdb; they're saved to the File System!!)
____________________________________________________________________
EXECUTE PROC/SCALAR FUNCTION Permission - SQL Server 2008/5 make the EXECUTE permission grantable at the database scope. We can issue a statement like the example below and this will GRANT execute permissions on all existing stored procedures and scalar functions AND all subsequently created ones.
Use myDatabase
GO
/* CREATE A NEW ROLE */
CREATE ROLE db_executor
/* GRANT EXECUTE TO THE ROLE */
GRANT EXECUTE TO db_executor
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply