user with db_datareader role that can create and drop stored procedures

Question

Post reply

user with db_datareader role that can create and drop stored procedures

diogenes1331

Default port

Points: 1420
More actions
December 1, 2006 at 5:34 pm

#62681

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.

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

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

MohammedU SSCoach Points: 19644 More actions · Answer 1

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

MohammedU
Microsoft SQL Server MVP

diogenes1331 Default port Points: 1420 More actions · Answer 2

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).

MohammedU SSCoach Points: 19644 More actions · Answer 3

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

diogenes1331 Default port Points: 1420 More actions · Answer 4

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.

Rich Hansell SSCrazy Points: 2346 More actions · Answer 5

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