Sudden denial of permissions

Question

Sudden denial of permissions

Viewing 11 posts - 16 through 25 (of 25 total)

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

David Lester Hall of Fame Points: 3647 More actions · Answer 1

I will be attempting Brian's suggestion in a little while. To add to the oddness, another user in the same user group is able to open the same report with no issues.

Steve Jones - SSC Editor SSC Guru Points: 736160 More actions · Answer 2

Crazy.

After you test, what I'd also do is remove the user explicitly and make sure they're not in any other groups from Windows. Then let them connect through the group and test with profiler again.

george sibbald SSC Guru Points: 104210 More actions · Answer 3

were you able to give them higher level of permissions (ie dbo) and see what happens?

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

David Lester Hall of Fame Points: 3647 More actions · Answer 4

I have removed and recreated a user ID. I have removed and recreated the ODBC connection on the user device. These both still return the same permission issue.

We have a user who has the EM tool to create reports as well. This user is unable to open the database, with select permissions error on the sysobjects table. However, the same user with the same permissions is able to interact normally with a different database on the same server.

Steve Jones - SSC Editor SSC Guru Points: 736160 More actions · Answer 5

Interesting, perhaps someone has issued a deny on system tables? People need those in 2000 for quite a few things.

David Lester Hall of Fame Points: 3647 More actions · Answer 6

Would it be plausible that the security/permissions sys data has become corrupted or somehow flawed? On the same server, with the same user IDs in different DBs, everything works. If I script out a table in the problem DB, with its permissions, and create it in another DB, again the user is able to use the report just fine.

If I use TSQL for removing and adding a user ID to multiple DBs on the server, on the single database logins are a problem for the command fails, but works on other DBs.

Steve Jones - SSC Editor SSC Guru Points: 736160 More actions · Answer 7

It is possible. Perhaps run dbcc on the db? It will check system tables.

If you have a new db with same permissions, can you do a join to the original db (outer join) to see if things are missing for this user?

David Lester Hall of Fame Points: 3647 More actions · Answer 8

I may have finally found the common thread/problem.

I am using windows auth. I do set up each of our 70 users by user ID. However, years ago the windows admins here set up a few windows groups for departments for me. These then I set up in SQL and used these groups primarily for the permission maintainence. When a user in one of these groups logs in, it is their windows logon which appears in sp_who2, not the windows group.

This afternoon when I was testing some ideas, I scripted out permissions for a few objects and tried replicating the issue. All these windows groups failed, excepting the one for IT. Those users who were able to use a report were new to the company, and not always set up in these groups. Those who could vs could not access SQL were in either the IT windows group, or not in one of the other windows groups, or were hitting an object which had no permissions to the windows groups themselves.

I won't know for sure until the windows admins return tommarrow, but I think this is caused by the windows groups being deleted on the windows side, but not on the SQL side. All the users for these groups still exist on the windows side. So I would see the user having permission, and the user connecting to SQL. But I would guess objects which had been given permissions to be used by the windows groups, are having issues validating the user ID.

David Lester Hall of Fame Points: 3647 More actions · Answer 9

So how would SQL respond if a new windows group was created, which included all the windows IDs which were used for permissions in SQL. Then that ID for the windows group was added to SQL, but given no permissions, no deny or select permissions, and not be included in any roles, including public?

Steve Jones - SSC Editor SSC Guru Points: 736160 More actions · Answer 10

Not sure. I would have thought that an error would come through, or the permissions dropped.

You ought to be able to script permissons for the old group, then apply them to the new group and see if that fixes things. I might remove the old group as well.

David Lester Hall of Fame Points: 3647 More actions · Answer 11

I needed to ask. This afternoon we finally managed to get everything settled. However, we did notice between the time the SQL people working on this issue left last night, and this morning when everyone was back to normal, a user ID with that situation was no longer on the user list. The SQL group also noticed the day this all started, when we went looking for oddness, this ID we did not set up.

I am fairly sure our windows admins were working on something last night specific to active directory and windows groups, and it had been discussed last week. So it is possible they added it (not entirely strict job lines here) without setting up permissions. Thinking it would not cause any problems.

However, all users have all their normal functions restored. So the result of the issue is dealt with, and now we can breath easier as we find the cause, and set up preventions.

Thanks for all the great suggestions!