July 25, 2009 at 2:58 am
Hi, im not a powerfull dba, more than that - im not dba at all, but i have a question
I have a database with a set of user-defined roles and two of them are causing following problem...
If i include this roles for a user, he can't connect to a databse
if i don't - he can.
But this roles grant select, update, insert for a few tables only.
Plz...i need advice on what could be wrong...where should i dig to find out a reason of this trouble?
July 25, 2009 at 6:52 am
1. check how is the author for those two roles.?
2. Why you created those two roles?
3. What is the error message you are getting while connecting to SQL Server.?
4. What is the error message you are seeing in SQL Server ERRORLOG file/eventvwr.?
5. Are you connecting locally or remotely?
Rajesh Kasturi
July 25, 2009 at 8:21 am
1. I don't know anything about authors for those roles...sorry, but if you ask about owner, then dbo
2. Those roles gives permissions for two additional tables and thus enables additional features in front-end application
3. There is no error msg when user connects to the server. As for getting access to a database the message of MSSMS is following: "The database is not accessible.(Object Explorer)"
5. Both variants gives same error.
and the last one 4. SQL server error log:
2009-07-25 15:02:33.46 spid59 Using 'dbghelp.dll' version '4.0.5'
2009-07-25 15:02:33.46 spid59 ***Stack Dump being sent to C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\LOG\SQLDump0007.txt
2009-07-25 15:02:33.46 spid59 SqlDumpExceptionHandler: Process 59 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
2009-07-25 15:02:33.46 spid59 * *******************************************************************************
2009-07-25 15:02:33.46 spid59 *
2009-07-25 15:02:33.46 spid59 * BEGIN STACK DUMP:
2009-07-25 15:02:33.46 spid59 * 07/25/09 15:02:33 spid 59
2009-07-25 15:02:33.46 spid59 *
2009-07-25 15:02:33.46 spid59 *
2009-07-25 15:02:33.46 spid59 * Exception Address = 0000000001C74E2C Module(sqlservr+00000000014B4E2C)
2009-07-25 15:02:33.46 spid59 * Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
2009-07-25 15:02:33.46 spid59 * Access Violation occurred reading address 0000000000000000
2009-07-25 15:02:33.46 spid59 * Input Buffer 336 bytes -
2009-07-25 15:02:33.46 spid59 * EXECUTE AS LOGIN = N'tiranitzar'; SELECT permission_name
2009-07-25 15:02:33.46 spid59 * AS [Permission] FROM fn_my_permissions(N'[ ]', N'SCHEMA') ORDER BY p
2009-07-25 15:02:33.46 spid59 * ermission_name; REVERT;
2009-07-25 15:02:33.46 spid59 *
2009-07-25 15:02:33.46 spid59 *
2009-07-25 15:02:33.46 spid59 * MODULE BASE END SIZE
2009-07-25 15:02:33.46 spid59 * sqlservr 00000000007C0000 0000000004003FFF 03844000
2009-07-25 15:02:33.46 spid59 * ntdll 0000000077620000 00000000777C7FFF 001a8000
2009-07-25 15:02:33.46 spid59 * kernel32 0000000077400000 000000007751DFFF 0011e000
2009-07-25 15:02:33.46 spid59 * KERNELBASE 000007FEFD640000 000007FEFD6A8FFF 00069000
2009-07-25 15:02:33.46 spid59 * MSVCR80 0000000073CF0000 0000000073DB8FFF 000c9000
2009-07-25 15:02:33.46 spid59 * msvcrt 000007FEFEBE0000 000007FEFEC7EFFF 0009f000
2009-07-25 15:02:33.46 spid59 * MSVCP80 0000000073970000 0000000073A78FFF 00109000
2009-07-25 15:02:33.46 spid59 * ADVAPI32 000007FEFEA60000 000007FEFEB3AFFF 000db000
2009-07-25 15:02:33.46 spid59 * sechost 000007FEFE740000 000007FEFE75EFFF 0001f000
2009-07-25 15:02:33.46 spid59 * RPCRT4 000007FEFED90000 000007FEFEEBEFFF 0012f000
2009-07-25 15:02:33.46 spid59 * sqlos 0000000074DB0000 0000000074DB6FFF 00007000
2009-07-25 15:02:33.46 spid59 * USER32 0000000077520000 0000000077619FFF 000fa000
2009-07-25 15:02:33.46 spid59 * GDI32 000007FEFF510000 000007FEFF576FFF 00067000
2009-07-25 15:02:33.46 spid59 * LPK 000007FEFEEF0000 000007FEFEEFDFFF 0000e000
2009-07-25 15:02:33.46 spid59 * USP10 000007FEFF810000 000007FEFF8D9FFF 000ca000
2009-07-25 15:02:33.46 spid59 * CRYPT32 000007FEFD710000 000007FEFD875FFF 00166000
2009-07-25 15:02:33.46 spid59 * MSASN1 000007FEFD630000 000007FEFD63EFFF 0000f000
2009-07-25 15:02:33.46 spid59 * Secur32 000007FEFD280000 000007FEFD28AFFF 0000b000
2009-07-25 15:02:33.46 spid59 * SSPICLI 000007FEFD430000 000007FEFD454FFF 00025000
2009-07-25 15:02:33.46 spid59 * MSWSOCK 000007FEFCDC0000 000007FEFCE13FFF 00054000
2009-07-25 15:02:33.46 spid59 * WS2_32 000007FEFD960000 000007FEFD9ACFFF 0004d000
2009-07-25 15:02:33.46 spid59 * NSI 000007FEFF580000 000007FEFF587FFF 00008000
2009-07-25 15:02:33.46 spid59 * pdh 000007FEF7410000 000007FEF745DFFF 0004e000
2009-07-25 15:02:33.46 spid59 * SHLWAPI 000007FEFF110000 000007FEFF17FFFF 00070000
2009-07-25 15:02:33.46 spid59 * USERENV 000007FEFC8E0000 000007FEFC8FDFFF 0001e000
2009-07-25 15:02:33.46 spid59 * profapi 000007FEFD590000 000007FEFD59EFFF 0000f000
2009-07-25 15:02:33.46 spid59 * SHELL32 000007FEFD9B0000 000007FEFE734FFF 00d85000
2009-07-25 15:02:33.46 spid59 * WINMM 000007FEFB160000 000007FEFB19AFFF 0003b000
2009-07-25 15:02:33.46 spid59 * opends60 0000000074E70000 0000000074E77FFF 00008000
2009-07-25 15:02:33.46 spid59 * NETAPI32 000007FEFB660000 000007FEFB675FFF 00016000
2009-07-25 15:02:33.46 spid59 * netutils 000007FEFB650000 000007FEFB65BFFF 0000c000
2009-07-25 15:02:33.46 spid59 * srvcli 000007FEFD070000 000007FEFD092FFF 00023000
2009-07-25 15:02:33.46 spid59 * wkscli 000007FEFB630000 000007FEFB644FFF 00015000
2009-07-25 15:02:33.46 spid59 * LOGONCLI 000007FEFCC10000 000007FEFCC3FFFF 00030000
2009-07-25 15:02:33.46 spid59 * SAMCLI 000007FEFB610000 000007FEFB623FFF 00014000
2009-07-25 15:02:33.46 spid59 * BatchParser 0000000074E40000 0000000074E6BFFF 0002c000
2009-07-25 15:02:33.46 spid59 * IMM32 000007FEFEEC0000 000007FEFEEEDFFF 0002e000
2009-07-25 15:02:33.46 spid59 * MSCTF 000007FEFEC80000 000007FEFED88FFF 00109000
2009-07-25 15:02:33.46 spid59 * psapi 00000000777F0000 00000000777F6FFF 00007000
2009-07-25 15:02:33.48 spid59 * instapi10 00000000738E0000 00000000738ECFFF 0000d000
2009-07-25 15:02:33.48 spid59 * cscapi 000007FEF6B00000 000007FEF6B0EFFF 0000f000
2009-07-25 15:02:33.48 spid59 * sqlevn70 0000000074160000 000000007435CFFF 001fd000
2009-07-25 15:02:33.48 spid59 * CRYPTSP 000007FEFCE20000 000007FEFCE36FFF 00017000
2009-07-25 15:02:33.48 spid59 * rsaenh 000007FEFCB20000 000007FEFCB66FFF 00047000
2009-07-25 15:02:33.48 spid59 * CRYPTBASE 000007FEFD4C0000 000007FEFD4CEFFF 0000f000
2009-07-25 15:02:33.48 spid59 * BROWCLI 000007FEF5730000 000007FEF5741FFF 00012000
2009-07-25 15:02:33.48 spid59 * AUTHZ 000007FEFD030000 000007FEFD05FFFF 00030000
2009-07-25 15:02:33.48 spid59 * MSCOREE 000007FEFA030000 000007FEFA095FFF 00066000
2009-07-25 15:02:33.48 spid59 * ole32 000007FEFF180000 000007FEFF382FFF 00203000
2009-07-25 15:02:33.48 spid59 * credssp 000007FEFC9F0000 000007FEFC9F8FFF 00009000
2009-07-25 15:02:33.48 spid59 * msv1_0 000007FEFCD50000 000007FEFCDA0FFF 00051000
2009-07-25 15:02:33.48 spid59 * cryptdll 000007FEFD120000 000007FEFD133FFF 00014000
2009-07-25 15:02:33.48 spid59 * kerberos 000007FEFCE40000 000007FEFCEF2FFF 000b3000
2009-07-25 15:02:33.48 spid59 * schannel 000007FEFCBB0000 000007FEFCC07FFF 00058000
2009-07-25 15:02:33.48 spid59 * security 0000000074100000 0000000074102FFF 00003000
2009-07-25 15:02:33.48 spid59 * RsFxFt 0000000074E30000 0000000074E38FFF 00009000
2009-07-25 15:02:33.48 spid59 * OLEAUT32 000007FEFEF00000 000007FEFEFD6FFF 000d7000
2009-07-25 15:02:33.48 spid59 * ftimport 0000000060000000 0000000060024FFF 00025000
2009-07-25 15:02:33.48 spid59 * MSFTE 000000004D060000 000000004D40DFFF 003ae000
2009-07-25 15:02:33.48 spid59 * VERSION 000007FEFC6F0000 000007FEFC6FBFFF 0000c000
2009-07-25 15:02:33.48 spid59 * dbghelp 0000000072200000 000000007235DFFF 0015e000
2009-07-25 15:02:33.48 spid59 * WINTRUST 000007FEFD880000 000007FEFD8B9FFF 0003a000
2009-07-25 15:02:33.48 spid59 * ncrypt 000007FEFCFE0000 000007FEFD02DFFF 0004e000
2009-07-25 15:02:33.48 spid59 * bcrypt 000007FEFCFB0000 000007FEFCFD1FFF 00022000
2009-07-25 15:02:33.48 spid59 * ntmarta 000007FEFB370000 000007FEFB39CFFF 0002d000
2009-07-25 15:02:33.48 spid59 * WLDAP32 000007FEFF8E0000 000007FEFF92FFFF 00050000
2009-07-25 15:02:33.48 spid59 * wship6 000007FEFCDB0000 000007FEFCDB6FFF 00007000
2009-07-25 15:02:33.48 spid59 * wshtcpip 000007FEFC7C0000 000007FEFC7C6FFF 00007000
2009-07-25 15:02:33.48 spid59 * ntdsapi 000007FEFB3F0000 000007FEFB416FFF 00027000
2009-07-25 15:02:33.48 spid59 * bcryptprimitives 000007FEFCA60000 000007FEFCAAAFFF 0004b000
2009-07-25 15:02:33.48 spid59 * SAMLIB 000007FEFC000000 000007FEFC01CFFF 0001d000
2009-07-25 15:02:33.48 spid59 * CLBCatQ 000007FEFE760000 000007FEFE7F8FFF 00099000
2009-07-25 15:02:33.48 spid59 * sqlncli10 0000000071E30000 0000000072136FFF 00307000
2009-07-25 15:02:33.48 spid59 * COMCTL32 000007FEF2E00000 000007FEF2E9FFFF 000a0000
2009-07-25 15:02:33.48 spid59 * COMDLG32 000007FEFF770000 000007FEFF805FFF 00096000
2009-07-25 15:02:33.48 spid59 * SQLNCLIR10 0000000074D70000 0000000074DA6FFF 00037000
2009-07-25 15:02:33.48 spid59 * mscorwks 000007FEF9680000 000007FEFA02DFFF 009ae000
2009-07-25 15:02:33.48 spid59 * mscorlib.ni 000007FEF87A0000 000007FEF967AFFF 00edb000
2009-07-25 15:02:33.48 spid59 * mscorsec 00000642FFAF0000 00000642FFB08FFF 00019000
2009-07-25 15:02:33.48 spid59 * imagehlp 000007FEFEB40000 000007FEFEB56FFF 00017000
2009-07-25 15:02:33.48 spid59 * GPAPI 000007FEFC8C0000 000007FEFC8DAFFF 0001b000
2009-07-25 15:02:33.48 spid59 * cryptnet 000007FEF56C0000 000007FEF56E4FFF 00025000
2009-07-25 15:02:33.48 spid59 * SensApi 000007FEF3150000 000007FEF3158FFF 00009000
2009-07-25 15:02:33.48 spid59 * Cabinet 000007FEF2550000 000007FEF256AFFF 0001b000
2009-07-25 15:02:33.48 spid59 * DEVRTL 000007FEFC900000 000007FEFC911FFF 00012000
2009-07-25 15:02:33.48 spid59 * SqlAccess 0000000074BA0000 0000000074C06FFF 00067000
2009-07-25 15:02:33.48 spid59 * mscorjit 000007FEF2C70000 000007FEF2DF3FFF 00184000
2009-07-25 15:02:33.48 spid59 * System.Data 0000000054160000 0000000054461FFF 00302000
2009-07-25 15:02:33.48 spid59 * System.ni 000007FEF7D80000 000007FEF879DFFF 00a1e000
2009-07-25 15:02:33.48 spid59 * System.Transactions 000000004D410000 000000004D458FFF 00049000
2009-07-25 15:02:33.48 spid59 * System.Security.ni 000007FEECCE0000 000007FEECDC4FFF 000e5000
2009-07-25 15:02:33.48 spid59 * System 0000000071970000 0000000071C79FFF 0030a000
2009-07-25 15:02:33.48 spid59 * System.Security 0000000074110000 0000000074151FFF 00042000
2009-07-25 15:02:33.48 spid59 * System.Xml 0000000061540000 0000000061737FFF 001f8000
2009-07-25 15:02:33.48 spid59 * apphelp 000007FEFD460000 000007FEFD4B6FFF 00057000
2009-07-25 15:02:33.48 spid59 * xpstar 0000000071CE0000 0000000071D67FFF 00088000
2009-07-25 15:02:33.48 spid59 * SQLSCM 0000000074E20000 0000000074E2BFFF 0000c000
2009-07-25 15:02:33.48 spid59 * ODBC32 000007FEEE950000 000007FEEE9FEFFF 000af000
2009-07-25 15:02:33.48 spid59 * ATL80 0000000073CA0000 0000000073CBEFFF 0001f000
2009-07-25 15:02:33.48 spid59 * odbcint 0000000073240000 0000000073277FFF 00038000
2009-07-25 15:02:33.48 spid59 * xpstar 0000000073210000 0000000073234FFF 00025000
2009-07-25 15:02:33.48 spid59 * xplog70 0000000062790000 000000006279FFFF 00010000
2009-07-25 15:02:33.48 spid59 * xplog70 0000000060FC0000 0000000060FC1FFF 00002000
2009-07-25 15:02:33.48 spid59 * dbghelp 000000005ACB0000 000000005AE0DFFF 0015e000
2009-07-25 15:02:33.48 spid59 *
2009-07-25 15:02:33.48 spid59 * P1Home: 00000000507DE010: 00000000C0000005 0000000000000000 0000000001C74E2C 0000000000000002 0000000000000000 0000000000000000
2009-07-25 15:02:33.48 spid59 * P2Home: 00000000507DDB20: 00000000507DE010 00000000507DDB20 00000000007C0000 00000000507DF340 0000000000000000 000000000C29DA90
2009-07-25 15:02:33.48 spid59 * P3Home: 00000000007C0000: 0000000300905A4D 0000FFFF00000004 00000000000000B8 0000000000000040 0000000000000000 0000000000000000
2009-07-25 15:02:33.48 spid59 * P4Home: 00000000507DF340: 000000000FB82C90 0000000000D0CC96 FFFFFFFFFFFFFFFE 0000000000000000 00000000507DF380 0000000000A263BC
2009-07-25 15:02:33.48 spid59 * P5Home: 0000000000000000:
2009-07-25 15:02:33.48 spid59 * P6Home: 000000000C29DA90: 000000000C29DA90 000000000C29DA90 0000000000000001 000000000C29DF80 000000000C29D850 0000000000000000
2009-07-25 15:02:33.48 spid59 * ContextFlags: 000000000010001F: 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000
2009-07-25 15:02:33.48 spid59 * MxCsr: 0000000000001FA0:
2009-07-25 15:02:33.48 spid59 * SegCs: 0000000000000033:
2009-07-25 15:02:33.48 spid59 * SegDs: 000000000000002B:
2009-07-25 15:02:33.48 spid59 * SegEs: 000000000000002B:
2009-07-25 15:02:33.48 spid59 * SegFs: 0000000000000053:
2009-07-25 15:02:33.48 spid59 * SegGs: 000000000000002B:
2009-07-25 15:02:33.48 spid59 * SegSs: 000000000000002B:
2009-07-25 15:02:33.48 spid59 * EFlags: 0000000000010246: 0000000000000000 0000000101580000 0000000102680000 0000000102780000 0000000000000000 0000001000000000
2009-07-25 15:02:33.48 spid59 * Rax: 0000000000000000:
2009-07-25 15:02:33.48 spid59 * Rcx: 00000000080AC080: 00000000007D08E0 0000000000002000 00000000000000B8 000000000077B4C0 0000000100000010 000000000804DB00
2009-07-25 15:02:33.48 spid59 * Rdx: 00000000098C2678: 0000000000867160 0000000000CC4938 000000000FBF9550 0000000000000000 0000000000000000 00300050000001F0
2009-07-25 15:02:33.48 spid59 * Rbx: 0000000000000000:
2009-07-25 15:02:33.48 spid59 * Rsp: 00000000507DE0E0: 000000000000400A 00000000098C2080 0000000000000000 00000000080AC080 0000000000000001 00000000507DE1E8
2009-07-25 15:02:33.48 spid59 * Rbp: 00000000080AC080: 00000000007D08E0 0000000000002000 00000000000000B8 000000000077B4C0 0000000100000010 000000000804DB00
2009-07-25 15:02:33.48 spid59 * Rsi: 00000000507DE1E8: 000000000A236670 0000000000000003 0000000000000000 0000000000000000 0000000000000000 0000000000000000
2009-07-25 15:02:33.48 spid59 * Rdi: 0000000000000000:
2009-07-25 15:02:33.48 spid59 * R8: 0000000000000000:
2009-07-25 15:02:33.48 spid59 * R9: 00000000507DE1E8: 000000000A236670 0000000000000003 0000000000000000 0000000000000000 0000000000000000 0000000000000000
2009-07-25 15:02:33.48 spid59 * R10: 0000000000000001:
2009-07-25 15:02:33.48 spid59 * R11: 0000000000000001:
2009-07-25 15:02:33.48 spid59 * R12: 00000000098C2600: 00000000098C2080 000000000FBF9410 000000000FBF9550 00000000098C2850 0040000000000004 0000000000BCD368
2009-07-25 15:02:33.48 spid59 * R13: 0000000000000015:
2009-07-25 15:02:33.48 spid59 * R14: 000000000381A83C: C247614802F4D017 025BCF8161490285 614B0255CB39614A C580614C026ACDB2 026AD08A614D0294 614F0261CCD1614E
2009-07-25 15:02:33.48 spid59 * R15: 000000000381A832: 028ECDD26146022E 614802F4D0176147 CF8161490285C247 0255CB39614A025B 614C026ACDB2614B D08A614D0294C580
2009-07-25 15:02:33.48 spid59 * Rip: 0000000001C74E2C: 50FFCB8B48038B48 038B485573F83B58 D78BCB8B4801C783 8B48D58B486050FF 50244489C08B44CE 74C085FF04F7E7E8
2009-07-25 15:02:33.48 spid59 * *******************************************************************************
2009-07-25 15:02:33.48 spid59 * -------------------------------------------------------------------------------
2009-07-25 15:02:33.48 spid59 * Short Stack Dump
2009-07-25 15:02:33.48 spid59 0000000001C74E2C Module(sqlservr+00000000014B4E2C)
2009-07-25 15:02:33.48 spid59 0000000001C74E8C Module(sqlservr+00000000014B4E8C)
2009-07-25 15:02:33.48 spid59 0000000001C74E8C Module(sqlservr+00000000014B4E8C)
2009-07-25 15:02:33.48 spid59 000000000223CE20 Module(sqlservr+0000000001A7CE20)
2009-07-25 15:02:33.48 spid59 000000000223D8C6 Module(sqlservr+0000000001A7D8C6)
2009-07-25 15:02:33.48 spid59 00000000016B7BFE Module(sqlservr+0000000000EF7BFE)
2009-07-25 15:02:33.48 spid59 00000000022C3F8A Module(sqlservr+0000000001B03F8A)
2009-07-25 15:02:33.48 spid59 0000000000A6696F Module(sqlservr+00000000002A696F)
2009-07-25 15:02:33.48 spid59 00000000023CC7F2 Module(sqlservr+0000000001C0C7F2)
2009-07-25 15:02:33.48 spid59 000000000084FDFA Module(sqlservr+000000000008FDFA)
2009-07-25 15:02:33.48 spid59 0000000000850FE3 Module(sqlservr+0000000000090FE3)
2009-07-25 15:02:33.48 spid59 0000000000851499 Module(sqlservr+0000000000091499)
2009-07-25 15:02:33.48 spid59 0000000000853FF2 Module(sqlservr+0000000000093FF2)
2009-07-25 15:02:33.48 spid59 000000000084EBBB Module(sqlservr+000000000008EBBB)
2009-07-25 15:02:33.48 spid59 00000000007D2ABB Module(sqlservr+0000000000012ABB)
2009-07-25 15:02:33.48 spid59 00000000007D0FDA Module(sqlservr+0000000000010FDA)
2009-07-25 15:02:33.48 spid59 00000000007D2665 Module(sqlservr+0000000000012665)
2009-07-25 15:02:33.48 spid59 0000000000D7ABB0 Module(sqlservr+00000000005BABB0)
2009-07-25 15:02:33.48 spid59 0000000000D7C4B0 Module(sqlservr+00000000005BC4B0)
2009-07-25 15:02:33.48 spid59 0000000000D7A060 Module(sqlservr+00000000005BA060)
2009-07-25 15:02:33.48 spid59 0000000000D7A9EF Module(sqlservr+00000000005BA9EF)
2009-07-25 15:02:33.48 spid59 0000000073CF37D7 Module(MSVCR80+00000000000037D7)
2009-07-25 15:02:33.49 spid59 0000000073CF3894 Module(MSVCR80+0000000000003894)
2009-07-25 15:02:33.49 spid59 000000007741542D Module(kernel32+000000000001542D)
2009-07-25 15:02:33.49 spid59 000000007765BE51 Module(ntdll+000000000003BE51)
2009-07-25 15:02:33.51 spid59 Stack Signature for the dump is 0x00000001E91983EA
July 27, 2009 at 12:45 pm
Despite of my poor English can someone help me?
July 27, 2009 at 1:46 pm
Hi..
1.Check if you are able to login from atleast 1 login with the role assigned to it.
2.Try to create a test login assign the role to it and try to login.
To make sure that the roles do not have any problem.
September 6, 2009 at 5:18 am
I have forgotten to write, that I have solved the given problem. Really, roles were problem. The reason of it was database migration from 2000 sql server to 2005. It caused an error with some roles. After their removal and creation anew all problems have disappeared. Thanks for the help.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply