Script to find out the statistics update date for all the indexes in the current database
sp_MSforeachtable 'sp_autostats "?"'
Script to update the statistics of all the indexes
EXEC sp_MSforeachtable 'UPDATE STATISTICS ? WITH FULLSCAN'
Problem
When connecting to SQL Server using Management Studio (SSMS), with the limited permissions, you do not see any user databases or receive Error 916 when expanding the database list from Object Explorer. The error message is “The server principal “Login Name” is not able to access the database “database name” under the current security context. (Microsoft SQL Server, Error: 916).” In this tip I will explain the root cause of the issue and how to fix it.
Solution
http://www.mssqltips.com/sqlservertip/2761/sql-server-management-studio-error-916/
Dbcc Config is an undocumented DBCC command which returns SQL server configuration. Configuration details are stored in Master database file 1 and on page 10.You have to enable the trace flag 3604 to get the result.
dbcc traceon(3604) dbcc config dbcc page(master,1,10, 3)
CONFIG: DS_CONFIG @0x032D6060 cconfsz = 8 cmajor = 8 cminor = 0 crevision = 10 cbootsource = 2 crecinterval = 0 ccatalogupdates = 0 cmbSrvMemMin = 8 cmbSrvMemMax = 2147483647 cusrconnections = 0 cnetworkpcksize = 4096 ckbIndexMem = 0 cfillfactor = 0 ctapreten = 0 cwritestatus = 0 cfgpriorityboost = 0x0 cfgexposeadvparm = 0x1 cfglogintime = 20 cfgpss = 0 cfgpad = 4096 cfgxdes = 16 cfgaffinitymask = 0 cfgIOAffinityMask = 0 cfgbuf = 4362 cfgdes = 0 cfglocks = 0 cfgquerytime = 600 cfgcursorthrsh = -1 cfgrmttimeout = 10 cfg_dtc_rpcs = 0 cclkrate = 31250 cfg_max_text_repl_size = 65536 cfgupddate = 40947 cfgupdtime = 22332689 fRemoteAccess = 1 cfgbufminfree = 331 cnestedtriggers = 0x1 cdeflang = 0 cfgTransformNoiseWords = 0x0 cfgPrecomputeRank = 0x0 crossdbownchain = 0 cidCfg = 0x3400d008 cidCfgOld = 0x3400d008 cfgCutoffYear = 2049 cfgLangNeutralFT = 1033 maxworkthreads = 0 minworkthreads = 32 minnetworkthreads = 32 threadtimeout = 15 connectsperthread = 0 cusroptions = 0 exchcostthreshold = 5 maxdop = 0 cpwdpolicyupgrade = 0x1 cfServerTriggerRecursion = 1 cfDisableResultsetsFromTriggers = 0 cfgPHConnectTimeout = 60 CLREnabled = 0 cfgmaxcrawlrange = 4 ftSmallBufMin = 0 ftSmallBufMax = 100 ftLargeBufMin = 0 ftLargeBufMax = 100 RemoteDacEnabled = 0 CommCritComplianceEnabled = 0 EkmEnabled = 0 cUserInstanceTimeout = 0x3c cfgEnableUserInstances = 0x1 m_BackupCompressionDefault = 0x0 FilestreamAccessLevel = 0 OptimizeForAdhocWorkloads = 0 cchecksum = 1787 DBCC execution completed. If DBCC printed error messages, contact your system administrator. PAGE: (1:10) BUFFER: BUF @0x043659F8 bpage = 0x069A4000 bhash = 0x00000000 bpageno = (1:10) bdbid = 1 breferences = 0 bUse1 = 45452 bstat = 0x2c00009 blog = 0x43212159 bnext = 0x00000000 PAGE HEADER: Page @0x069A4000 m_pageId = (1:10) m_headerVersion = 1 m_type = 14 m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x200 m_objId (AllocUnitId.idObj) = 99 m_indexId (AllocUnitId.idInd) = 0 Metadata: AllocUnitId = 6488064 Metadata: PartitionId = 0 Metadata: IndexId = 0 Metadata: ObjectId = 99 m_prevPage = (0:0) m_nextPage = (0:0) pminlen = 0 m_slotCnt = 1 m_freeCnt = 0 m_freeData = 8190 m_reservedCnt = 0 m_lsn = (0:0:1) m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0 m_tornBits = -1715479820 Allocation Status GAM (1:2) = ALLOCATED SGAM (1:3) = NOT ALLOCATED PFS (1:1) = 0x64 MIXED_EXT ALLOCATED 100_PCT_FULL DIFF (1:6) = CHANGED ML (1:7) = NOT MIN_LOGGED Slot 0, Offset 0x60, Length 9, DumpStyle BYTE Record Type = PRIMARY_RECORD Record Attributes = Record Size = 9 Memory Dump @0x6300A060 00000000: 00000800 fb060000 08†††††††††††††††††....û.... DS_CONFIG @0x6300A060 cconfsz = 8 cmajor = 8 cminor = 0 crevision = 10 cbootsource = 2 crecinterval = 0 ccatalogupdates = 0 cmbSrvMemMin = 0 cmbSrvMemMax = 2147483647 cusrconnections = 0 cnetworkpcksize = 4096 ckbIndexMem = 0 cfillfactor = 0 ctapreten = 0 cwritestatus = 0 cfgpriorityboost = 0x0 cfgexposeadvparm = 0x1 cfglogintime = 20 cfgpss = 0 cfgpad = 4096 cfgxdes = 16 cfgaffinitymask = 0 cfgIOAffinityMask = 0 cfgbuf = 4362 cfgdes = 0 cfglocks = 0 cfgquerytime = 600 cfgcursorthrsh = -1 cfgrmttimeout = 10 cfg_dtc_rpcs = 0 cclkrate = 31250 cfg_max_text_repl_size = 65536 cfgupddate = 40947 cfgupdtime = 22332689 fRemoteAccess = 1 cfgbufminfree = 331 cnestedtriggers = 0x1 cdeflang = 0 cfgTransformNoiseWords = 0x0 cfgPrecomputeRank = 0x0 crossdbownchain = 0 cidCfg = 0x3400d008 cidCfgOld = 0x3400d008 cfgCutoffYear = 2049 cfgLangNeutralFT = 1033 maxworkthreads = 0 minworkthreads = 32 minnetworkthreads = 32 threadtimeout = 15 connectsperthread = 0 cusroptions = 0 exchcostthreshold = 5 maxdop = 0 cpwdpolicyupgrade = 0x1 cfServerTriggerRecursion = 1 cfDisableResultsetsFromTriggers = 0 cfgPHConnectTimeout = 60 CLREnabled = 0 cfgmaxcrawlrange = 4 ftSmallBufMin = 0 ftSmallBufMax = 100 ftLargeBufMin = 0 ftLargeBufMax = 100 RemoteDacEnabled = 0 CommCritComplianceEnabled = 0 EkmEnabled = 0 cUserInstanceTimeout = 0x3c cfgEnableUserInstances = 0x1 m_BackupCompressionDefault = 0x0 FilestreamAccessLevel = 0 OptimizeForAdhocWorkloads = 0 cchecksum = 1787 DBCC execution completed. If DBCC printed error messages, contact your system administrator.
You can use below simple technique to check whether lock pages in memory is enabled or not. If lock pages in memory is enabled you can see the “Using locked pages for buffer pool” message in the SQL Server error log.
exec xp_readerrorlog 0, 1, 'locked pages'
To check if it is disabled. You have to check for the “Address Windowing Extensions (AWE) requires the ‘lock pages in memory’ privilege which is not currently present in the access token of the process.” message.
exec xp_readerrorlog 0, 1, 'lock pages in memory'
SQL Server 2005 onward, we can create the roles in database. You can follow below simple script to grant the EXECUTE permission to user.
-- creating the database role CREATE ROLE Database_Executor -- granting the execute permission to database role GRANT EXECUTE TO Database_Executor -- Here I am granting the SQLDBPool login execute permission USE [DBName] GO CREATE USER [SQLDBPool] FOR LOGIN [sqldbpool] GO USE [DBName] GO EXEC sp_addrolemember N'Database_Executor', N'sqldbpool' GO
aiDeeva.com 