February 23, 2010 at 12:33 am
Hi, I have the following weird problem.
Client is using an application that stores data in several SQL databases. The application connects to the SQL server using an account it creates during installation. This account is assigned the sysadmin role. So far, so good.
Now, there's another party that connects to some of the databases from outside the company network. For this party, I've created a user on the SQL server, assigned it only the public role, then created user mapping to individual databases and gave him appropriate access there (usually db_datareader and db_datawriter besides the default public database role).
The problem is that the user loses the mapping to some of the databases sometimes. It happens only on some of the DBs they have access to and it happens irregularly.
Any idea what could cause this? I assume the user can't somehow remove or break the mapping when he's only assigned the public server role, right?
Application vendor claims that the app can't be doing that.
Is there a way how to track this or find the cause?
Note: my user is assigned the sysadmin role, but I certainly don't change the permissions and the only other account with the sysadmin role is the user that the application uses to connect to the SQL server
Note 2: if it's important, SQL Server 2008 Express on Windows 2008 R2 server
February 23, 2010 at 11:49 am
The only time I have seen something like this happen, was when the application DBAs where restoring test scenario DB backups over the existing databases, without telling us SQL Server SysAdmins what they were doing. This would of course wipe out all of our later user mappings to those databases. So, you might want to check for something like that...
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 23, 2010 at 11:53 am
In general, since you do not know who is doing this, you should not yet try to analyze the permissions and rights of the situation.
Rather, I think that you should probably try to catch the presumed "DROP USER" command with either a DB DDL Trigger, or Event Notification (of DROP USER).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 24, 2010 at 7:02 pm
Thank you, I'll try look into that.
February 25, 2010 at 6:48 am
If it is due to database being restored you can check the restore history of the databases by querying the msdb system tables.
Thomas LaRock put an article on mssqltips.com that will give you the username that restored it as well. If the time the DB was restored coincides with the start of the issue...
Note: This query will only work if the history has not be cleared from the msdb system tables.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
June 6, 2012 at 8:48 am
Were you ever able to resolve this issue? I am seeing the same thing and have been unable to determine the cause.
June 6, 2012 at 1:22 pm
Have you tried any of the recommendations above?
June 6, 2012 at 2:59 pm
We have looked thru all the code for a drop user or alter role and have found none. We have not added event notification on these two commands yet. Probably our next step.
It does look like a Drop User since the account disappears from the database security - she ends up with just the public role. Her roles are used by an application that often creates them for a user - not in this case - so we think it may also be dropping them. We added a role to the user to determine whether just the application specific roles are disappearing or they all are. Very intermittent and other users have the same roles and are being left intact.
Not much out there about this issue outside the problem with restore which is not the case here.
June 7, 2012 at 6:03 am
See if this turns anything up. It looks through the default trace for any security events (excluding non-repair checkdbs). You may want to exclude some other things. I was just trying to track down user action so I also excluded the SQL service account and our monitoring application. And, as a disclaimer, this hasn't been fully tested so I wouldn't rely 100% on the CheckDB logic.
DECLARE @trace_path VARCHAR(MAX)
SELECT @trace_path = PATH FROM sys.traces WHERE id = 1
SELECT @trace_path = SUBSTRING(@trace_path,1,PATINDEX('%log_[0-9]%',@trace_path)+2) + '.trc'
SELECT t.DatabaseName, t.starttime, t.HostName, te.name, t.LoginName, t.ApplicationName,
EventSubclassInfo = (
SELECT subclass_name
FROM sys.trace_subclass_values tsv
WHERE te.trace_event_id = tsv.trace_event_id
AND t.EventSubClass = tsv.subclass_value
AND tsv.trace_column_id = 21
),
TargetLoginName,
RoleName,
t.TextData,
t.Success
FROM fn_trace_gettable(@trace_path, NULL) t
INNER JOIN sys.trace_events te ON t.EventClass = te.trace_event_id
INNER JOIN sys.trace_categories tc ON te.category_id = tc.category_id
WHERE tc.name = 'Security Audit'
AND te.name NOT IN ('Audit Server Alter Trace Event','Audit Backup/Restore Event')
AND (
t.TextData NOT LIKE 'DBCC CHECKDB%' OR (
t.TextData LIKE 'DBCC CHECKDB%' AND t.TextData LIKE '%REPAIR%'
)
)
ORDER BY starttime DESC
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply