user with db_datareader role that can create and drop stored procedures

  • I have two users both have exactly the same rights. As far as I can see in the SYSUSERS table both records are identical for both users. But one can create and drop stored proceudres while the other one can not (as expected).

    Please anybody has an idea of what is going on?

    Thank you very much in advance for your help.

     

  • The user which can create proc. may be the member of some other role which has higher access.

     

    MohammedU
    Microsoft SQL Server MVP

  • Well, I have checked the server roles and the user is member of none of these roles. The same thing for database roles, as I said when checking the sysuser table for both uid's I get identical results (except the sid, obviously).

  • I think some one might have granted the execlusive CREATE PROCEDURE permissons to the login.

    GRANT CREATE PROCEDURE TO <User name>

    Execute "Revoke CREATE PROCEDURE TO <User name>" and see what happens.

    Note: When you grant CREATE PROCEDURE permissions I don't think it is going effect anything in SYSUSERS table.

     

    MohammedU
    Microsoft SQL Server MVP

  • Thank you very much for the idea. REVOKE did not do it. I even had to go the further step of DENY. Now the [User] cannot create objects (procedures, tables)

    However, the [User] can still ALTER and DROP existing objects. In reallity I have never seen anything like this before. We just recreated the login of the [User] to the Server (via WIndows Authentication) and the [User] in the database, last night with just db_datareader rights and even stopped and restarted SQL Server in the event that some permissions were still cached somehow, somewhere. I am completely confused.

  • And you examined every Windows group the user was a member of to see if any of those groups had that access? Is the NT id in the local admin group of the server? Also.. if you changed the group membership (Windows) for the user you need to make sure that the login is not logged into the domain anywhere or the changes will not materialize.. the ID needs to reauthenticate to the domain to grab a new token.

    Thanks..

    Rich

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

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