Risk Associated with read-only access to MSDB

Question

Post reply

Risk Associated with read-only access to MSDB

simpkint

SSC Enthusiast

Points: 183
More actions
March 4, 2010 at 6:33 am

#217477

Does anyone know of any risks associated with giving developers read only access to the MSDB? As a general rule we try to be very restrictive in granting any access, but the developers have only cited the ease of looking random things up in the MSDB to support their need for access. Does anyone have any thoughts on granting read only access to the development & production support team.

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 1

How often do you refresh the development & QA Environments?

I wrote DTS and SSIS Packages to do this on a routine basis.

This has to be be coordinated and your developers and they need to script out everything and check it into and out of Source Control.

Are you are subject to SOX?

Personally, I would not grant access to Developers to Production Databases.

Regards,

WC

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Carl Federl One Orange Chip Points: 25384 More actions · Answer 2

All SQL Server logins will always have those privileges granted to the public role within the msdb database. These privileges can be list by running:

exec msdb.dbo.sp_helprotect @username = 'public'

MS has not provided a role that

1) Grants viewing job information and history, regardless of the job owner

2) Denying creating and execution of jobs owned by the login.

I have had success by granting both the role TargetServersRole and SQLAgentUserRole.

You can determined the privileges granted and denied these roles by running sp_helprotect.

SQL = Scarcely Qualifies as a Language

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 3

I do not know what the requirement is in this situation but if a user wishes to view a SSIS package they will need a user password....

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Ron McCullough SSC Guru Points: 63877 More actions · Answer 4

When running the sp_helprotect @username = 'public' take careful note of all those commands that can be EXECUTED by the public role... be sure you want to allow this.

Also be aware that in SQL 2008 you also have a GUEST user role.

You may want to read this article by

K. Brian Kelley

http://www.mssqltips.com/tip.asp?tip=1900

Part of which is:

The db_datareader role gives implicit access to SELECT against all tables and views in a database.
In SQL Server 2005 and up, an explicit DENY will block access to objects.
It is not unusual to see this role used in production for developers.
It is not unusual to see this role used in production for normal users.
Applications will occasionally need this role.
Creating a user-defined database role and explicitly defining permissions is still preferred over the use of this role.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]