Warning: Invalid argument supplied for foreach() in D:\home\site\wwwroot\wp-includes\translations.php on line 1174 Warning: Invalid argument supplied for foreach() in D:\home\site\wwwroot\wp-includes\translations.php on line 1367 SQL Server – Scattered Code

SQL Server hide database list from a particular user

Warning: Invalid argument supplied for foreach() in D:\home\site\wwwroot\wp-includes\translations.php on line 1174 Warning: Invalid argument supplied for foreach() in D:\home\site\wwwroot\wp-includes\translations.php on line 1367

One of the things I've been searching for is to hide the list of databases a user doesn't have access to. By default, when a user connects to SQL Server, especially a shared instance, he sees all the databases available on the server, even though he doesn't have access to view or access any of them. The goal is to only show a user the database(s) he should see. This isn't fully supported yet by SQL, but there's a way to solve the problem for the majority of cases. First,...

SQL Full Backup all databases

Warning: Invalid argument supplied for foreach() in D:\home\site\wwwroot\wp-includes\translations.php on line 1174 Warning: Invalid argument supplied for foreach() in D:\home\site\wwwroot\wp-includes\translations.php on line 1367

Backups are one of the most important tasks when maintaining a system, and as the complexity of your environment grows, you need tools to automate tasks. Using the SQL Agent, you can schedule the following stored procedure to back up every database and database log on your server (or modify it to your needs), excluding some stuff you can live without.   CREATE PROCEDURE BackupDatabases AS BEGIN     SET NOCOUNT ON;     DECLARE @name VARCHAR(50) /*database name*/     DECLARE @dbpath VARCHAR(256) /*path for backup db files*/     DECLARE @logpath VARCHAR(256)...

SQL Execute large scripts

Warning: Invalid argument supplied for foreach() in D:\home\site\wwwroot\wp-includes\translations.php on line 1174 Warning: Invalid argument supplied for foreach() in D:\home\site\wwwroot\wp-includes\translations.php on line 1367

SQL Server has a stored procedure that accepts a parameter of type text, and executes the code. This is useful if you need to run a large (over 8000 characters) dynamic query sys.sp_sqlexec

SQL Batch alter all columns

Warning: Invalid argument supplied for foreach() in D:\home\site\wwwroot\wp-includes\translations.php on line 1174 Warning: Invalid argument supplied for foreach() in D:\home\site\wwwroot\wp-includes\translations.php on line 1367

Sometimes you need to make mass changes to a database, and either add or remove a column, or other table or data manipulations. The script below prints SQL statements you can later execute for whatever you need. It loops through each table in your database, and replaces ? with the table name.   EXEC sp_msforeachtable 'PRINT ALTER TABLE ? ADD LastModified DATETIME NOT NULL DEFAULT GETDATE(); ALTER TABLE ? ADD UserID int NULL; ALTER TABLE ? ADD Deleted bit NOT NULL DEFAULT 0;' EXEC sp_msforeachtable 'PRINT Update ? SET SiteID=1'