We have a few SQL Server databases that needs to have trustworthy option set to 'ON' to allow for external assemblies to load, but every time there's any security patch, the option sets to off and the application breaks. I don't want to create multiple SQL agent jobs to constantly run the set trustworthy on command, so I've been trying to develop a cursor that will run this command on the five databases. This is what I have right now, but I'm an getting error saying that trustworthy is not a recognized SET option. Could somebody please look and help fix this? Running on SQL Server 2019 Enterprise Edition.

Declare @db_name varchar (100)
Declare @command nvarchar (200)
Declare database_cursor cursor for
Select name from master.sys.sysdatabases
Where name in ('db1', 'db2', 'db3', 'db4', 'db5')

Open database_cursor

Fetch next from database_cursor into @db_name
While @@fetch_status = 0

Begin
     Select @command = 'ALTER DATABASE' + ''''+ @DB_NAME+ '''' + 'SET TRUSTWORTHY ON'
PRINT @COMMAND
EXEC SP_EXECUTESQL @COMMAND

FETCH NEXT FROM DATABASE_CURSOR INTO @DB_NAME
END

CLOSE DATABASE_CURSOR
DEALLOCATE DATABASE_CURSOR

I changed the database (foo) mode from "Standby / Read-Only" to Active by using the following statement: restore database foo with recovery After that, I changed the mode back to Active by using the following statement: ALTER DATABASE foo SET READ_WRITE Then, I changed back to "Read-Only" by using user interface here: Properties --> Options --> State --> Database Read-Only --> changed to 'True': Now, I am trying to go back to the original state "**Standby / Read-Only**" mode (original). How do I go about doing that?

I am trying to change the mode of database from "Standby / Read-Only" to "Normal" mode so that I could use mdf file to attach to other SQL Server. One instruction that I got was go to Properties --> Options --> State --> change the Database Read-Only from True to False. Then I got this error message: **A warm-standby database is read-only.** Why do I get this error message and what should I do to change the mode?

During a SQL Server installation, I "thought" I specified location of tempdb on 'F:' and not 'C:', but I can see that although tempdb.mdf and templog.ldf are on 'F:', the 3 .ndf secondary data files are on 'C:'. I'm thinking of moving the .ndf files by performing following steps: 1.

ALTER DATABASE tempdb MODIFY FILE ( NAME = temp2 , FILENAME = 'F:\MSSQLData\temp2' )
	ALTER DATABASE tempdb MODIFY FILE ( NAME = temp3 , FILENAME = 'F:\MSSQLData\temp3' )
	ALTER DATABASE tempdb MODIFY FILE ( NAME = temp4 , FILENAME = 'F:\MSSQLData\temp4' )

2. Stop SQL Server (the instance isn't doing anything currently). 3. copy/paste the 3 .ndf files from their current C: location to the new F:\MSSQLData\ location 4. Restart SQL Server. 5. Check if it worked:

SELECT name, physical_name AS CurrentLocation, state_desc  
	FROM sys.master_files  
	WHERE database_id = DB_ID(N'tempdb');

Is that the best approach? The instance supports only 3 small databases (<10GB total).

I'm having some problems moving DATAFILE from one location to another due to the shortage of storage space. I've managed to change the path file of control file via command(linux). Now I would like to change the path file of the datafile and logs to another since I've already manual moved the folder of the database. SQL> startup; ORACLE instance started. Total System Global Area 6710886400 bytes Fixed Size 2939560 bytes Variable Size 3506439512 bytes Database Buffers 3187671040 bytes Redo Buffers 13836288 bytes Database mounted. ORA-01157: cannot identify/lock data file 1 - see DBWR trace file ORA-01110: data file 1: '/app/oracle/oradata/ENOVIA/system01.dbf' Apparently, the data files have already been moved to another location, whereas the path of the files has yet to be changed. How can I check for the current location of Database in SQL and which command is needed to be used to alter the path file? The new location is home/oradata/ENOVIA.

id | bigint unsigned | NO | PRI | NULL | auto_increment | | title | varchar(255) | NO | | NULL | | | category | varchar(255) | NO | | NULL | | | summary | varchar(10000) | NO | | NULL | | | detail | mediumtext | NO | | NULL | | | created_at | timestamp | YES | | NULL | | | updated_at | timestamp | YES | | NULL | | | image | mediumblob | YES | | NULL | | | image1 | mediumblob | YES | | NULL | | | image3 | mediumblob | YES | | NULL | | | phold | varchar(50) | NO | | NULL | | +------------+-----------------+------+-----+---------+----------------+ I have this database now,I want to make a one-one relationship with with another table with where I use column

( title )

to link specific data. When I run mysql> alter table tours add primary key (id,title); OR mysql> alter table tours add constraint pk_tours primary key (id, title); The error: ERROR 1068 (42000): Multiple primary key defined on both queries. I want another table named itinery where I use to link it with former table with help of volumn title ; I am using mysql 8 with laravel 8. Thanks

I Want to **restore a backup** with **overwriting on a database in use** for this problem I want to create a query base on these steps : 1- Remove All Connections [MyDB] 2- Create a backup from [MyDB] 3- Restore a Backup as an Overwrite on [MyDB] from specifying a path Thank you very much for your help

While altering a DB to open getting the below error. SQL> alter database open; alter database open * ERROR at line 1: ORA-00600: internal error code, arguments: [kcratr_nab_less_than_odr], , , , , [], [], [], [], [], [], []

I know you can alter the database to update their data and log file paths in the database properties, but is there also a way to script out the actual physical move of the files themselves?...maybe through a database command?

What is the minimum privilege needed to execute ALTER DATABASE {db_name} SET ARITHABORT ON? I found that the privilege ALTER ANY DATABASE would suffice this, but I wonder if there isn't any other "smaller" privilege that does the same thing, because I think this permission is too broad and maybe I'll not have it in production environment.

I have a database with several tables and i have a table which contains all tablenames which I want to alter. I want to perform a query like this: ALTER TABLE (SELECT "TABLE_NAME" FROM "RESOURCE_TABLES) DROP COLUMN ID; My Table RESOURCE_TABLE with Table Names: TABLE_NAME ID Table1 1 Table2 2 Table3 3 ... ... How can I alter all tables named in the table in the column TABLE_NAME with a loop or a query? So i want Table1,Table2,Table3 to drop a column id without executing a single alter table query on each table because maybe the tables are 100 Tables.

The DBA run a file relocation script to move the physical Database file from where it was running to I:. ALTER DATABASE [cro01] MODIFY FILE ( name=cro01,filename=N'i:\SQLData\cro01.MDF') He take the Database offline ad copy the file. But *for some strange reason* he don't finnish the job correctly. We have now mdf files duplicated and we dont know which one is running. How do I know the "real" running location of the files via a Tsql query? Is it posible? I want to avoid checking manually each disk file location against each sys.master_files entry.

I have SQL Server 2008 R2 with 323 databases consuming some 14 GB on my C: drive, a fast SSD. Because I want to reclaim some space on my C: drive, I would like to move them to my D: drive. I have found this MSDN article , but that seems to be the procedure to move only one database. Is there an automatic way or script to move all my databases at once?

I'm trying to setup a "deployment" user which can create and alter tables on an existing database in addition to selecting, updating, inserting and deleting records. Here is what I've tried so far: -- Create deployment user CREATE ROLE deploy_user WITH LOGIN PASSWORD 'deploy_user'; -- Grant connect and create GRANT CONNECT, CREATE ON DATABASE my_database TO deploy_user; -- Grant create schema privilege ALTER ROLE deploy_user CREATEDB; -- Change db owner to deployment user ALTER DATABASE my_database OWNER TO deploy_user; -- Grant CRUD operations ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT INSERT, UPDATE, DELETE ON TABLES TO deploy_user; None of the above grants work. What I end up with is a user which can login but that's it. I cannot select, insert, update, delete on any tables. I cannot make changes to the schema either. Can anybody help out?

I know the rename database functionality has been taken out of MySQL. My question is, does renaming it in PHPMyAdmin work and is safe? I know they have written functionality to deal with a number of issues that can arise but I haven't found any commentary on whether it's safe. By safe I mean no data/structure loss. Thank you

I am trying to set a global setting/GUC variable postgis.gdal_enabled_drivers from the PostGIS extension for a while. I am a non-admin user but can ask admin to change settings if needed. But I tried several different methods following the instructions but none worked. 1. I tried the old fashion way to change environmental variables. I had the following added to /etc/environment: POSTGIS_ENABLE_OUTDB_RASTERS=1 POSTGIS_GDAL_ENABLED_DRIVERS=ENABLE_ALL so that echo $POSTGIS_GDAL_ENABLED_DRIVERS returns ENABLE_ALL Now, in my non-admin PostreSQL account, SELECT short_name, long_name FROM ST_GdalDrivers(); returns 0 rows, meaning no GDAL drivers are enabled. 2. I also tried to have postgres to: ALTER SYSTEM SET postgis.gdal_enabled_drivers TO 'ENABLE_ALL'; but I got an error: ERROR: unrecognized configuration parameter "postgis.gdal_enabled_drivers" 3. I tried to: ALTER DATABASE my_db SET postgis.gdal_enabled_drivers TO 'ENABLE_ALL'; but I got: ERROR: permission denied to set parameter "postgis.gdal_enabled_drivers" In sum, I can ask admin to change some settings **once** as in methods 1 and 2, but not every time e.g. when I do a full database restore (method 3). My question is: **How can I make the above methods work?** FYI, my PostgreSQL version is 9.5.1, OS is Lubuntu 16.04 beta postgis_version --------------------------------------- 2.2 USE_GEOS=1 USE_PROJ=1 USE_STATS=1 In method 3, I can use postgres to ALTER DATABASE but I can't ask amin to do that every time I restore db, where I do something like: dropdb my_db && createdb -T template my_db $$ psql < my_pg_dump_file

I simply want to add multiple users that I have created to a created role then grant all privileges on said role. This is what I have thus far: CREATE USER Bob FROM LOGIN Bob; CREATE USER Billy FROM LOGIN Billy; CREATE USER Bobby FROM LOGIN Bobby; CREATE ROLE Users; ALTER ROLE Users ADD MEMBER Bob; GRANT ALL PRIVILEGES ON ABCdatabase.* TO Users; I want to add Billy and Bobby, is there a way to add them to the ALTER line or do I have a create a statement for each members? Any improvements to the code above is helpful. What kind of SQL server am I using? I am not necessarily using any specific type. I merely need to provide code for a school assignment and am not testing any of this on any particular system. Thanks in advance.

I specifically want a programmable way to change the recovery model of all the databases on a server, and i know this solution can be applied to all 'Alter Database' commands. While I know SMO in PowerShell can solve this problem very easily: Import-Module SQLPS CD SQL\*ServerName*\*InstanceName*\databases foreach ($database in (ls -force)){ $database.recoverymodel = 'Full' $database.update } I'm looking for the most efficient T-SQL solution. It has been ingrained in my head to avoid cursors at all costs, however I can't think of a set based solution to perform alter statements. Here is the cursor based solution. DECLARE @sql varchar(MAX), @name varchar(50) DECLARE cur CURSOR FOR SELECT name FROM sys.databases where name 'tempdb'; OPEN cur FETCH NEXT FROM cur INTO @name WHILE @@FETCH_STATUS = 0 BEGIN SET @sql = 'ALTER DATABASE ' + @name + ' SET RECOVERY FULL;' exec(@sql) FETCH NEXT FROM cur INTO @name END CLOSE CUR DEALLOCATE cur 1. This can also be done with a while loop, but that solution still goes through a looping process... do while loops offer significantly better performance? 2. Am i being paranoid about performance? I say that because administrative tasks such as this aren't done very often, and there generally isn't a massive amount of objects you need to loop through. However i always think "What if i worked in a large environment with thousands of objects" and then i begin to feel guilty that I may not be using the most efficient solution.

I just installed Microsoft ServiceBus and it created 5 databases. Now I want to move them to some specific location so I can join them to SQL Server AlwaysOn Availability group. I stopped service bus farm and tried to execute the following queries one by one: ALTER DATABASE [SbManagementDB] SET OFFLINE; ALTER DATABASE [SbManagementDB_log] SET OFFLINE; ALTER DATABASE [SbManagementDB] MODIFY FILE ( NAME = [SbManagementDB], FILENAME = 'C:\Program Files\Microsoft SQL Server\MyLocation\SbManagementDB.mdf' ); ALTER DATABASE [SbManagementDB_log] MODIFY FILE ( NAME = [SbManagementDB_log], FILENAME = 'C:\Program Files\Microsoft SQL Server\MyLocation\SbManagementDB_log.ldf' ); ALTER DATABASE [SbManagementDB] SET ONLINE; I brought SbManagementDB offline successfully, but when I tried to do that to SbManagementDB_log I've got the following error: Msg 5011, Level 14, State 5, Line 2 User does not have permission to alter database 'SbManagementDB_log', the database does not exist, or the database is not in a state that allows access checks. Msg 5069, Level 16, State 1, Line 2 ALTER DATABASE statement failed. I executed the following query SELECT name, physical_name AS CurrentLocation, state_desc FROM sys.master_files and made sure that this DB exists and I tried sa login, but got the same error. Any ideas what am I doing wrong?

When trying to mirror a database using the following command ALTER AVAILABILITY GROUP SQLAlwaysonGroup ADD DATABASE test0916aj8CJ I get the following error > Msg 1475, Level 16, State 105, Line 1 > Database "test0916aj8CJ" might contain bulk logged changes that have not been backed up. Take a log backup on the principal database or primary database. Then restore this backup either on the mirror database to enable database mirroring or on every secondary database to enable you to join it to the availability group. Can this be done without backing the database? Or should I backup and then discard the backup. It is for a newly created db, so I do not need the backup anyway at this point. I have tried the following... BACKUP DATABASE [test0916aj8CJ] TO DISK = N’NUL’ WITH COPY_ONLY, NOFORMAT, INIT, NAME = N’test-Full Database Backup’, SKIP, NOREWIND, NOUNLOAD GO but the above method did not work either. Thanks