December 8, 2008 at 8:26 pm
Hi,
I have a sql 2005, where the user seems to be recreated automatically between 5pm and 9pm. This cause a major problem since the rights assigned to the user are lost. How I can find how & where the user are re-created?
I don't have any tasks running on the server at that time execpt a full backup.
Does someone already got a similar issue? How can I hook a sniffer to see what is hapenning?
Any help will be appreciated !
Rem
December 8, 2008 at 9:13 pm
What do you mean recreated? Are they dropped and recreated? How do you know? Are permissions lost?
If this happens consistently and you know roughly when, I'd set up a trace and see what is happening.
December 8, 2008 at 9:48 pm
i am wondering why to drop the user and recreate it with same permissions when you are taking full backup of the database. Rem, can you please tell the business requirement behind all this? It would be intersting to know the scenarios where one could use this technique.
December 8, 2008 at 10:06 pm
This cause a major problem since the rights assigned to the user are lost. How I can find how & where the user are re-created?
select * from master..syslogins
Run the above query and check the CreateDate, UpdateDate, etc.
Run Profiler to see what is going on.
December 9, 2008 at 12:01 am
Rem (12/8/2008)
Hi,I have a sql 2005, where the user seems to be recreated automatically between 5pm and 9pm. This cause a major problem since the rights assigned to the user are lost. How I can find how & where the user are re-created?
I don't have any tasks running on the server at that time execpt a full backup.
Does someone already got a similar issue? How can I hook a sniffer to see what is hapenning?
Any help will be appreciated !
Rem
why dont you use SQL Profiler if it is not productioin
kshitij kumar
kshitij@krayknot.com
www.krayknot.com
December 9, 2008 at 8:11 am
I think I didn't explain my problem in the right way. I have some users in a database that are losing their right in the database (db_datareader). This is happening only in one database on my server. I don't have anything running on the server that can change the right (as far as I know).
I will try to use SQLProfiler to see what is causing that. Should I user the TSQL template to kick the Profiler?
Meanwhile any one has never faced something similar?
Thanks for all your help/answer.
December 10, 2008 at 2:24 am
could the commands have been left in the middle of a stored procedure somewhere, probably put in during dev / testing?
I have seen this before but the command was granting execute permissions to a user because the developer had missed the GO command at the end of the proc before granting the permission so it ran the command every time......
Run a profiler at around that time to see what is running
_________________________________________________________________________________SQLGeordieWeb:- Jarrin ConsultancyBlog:- www.chrisjarrintaylor.co.ukTwitter:- @SQLGeordie
December 11, 2008 at 11:54 am
I might have found my problem. Someone added a copy object task in a SSIS project. The default parameters were still there as for as copy role, user and schema.
Hopefully this was the issue.
Thanks for your help guys.
Remy
December 11, 2008 at 2:08 pm
glad u got it sorted!
_________________________________________________________________________________SQLGeordieWeb:- Jarrin ConsultancyBlog:- www.chrisjarrintaylor.co.ukTwitter:- @SQLGeordie
December 11, 2008 at 2:30 pm
Yeah, was a pain. Hopefully I will be able to get ride of the silly script I wrote to assign role every 2 min.;)
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply