Moving database files to a different location

Step 1

ALTER DATABASE <databaseName> SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE <databaseName> SET OFFLINE;

Step 2

ALTER DATABASE <databaseName> MODIFY FILE
(
Name = <logicalfilename>,
Filename = ‘<fullpathofthefile including.mdf>’
);

ALTER DATABASE WSS_Content_CCS MODIFY FILE
(
Name = <logicallogfilename>,
Filename = ‘<fullpathofthefile including.ldf>’
);

Step 3

Check permissions on the files in their new location,

i.e. SQL Server should have full control.

Step 4

ALTER DATABASE <databaseName>  SET ONLINE;

ALTER DATABASE <databaseName> SET MULTI_USER;

eg.

ALTER DATABASE StateServiceDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE StateServiceDatabase SET OFFLINE;

ALTER DATABASE StateServiceDatabase MODIFY FILE
(
Name = StateServiceDatabase,
Filename = ‘N:\SPTEST_StateServiceDB\StateServiceDatabase.mdf’
);

ALTER DATABASE StateServiceDatabase MODIFY FILE
(
Name = StateServiceDatabase_log,
Filename = ‘O:\SPTEST_StateServiceDB\StateServiceDatabase_log.LDF’
);

ALTER DATABASE StateServiceDatabase SET ONLINE;

ALTER DATABASE StateServiceDatabase SET MULTI_USER;

Advertisements

Create a role to EXECUTE Stored Procedures in SQL

CREATE ROLE proc_executor
GRANT EXECUTE TO proc_executor

And then you can add users to that role.