Should Developers manage database read status

A programmer asked me if he can include the a function to change the database status out of READ_ONLY , add some data and then put it back into READ_ONLY. These are the basic commands ALTER DATABASE database-name SET READ_ONLY ALTER DATABASE database-name SET READ_WRITE There are some considerations for deciding if a Developer should be able to include as part of an ETL process , the capacity to change the READ STATE of a SQL database 1) Requires ALTER permission on the database. This is an elevated privilege - and may break the organisations sql server security policy 2)... Read more →

How to write a conditional substring in sql

Question: I have a SELECT statement returning a recordset . Before presenting the recordset , I want to strip out some of the substrings within certain records. This is an example of the recordset Brazil Brazil.com Argentina.net Bolivia The requirement is to strip out any part of the string from the dot and after. For example, “brazil.com” will become “brazil”. The problem I have currently is by running the SUBSTRING function across all the rows, if there is no dot in the row , the rown will return as blank. The only rows returning are the ones with a dot!... Read more →

The EXECUTE permission was denied on the object sp_enum_dtspackages

Granting permissions on SQL Server , to view legacy DTS packages requires EXECUTE permissions to various stored procedures on the msdb system database. The easiest way to implement , is to create a database role including the permissions. Add the SQL Server login to the database role. The added benefit of using database roles is it complies with standard SQL Server security policies. Most standard security policies advocate the usage of database roles versus using object level permissions. Read more on How to manage the IS_GRANTABLE security risk Stored procedures required to view legacy DTS packages Add EXECUTE permissions to... Read more →

How to backup a single table with data in a database

Question: How can I backup a single table with data in a database ? I need a quick method to save the data as I’d like to make changes to the original table, without backing up the whole database. Answer: A simple method to backup the whole table is: select * into dbo.new_table from dbo.old_table For backup of specific Column Data in the table select Col1, Col2 into dbo.new_table from dbo.old_table To backup to an Excel spreadsheet – read SQL Server Export to Excel with Powershell Read More Export-CSV Powershell - SQL Server DBA Powershell - run script on all... Read more →

Non-sysadmin permissions to create or edit an SSIS package

Questions: How can I allow non-sysadmin user to create and edit a SSIS package? Are special permissions required? Answer:Any user with Business Intelligence Developer Studio( BIDS) can create or edit an SSIS package. The permissions are required to deploy the SSIS package to a SQL server. A suitable msdb role is the db_ssisltduser role. This role allows: READ functions Enumerate own packages. Enumerate all packages. View own packages. Execute own packages. Export own packages. WRITE functions Import packages. Delete own packages. This is a sample T-SQL script assigning the role to a database user USE [master] GO CREATE LOGIN [mydomain\mylogon]... Read more →