Post reply

db_datareader allow alter permissions

  • January 5, 2012 at 2:39 am

    #249137

    Hi All,

    Hope someone else can confirm this, but it seems like db_datareader db role do not just provide select permissions, but also alter and update permissions.

    According to what I read, it is not suppose to be like this.

    Kind regards

    Andries

  • January 5, 2012 at 2:52 am

    #1428855

    If a user is member of only db_datareader db role he/she can only select. He/she cannot alter or update.

    Check whether alter or update permission is granted separately.

    Also check whether the login is member of sysadmin fixed server role.

  • January 5, 2012 at 2:54 am

    #1428857

    ajsnyman (1/5/2012)

    Hi All,

    Hope someone else can confirm this, but it seems like db_datareader db role do not just provide select permissions, but also alter and update permissions.

    It does not. db_datareader grants select on all tables, nothing more.

    Check that the login isn't a member of other roles. If it's a windows login check that it doesn't have DB access from another group with different permissions.

    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

  • January 5, 2012 at 2:55 am

    #1428858

    I did, the user had nothing else, even if I run:

    SELECT * FROM fn_my_permissions (NULL, 'DATABASE');

    I only get

    entity_namesubentity_namepermission_name

    databaseCONNECT

    databaseSELECT

    I had to explicitly deny alter and update on database permissions for the user.

    Is there a way of scripting the user to see if it has extra permissions via some unforeseen grant?

  • January 5, 2012 at 2:57 am

    #1428859

    ajsnyman (1/5/2012)

    I did, the user had nothing else, even if I run:

    SELECT * FROM fn_my_permissions (NULL, 'DATABASE');

    I only get

    entity_namesubentity_namepermission_name

    databaseCONNECT

    databaseSELECT

    I had to explicitly deny alter and update on database permissions for the user.

    db_datareader applies select on all tables and views only, is this a sql server account or a windows account?

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • January 5, 2012 at 2:58 am

    #1428861

    It's a SQL server account

  • January 5, 2012 at 4:05 am

    #1428880

    Please return the results of the following queries

    check explicit permissions

    select dp.name, dbp.state_desc, dbp.permission_name, dbp.class_desc

    from sys.database_principals dp inner join sys.database_permissions dbp

    on dp.principal_id = dbp.grantee_principal_id

    where dp.name = 'yoursqlacct'

    Check roles assigned

    select dp.name, dp2.name from sys.database_principals dp

    inner join sys.database_role_members drm on dp.principal_id = drm.member_principal_id

    inner join sys.database_principals dp2 on drm.role_principal_id = dp2.principal_id

    where dp.name = 'yoursqlacct'

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • January 5, 2012 at 4:10 am

    #1428882

    Here goes

    q1:

    namestate_descpermission_nameclass_desc

    horizonReaderDENYALTERDATABASE

    horizonReaderGRANTCONNECTDATABASE

    horizonReaderDENYALTERSCHEMA

    q2:

    namename

    horizonReaderdb_datareader

    horizonReaderdb_denydatawriter

    The denies is what I had to specify later.

  • January 5, 2012 at 4:22 am

    #1428890

    Sorry guys, it must be related to my specific db some how.

    I tried db_datareader on master, then alter is denied.

  • January 5, 2012 at 4:50 am

    #1428899

    Check that the user isn't the owner of any objects (or the owner of the DB)

    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

  • January 5, 2012 at 5:02 am

    #1428911

    No object owner or db_owner ;(

  • January 5, 2012 at 5:52 am

    #1428943

    ajsnyman (1/5/2012)

    ALTERDATABASE

    ALTERSCHEMA

    These are not object level but higher permissions and imply certain other permissions. Does the user own a schema?

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • January 5, 2012 at 6:09 am

    #1428950

    Perry Whittle (1/5/2012)

    ajsnyman (1/5/2012)

    ALTERDATABASE

    ALTERSCHEMA

    These are not object level but higher permissions and imply certain other permissions. Does the user own a schema?

    There is a default schema 'dbo' but none selected under the Owned Schema.

  • January 5, 2012 at 6:21 am

    #1428958

    please check the results of the following query

    selectsch.name AS SchemaName

    , dp.name AS DBUserName

    from sys.schemas sch inner join sys.database_principals dp on

    sch.principal_id = dp.principal_id

    where dp.name = 'yourdbuser'

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • January 5, 2012 at 6:26 am

    #1428960

    Returned no results.

Viewing 15 posts - 1 through 15 (of 32 total)

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