December 1, 2006 at 5:34 pm
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.
December 1, 2006 at 7:31 pm
The user which can create proc. may be the member of some other role which has higher access.
MohammedU
Microsoft SQL Server MVP
December 1, 2006 at 9:14 pm
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).
December 1, 2006 at 11:28 pm
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
December 2, 2006 at 6:35 am
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.
December 2, 2006 at 9:24 am
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