This came up because a user had db_owner perms and it was only reading from a database and creating a table variable from it. I created a new user (on a test system) with read only permissions to test if the code would still work.…
Category: sql sever syntax
I wanted to find out what data types SQL Server auditing was using, so I put the auditing file results into a temp table and executed tempdb..sp_help on that temp table to get the schema. This will give you the following results: Here’s the expanded…
good to do b/c the bigger it is, the longer it will take to load into memory — USE msdb; GO EXEC sp_cycle_agent_errorlog;
https://www.red-gate.com/simple-talk/sql/performance/the-default-trace-in-sql-server-the-power-of-performance-and-security-auditing/ –enabling default trace sp_configure ‘show advanced options’, 1; GO RECONFIGURE; GO sp_configure ‘default trace enabled’, 1; GO RECONFIGURE; GO –querying default trace SELECT * FROM sys.fn_trace_gettable(CONVERT(VARCHAR(150), ( SELECT TOP 1 f.[value] FROM sys.fn_trace_getinfo(NULL) f WHERE f.property = 2 )), DEFAULT) T JOIN sys.trace_events TE…
SELECT dest.text FROM sys.dm_exec_query_stats AS deqs CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest WHERE deqs.last_execution_time > ’12/28/15 11:00′ AND dest.text LIKE ‘%DROP%’;
two undocumented Stored Procedures that allow you to process through all tables in a database, or all databases in a SQL Server instance http://www.databasejournal.com/features/mssql/article.php/3441031/SQL-Server-Undocumented-Stored-Procedures-spMSforeachtable-and-spMSforeachdb.htm
This would be run for each db in turn. 1. Pause log backup job 2. On primary – alter database [dbname] set partner off 3. On primary – ALTER AVAILABILITY GROUP [agname] ADD DATABASE [dbname] 4. On secondary – ALTER DATABASE [dbname] SET HADR AVAILABILITY…
All of these instructions should be done as a database admin, with the restored database selected. First, make sure that this is the problem. This will lists the orphaned users: EXEC sp_change_users_login ‘Report’ If you already have a login id and password for this user,…
http://blogs.msdn.com/b/sqlserverstorageengine/archive/2006/10/20/consistency-checking-options-for-a-vldb.aspx DBCC CHECKDB includes: DBCC CHECKALLOC DBCC CHECKCATALOG DBCC CHECKTABLE http://sqlmag.com/blog/unkillable-dbcc-checkdb DBCC CHECKDB(‘DatabaseName’) WITH NO_INFOMSGS, ALL_ERRORMSGS http://www.bpsoftware.com/Blog/post/2008/11/25/SQL-DBCC-CHECKTABLE-on-multiple-tables.aspx — set options SET NOCOUNT ON; SET ANSI_PADDING ON; SET ANSI_NULLS ON; SET QUOTED_IDENTIFIER ON; DECLARE @tablenames TABLE (TableName VARCHAR(255)); — populate table with tablenames INSERT INTO…