May 21, 2008 at 5:31 pm
A tale of two permission levels. If I give the login sysadmin level access it can create a database snapshot just fine. If it has dbcreator access the attempt to create the snapshot ends in an access violation. No errors, just the AV and a stack dump.
Windows Event viewer comes up with a event id 5000 from SQLDUMPER: sql90exception64, P1 sqlserver.exe
...
Any clues?
May 21, 2008 at 6:55 pm
Can you confirm whether there is sufficient folder permission to create the sparse file ? Please check the if that particular login has permissions to the NTFS folder permission to create the sparse file..
[font="Verdana"]- Deepak[/font]
May 22, 2008 at 12:24 am
Sounds like you may have hit a bug.
What's your version, edition and OS? Can you zip and attach the text file that the dump creates to a post please?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 22, 2008 at 10:43 am
I'm figuring it's either a bug or some obscure setting I'm missing.
Enterprise 64-bit edition
Version is: 9.00.3228.0
This is with a SQL Server login, not a Windows login.
Snapshot is being created from a mirror. I have a nightly running job that's been doing this for several months. This script is for "custom" / "on demand" snapshots.
There are two dumps in the zip file.
May 22, 2008 at 10:58 am
Generally for access violations you should contact Microsoft's Customer Support Services (formerly known as PSS) as they have much more chance of solving this than we do. It does cost, but they waive the fees if the problem is as of a bug in SQL Server.
The AV is definitely from within SQL Server itself
(Exception Address = 0000000001707E73 Module(sqlservr+0000000000707E73)). Without the actual mdmp, a debugger and the symbols, there's no way to get more precise.
It looks like you're on a pretty recent build of 2005. 3228 is cumulative update 6. You may want to test out CU 7, see if it resolves the problem
Since this is a mirror, are you checking to ensure that the mirror DB is synchronised before creating the snapshot?
What's the script for "ssp_CreateOlapSnapCustom"?
Oh, and it's the permissions of the SQL Server service account that matter, not the permissions (if any) of the user connecting.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 12, 2012 at 12:26 am
Pam Brisjar (5/22/2008)
Snapshot is being created from a mirror. I have a nightly running job that's been doing this for several months. This script is for "custom" / "on demand" snapshots.There are two dumps in the zip file.
I know its an older post but the answer may help someone who encounters this issue in the future.
From Create a Database Snapshot (Transact-SQL)
Permissions
Any user who can create a database can create a database snapshot; however, to create a snapshot of a mirror database, you must be a member of the sysadmin fixed server role.
So, the account must be granted "sysadmin"inorder to create a db snapshot from a mirrored database.
Prashant
Check out my blog at http://sqlactions.com
June 12, 2012 at 4:13 am
sqlactions (6/12/2012)
PermissionsAny user who can create a database can create a database snapshot; however, to create a snapshot of a mirror database, you must be a member of the sysadmin fixed server role.
So, the account must be granted "sysadmin"inorder to create a db snapshot from a mirrored database.
You are aware that Access Violations have nothing whatsoever to do with SQL permissions? An Access Violation is a memory error, when an application accesses memory that does not belong to it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 12, 2012 at 6:23 am
GilaMonster (6/12/2012)
You are aware that Access Violations have nothing whatsoever to do with SQL permissions?
Nearly anything can cause an Access Violation (AV) in SQL Server. In this particular case, it was 100% SQL permission which caused an AV. If you look at the exception context in the dump file, you will see a call to function sqlservr!HoBtFactory::DirtyLockResourceLookup raied an AV. The issue is reproducible. If you’d like to, just set up a database mirroring and try to create a snapshot against the mirror database with a non-sysadmin user. You will see an AV. Be advised, the SQL Server build should be lower than SQL 2008 CU2(SP1) or SQL 2008 CU5(RTM). The issue was fixed (behaviour changed, AV is not raised in the fixed versions) in the said builds.
Actually, it’s not just the creation of a snapshot against mirror db that would result in an AV. If you just try to query sys.dm_database_encryption_keys that would also result in an AV. The problem is that non-sysadmin users can’t access the bit values (in sys.dm_database_encryption_keys or sys.databases) for a databases which has yet not fully recovered. Mirrored database is also the one which stays in“Restoring” state. So when a non-sysadmin user tries to read the bit values for such database a null pointer exception is throws as you may find in the call stack of the offending thread in the minidump.
On a sidenote, look at this blog[/url] where I demonstrate how a SQL native backup thread may cause an AV.
An Access Violation is a memory error, when an application accesses memory that does not belong to it.
Technically speaking, SQL Server generates an AV when a thread tries to access a memory region marked as “NO_ACCESS” or when a thread tries to write into a memory region marked as “READ_ONLY”. Practically, you’d see an AV raised by any kind of operation. It’s NOT only that the AV would be raised when “application accesses memory that does not belong to it”
Prashant
Check out my blog at http://sqlactions.com
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply