Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


DB Role permissions


DB Role permissions

Author
Message
Express12
Express12
Say Hey Kid
Say Hey Kid (693 reputation)Say Hey Kid (693 reputation)Say Hey Kid (693 reputation)Say Hey Kid (693 reputation)Say Hey Kid (693 reputation)Say Hey Kid (693 reputation)Say Hey Kid (693 reputation)Say Hey Kid (693 reputation)

Group: General Forum Members
Points: 693 Visits: 1021
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?

BT
Greg Charles
Greg Charles
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4089 Visits: 5823
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
Express12
Express12
Say Hey Kid
Say Hey Kid (693 reputation)Say Hey Kid (693 reputation)Say Hey Kid (693 reputation)Say Hey Kid (693 reputation)Say Hey Kid (693 reputation)Say Hey Kid (693 reputation)Say Hey Kid (693 reputation)Say Hey Kid (693 reputation)

Group: General Forum Members
Points: 693 Visits: 1021
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

BT
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search