June 17, 2009 at 4:40 am
I've found this problem with user permission. My case is that I create a user 'abc' and assign 'public' and 'datareader' permission to this user. After a while, permissions are disappear. This happen to only 1 user while there are many users working on the same thing.
I cannot see how and when this case happen. Sometime it happen while user 'abc' trying to retrieve data. Sometime it just happen while they do nothing.
Any help would be appreciated. 🙂
June 17, 2009 at 10:44 am
Does the user disappear from the database or just the permissions? My guesses would be another admin revoking the permissions or the database being restored to a point in time before the user was created.
Greg
June 18, 2009 at 3:25 am
Greg, thanks for your reply.
It is permission that disappear. User still in the database.
Neither admin revoke the permission nor database restore happen.
Thing is we use another tool, which is Cognos, to connect to SQL database. It is able to connect and query data.
What happen is while we connected with database and query data using Cognos tool, sometime, permission disappear so I have to ask system admin to re-grant permission for me quite offen in a day.
If I use query analyzer to query data, there is no problem happen.
What I will try to test is
1. Create another database, connect to it and try to query data and see the result.
2. Create another user to connect to the same database and try to query data and see the result.
June 18, 2009 at 12:43 pm
You'd want to run a trace/Profiler for permission changes while using the tool. The tool must be doing something. SQL Server never loses permissions or changes them itself. Some user or process must be doing it.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
September 9, 2010 at 10:08 am
Has anyone found a resolution to this problem? I'm having the same issue.
SQL 2000 SP4
Windows 2003 SP2
I add a domain user and grant db access with db_datareader permissions. The next morning, my user complains of no access to db. I find that the users AD account is still present, but access to the db and subsequent db_datareader permissions are unchecked.
All other users/groups have no issue. My server admins have checked the DNS settings and found no problems.
dwg
September 9, 2010 at 10:25 am
Are you perhaps doing a restore of the database?
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
September 9, 2010 at 11:42 am
No restores, no dropping tables, etc. This is a fixed database that only experiences regular add/update/deletes to the data, and a daily full backup.
I currently have a trace running looking for "sp_revokedbaccess" in the text data.
September 9, 2010 at 11:59 am
You'll have to run a trace. AFAIK, without some action, there is no way permissions are changed on objects.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
September 10, 2010 at 10:00 am
Well since I was only looking for 'sp_revokedbaccess' in the text data, I let the trace run all night and this morning the permissions are gone and theres no usable data captured by the trace. I tested the trace by starting it and removing the access via EM to make sure it would give me the right results, but now it seems that there's something else going on since that never came up in the trace.
Another wierd aspect of this problem is that when I try to re-add the permissions to the db, SQL says the user already exists. Then when I go into the security at the db level and try to add db_datareader there, SQL tells me the user does not exist in the db. It's like the server and database are not on the same page!?
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply