User Access in SQL server 2005

  • Hi

    I am facing a unique problem maybe i am missing something here. The problem is I have a user Domainname\Azeem

    I give the user db_dataReader permission on a database Customers.

    Security --> Logins --> LoginName (Domainname\Azeem) rightclick --> Properties --> Usermapping --> databaseName

    --> db_dataReader.

    but when i right click on the database --> select Properties --> Permissions\EffectivePermissions i see that the user has got the permissions on everything.

    Any advice will be immensely appreciated.

    Thanks & Regards

    Azeem.

  • Please clarify "permissions on everything"

    db_datareader gives a User permission to select from all tables within the database.

    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

  • hi

    some of the Permissions the user has are:

    1.ALTER

    2. ALTER ANY APPLICATION ROLE

    3. ALTER ANY ASSEMBLY

    4. ALTER ANY ASYMMETRIC KEY

    5. ALTER ANY CERTIFICATE

    6. ALTER ANY SCHEMA

    7. ALTER ANY SERVICE

    Regards.

  • Did the user get added to a server role as well? Did the user get added to DBO within the database?

    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

  • No i did not add the user to any server roles. I have given the default schema for the user as dbo

    Do you want me to look for something specific?

    When i am looking at the database properties for the user i am seeing only the db_datareader checked for the database role membership.

    Thanks

  • when you granted permissions to the user, what were the exact commands you issued? Or, in the case that you did it through the GUI, what was your process?

    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

  • I have done using GUI. The process i used is

    Security --> Loginss --> New Login --> Login with windows Authentication --> User Mapping --> Database --> db_datareader --> ok

    then when i go right click the database and select properties and look at the effective permissions for that user everything is being shown in that list.

    thanks

  • Hi Azzu,

    Has this database been associated with the user in the past?

    Reason for this line of thinking is, if a database has been migrated the exisitng users will remain within the database even though they have never been granted access on the new server.

    Just a thought.

  • Yes the database was associated with the user. but even if i take any new user and assign him the db_datareader role this issue is encountered.

    may be the this has got something to do at the database level. because as and when a user is assigned access to the database all the roles are being assigned irrespective of our choice.

    Thanks

  • I would start by removing the login and the user from the database (make sure it is gone). Even so far as to remove all the new users you have added.

    Next, verify that no orphaned users exist - remove them from the database if no Login exists for them. Do this for your user database and the master and model databases as well.

    Then stop and restart the service (if you can).

    Lastly, try to add the user back in, one by one.

    This may seem a bit overkill, but you want to make sure the user is not over-permissioned.

    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

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

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