Sysadmin, DB_Owner and Datareader

Question

Sysadmin, DB_Owner and Datareader

Eoin The Forge

SSCrazy

Points: 2932
More actions
March 23, 2009 at 6:27 am

#219288

Hi
I've beens asked to present to the team the basic permissions that a user will have on a DB server with the following roles Sysadmin, db_owner, db_datareader (See the image attached).
Have I missed anything big.
Regards
Eoin

Viewing 13 posts - 1 through 12 (of 12 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Your grid indicates that sysadmin cannot create or alter logins. That is incorrect.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Eoin The Forge SSCrazy Points: 2932 More actions · Answer 2

Hi Gail,

Great article in the last newsletter.

My formatting may not be the best but X means that they have this ability.

More of an X marks the spot rather that an X marking something unavailable. I might change that to a tick.

Eoin

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Kop_Eoin (3/23/2009)
My formatting may not be the best but X means that they have this ability.

Yes, I realise that. Your grid has Create/alter login listed twice, sysadmin is checked on one of them and not on the second.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Eoin The Forge SSCrazy Points: 2932 More actions · Answer 4

Oops ,

Thanks for that. I'll remove the offending line. Is their anything that you would add in or remove from that list. It's to give to developers to explain their (Initial) level of access to a dev server.

Moving from a scenario where everyone has sysadmin access.

Hopefully will be able to scale permissions back futher in time.

Thanks again

Eoin

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Just one point on the datareader. You said that it can view objects, but not stored procedures. That's not technically true.

The way the system objects are set up, a user can view the definition of any object that he has access to. So, datareader gives select on all tables and means that the user can see the definitions of those tables. He can't see the definition of the procedure, because he has not rights on the procedure. If the user has no rights on a view, then he cannot see the definition of the view, etc.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Eoin The Forge SSCrazy Points: 2932 More actions · Answer 6

Thanks Gail,

That exactly the type of feedback I was looking for. I need to rephrase that section in that they may be able to run the views (with some exceptions) and see datatypes (sp_help), but won't be able to see code behind the creation of the view.

Eoin

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

If they have permission to query a view, then they'll be able to see the definition of it.

If they have any permissions at all on the object, then they can see it's definition.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Eoin The Forge SSCrazy Points: 2932 More actions · Answer 8

Hi Gail,

I have a created a test user in the db_datareader role. The user does not have explicit access to the view I've created (select top 2 * from table1) however even though I can't see the definition, I can select from the view.

I'm just going to edit the grid to put it that the table definitions are the only object that the role gets implicit permissions to view.

And put in a separate row to let them know that they can select from all tables, most views and have no assess to run sp's

Eoin The Forge SSCrazy Points: 2932 More actions · Answer 9

Eoin The Forge

SSCrazy

Points: 2932

March 23, 2009 at 10:21 am

#964578

Updated grid.

Eoin The Forge SSCrazy Points: 2932 More actions · Answer 10

As regards giving the users the ability to view definitions

I found this info here http://www.mssqltips.com/tip.asp?tip=1593

So USE [dbname]

GO

GRANT VIEW Definition TO [reader_role]

Will allow a members of a role which has db_datareader permissions to view all the definitions without causing havoc.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

Kop_Eoin (3/24/2009)
So USE [dbname]
GO
GRANT VIEW Definition TO [reader_role]

GRANT VIEW DEFINITION ON [database|schema|object] TO [role|user]

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Eoin The Forge SSCrazy Points: 2932 More actions · Answer 12

Thanks Gail,

I am giving the developers the option (initially) to view all definitions for all users in a specific role

i.e

use my_db

create role dbreader

Go

sp_addrolemember 'db_datareader','dbreader'

Go

GRANT VIEW Definition TO dbreader --Will run on my_db

Go

sp_addrolemember 'dbreader','new_user'

Go

Eoin