Permission nightmare or not?

Question

Permission nightmare or not?

Barkingdog

SSCoach

Points: 18670
More actions
April 12, 2010 at 5:03 pm

#217741

I work with a dev who would lke the following permissions on several databses, on different servers (sql 2000, sql 2005, sql 2008). Is his request a reasonable one would it open a security nightmare?
>>>
Read-only access to see data from any table
Ability to display estimated execution plan of a query
Ability to include actual estimated plan of a query
Ability to create/modify and see the SQL code of a "View"
Ability to view the sQL code of a "Stored procedure"
>>>
Barkingdog

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

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

sandy_1006 SSC Eights! Points: 953 More actions · Answer 1

Give user access (can be a windows or sql user)the public permission as public.Check in the properties then permissions.

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 2

sandy_1006 (4/13/2010)
Give user access (can be a windows or sql user)the public permission as public.Check in the properties then permissions.

Try to avoid assigning any permissions to public. That's a worst practice.

K. Brian Kelley
@kbriankelley

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 3

Barkingdog (4/12/2010)
I work with a dev who would lke the following permissions on several databses, on different servers (sql 2000, sql 2005, sql 2008). Is his request a reasonable one would it open a security nightmare?
>>>
Read-only access to see data from any table
Ability to display estimated execution plan of a query
Ability to include actual estimated plan of a query
Ability to create/modify and see the SQL code of a "View"
Ability to view the sQL code of a "Stored procedure"
>>>
Barkingdog

You didn't indicate whether these are development or production SQL Servers. Ultimately, it's not the call of the developer. It's the call of whoever owns the data with respect to the first request. Estimated and actual execution plan isn't as big a deal unless the queries themselves can contain sensitive data (which is always possible). Create/Modify a view... not in production. Your change control should prohibit such. See the SQL code of a stored procedure? Typically not a big deal, but not typically something you see in production, either.

K. Brian Kelley
@kbriankelley

WayneS SSC Guru Points: 95460 More actions · Answer 4

Barkingdog (4/12/2010)
I work with a dev who would lke the following permissions on several databses, on different servers (sql 2000, sql 2005, sql 2008). Is his request a reasonable one would it open a security nightmare?
>>>
Read-only access to see data from any table
Ability to display estimated execution plan of a query
Ability to include actual estimated plan of a query
Ability to create/modify and see the SQL code of a "View"
Ability to view the sQL code of a "Stored procedure"
>>>
Barkingdog

I'd be okay with this on a dev server... but not a production one.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

I agree with Wayne. I am fine with development being a sandbox for the developers - so long as proper checks and constraints are in place to pass code up to QA and Prod.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events