 | A community of more than 1,600,000 database professionals and growing |
| Featured ContentsFeatured Script |
| | The Secret Password As I work with more server systems that help developers run Continuous Integration and automated releases, one of the things I see used often are variables. These are values you can set for a particular process and reference inside of that process. Great for setting server names, paths, etc. In releases, these are great for specifying specific values that change for each environment, such as the instance name or IP address. As with many developer based systems, security is not always set tightly on these systems and any developer can access the build server to kick off builds, reconfigure a process, etc. That makes sense in a CI process, but not so much in a release system. This is one reason I do recommend a separate release server from the CI server. You can use Jenkins or TeamCity to perform releases, but is it a good idea? Have you thought through the security? In the release servers, one thing that most systems allow the admin to do is use a variable for a password and mark it secret, so the value cannot be recovered. In this way, if some developer is working on the release process, they can't get the password to the production server. They can only click the buttons that deploy to that server. However. They can deploy to that server, and they don't need the password. If I were to execute a script in the release system that executes the "CREATE USER" and a "ALTER ROLE " commands to give me access to data, does it matter if I know the deployment password is "G4da%$2h#5f" or $(ProdPwd)? It doesn't. The actual value isn't relevant; I just need to be able to use it. I think release systems are great pieces of software for reducing the risk of your deployments, but I do think the security models need to be carefully designed and easy to configure, especially when it comes to allowing arbitrary code to be submitted and executed by one person. Be sure that developers can't necessarily deploy code directly to production servers, whether the password is hidden or not. If there is a way to use the value, someone will find it. Steve Jones from SQLServerCentral.comJoin the debate, and respond to today's editorial on the forums |
|
| |
| | |  | Mat Richardson from SQLServerCentral.com When INSERTING or DELETING rows from a table, the OUTPUT clause can be used to return a dataset containing the changes made. Mat Richardson explains how. More » |
 | Additional Articles from Redgate Louis Davidson shows how to save a defined SQL formatting style as a shareable template that you can quickly share with your coworkers. More » |
 | Additional Articles from SimpleTalk Load balancing in Azure has more importance for the DBA, because it is essential for Windows Server Failover Clustering in Azure, whether it is for AlwaysOn Availaiblity Groups, Failover Clustered Instances, or any other highly-available solution. Azure load balancing works out the location of the availability group, and routes traffic there. The load balancer detects a failure, and routes traffic to the new primary replica. Joshua Feierman gives an overview of what is required. More » |
 | Devin Knight from SQLServerCentral Blogs In this module you will learn how to use the Time Brush Power BI Custom Visual. The Time Brush gives... More » |
|
|
| | | Today's Question (by Carlo Romagnano): What are the values returned by this query? (they could be in any order) USE tempdb CREATE TABLE MyStuff(t varchar(100)) INSERT INTO MyStuff SELECT * FROM ( VALUES ('a') ,('aa') ,('aaa') ) AS V([t]) SELECT STUFF(t,2,0,CASE WHEN LEN(t) <= 2 THEN 'b' ELSE NULL END) AS Result FROM MyStuff DROP TABLE MyStuff |
Think you know the answer? Click here, and find out if you are right.
We keep track of your score to give you bragging rights against your peers.
This question is worth 2 points in this category: T-SQL. We'd love to give you credit for your own question and answer.
To submit a QOTD, simply log in to the Contribution Center. |
|
|
| |
| | Yesterday's Question of the Day |
| | |
| | | Haden Kingsland (theflyingdba) from SQLServerCentral.com /* Author: Haden Kingsland (FlyingDBA) Date: 19th April 2017 Description: A quick script to ascertain the headroom in SQL Server data files based on current size of data against the max size set for the file. You WILL need to have a max size set for your data file for this to work! This script is offered by the "FlyingDBA" as is, with no warranties or guarantees and should be used with caution in any environment. The FlyingDBA takes no responsibility for environments not under his current control and advises that all scripts are run in non production environments to verify their usefulness prior to moving to production! */ create table #drives ( --servername varchar(100), driveletter varchar(1), MBFree int ) declare @drive varchar(100), @fname varchar(5) set @drive = 'F' set @fname = @drive + ':\%' print @drive print @fname insert into #drives exec xp_fixeddrives -- uncomment for SQL 2012 and above as you can use the new "concat" function --select driveletter, concat(MBFree,' MB') as 'MB Free', concat(MBFree/1024,' GB') as 'GB Free' from #drives -- uncomment for 2008 R2 and below, as you need to use {fn concat] instead! select driveletter, {fn concat (convert(varchar(10),MBFree),' MB')} as 'MB Free', {fn concat (convert(varchar(10),MBFree/1024),' GB')} as 'GB Free' from #drives where driveletter = @drive select af.name, af.filename, convert(bigint,af.size * 8/1024) as 'Current Size (MB)', convert(bigint,af.maxsize * 8 / 1024) as 'Max Size (MB)', convert(bigint,(af.maxsize - af.size) * 8 / 1024) as 'Headroom (MB)', convert(bigint,(af.maxsize - af.size) * 8 / 1024 / 1024) as 'Headroom (GB)' from sys.sysaltfiles af inner join sys.databases d on af.dbid = d.database_id where d.state_desc = 'ONLINE' -- only online databases and af.maxsize not in (-1,268435456) -- only check database files with a max size set! and af.filename like @fname order by 6 desc drop table #drives --exec xp_fixeddrives More »
|
|
|
| Database Pros Who Need Your Help |
| Here's a few of the new posts today on the forums. To see more, visit the forums.
is it possible to convert Hashbyte back to string? - We have unique key in table which is hashbye sha1. This value is combination of multiple columns from a different table.... Append json value - Hi all, I have json like this DECLARE @j NVARCHAR(max) = N'' i want to add "size"="1000" to this array SET @j = JSON_MODIFY(@j,'append... Export data in XML to import into Word - Hi Folks, I am building a one-click application to export information from SQL Server 2016 in XML format that will be...
Query Performance Standards - Overall Per DB - Good morning fellow SQL server folks! I have a general question regarding Query Performance standards. If you have them, what... Deadlock with two update statements - Hi Guys, I am getting a deadlocks recently on one of my instance of SQL Server. Below are the two statements... SignalR Deadlock - The below select is causing deadlock on our production box. How can i avoid this? The same query is running from... Modify Column - Tried to change the datatype from nvarchar(250) to nvarchar (20) using below script and its threw error ,but the same... Sync Prod to Non-Prod - Our Production sits in a very strict environment and different subnet from Non-Prod environment and is in a different subnet.... SchemaName.ObjectName - We have an app user with default schema 'bill' . The scripts given by developers never mention the schema name 'bill'...
Stored Procedure for Dynamic Query in Sql server - I need procedure for below dynamic query.In that procedure f_id and value is input parameters. f_id and value is collection of... Index analysis is not working in my SQL sentry tool , Any experts ? - Index analysis is not working in my SQL sentry tool , Any experts ?
Is there a best way to combine data into a single output field - I need to create output that takes a list of names and combines them into one field. Here is an... Do you need an order by when selecting TOP N from a clustered index? - Lets say you have a table with 2 columns, the first, an identity primary key column, the second a foreign... Unpivot column to rows dynamic sql - Hello, I was tasked to move the following data to our database and visualize through Cognos, so the data is currently... Need Logic - Hi Team, I have a monthly billing report which works absolutely fine. Now i need to add some more features...
Why DECLARE doesn't work? -
DB Backup Maintenance Plan Won't Execute - I've created a back up plan on one DB using the Maintenance Plans Wizard and the regular way (New Maintenance...
Connecting to two different databases from the same report on different server paths - Hi everyone! Does anyone know if it's possible to deploy a report to two different server paths on the same reporting server each... Scaling Out SSRS -- Licensing - Hi folks, I did a search but couldn't find anything regarding "scaling" in the SSRS section. Sorry in advance for starting...
How to check if a field contains unicode data - Hi, I want to check if a field contains Unicode characters or not. This is how I have been doing it: SELECT... |
|
| This email has been sent to newsletter@newslettercollector.com. To be removed from this list, please click here.
If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com.
| This newsletter was sent to you because you signed up at SQLServerCentral.com.
Feel free to forward this to any colleagues that you think might be interested.
If you have received this email from a colleague, you can register to receive it here.
| This transmission is ©2017 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved.
Contact: webmaster@sqlservercentral.com |
|
|
