Problems displaying this newsletter? View online.
SQL Server Central
Featured Contents
Question of the Day
The Voice of the DBA
 

Least Privilege

I assume that most of you know about the principle of least privilege. If not, please read this short blog from Brian Kelley and make sure you understand how you should approach security. In the modern world, we also ought to adapt our systems for the zero trust model, which includes the least privilege principle.

However, I wonder how many of your organizations really follow these security guidelines internally. Are you strict about adding limited access and removing it when people change jobs/roles? If you use Windows Auth (or Entra), are your admins doing that or just adding in new roles? Do you scope down database access roles in granular ways or just stick with 1-2 roles for the most common things people do?

Maybe more importantly, do you use roles or are these systems that still have explicit grants for users?

Microsoft had a major hack recently from a test account that had administrative privileges. While there certainly might be a need for a test account to have privileged access, I'd hope that any test account created had a limited lifetime. I've created privileged database access accounts for vendors, but usually set a reminder to myself to disable the account after xx days. When I got smarter, I wrote a one-time job to do that and scheduled it. These days, I'd also file a ticket for my team noting that this needs disabling as well.

Humans get lazy and often don't think about the future. If you've never had an issue with a test account, why think something might happen? Why spend the time writing a note or a job when surely you'll remember or deal with it later? Maybe more common, why disable a login when the user might need access longer? We don't want to deal with another phone call and enabling the account. That's an interruption to our work week.

What has been humorous to me is that I've seen quite a few people who are very security conscious get annoyed when some automated system or process disables their account and forces them to make a call.

It is annoying. However, these little things, the details, the adherence to good practices are what help ensure we have better security. When we take shortcuts (like not enabling MFA), when we skip steps, when we do small favors for others, we're increasing risk. Most of the time that's fine.

Once in awhile it really comes back to cause problems. I'm not sure the savings are worth it.

Steve Jones - SSC Editor

Join the debate, and respond to today's editorial on the forums

 
 Featured Contents
SQLServerCentral Article

Exploring Generative Adversarial Networks (GANs) for Butterfly Image Generation

Rohit Alladi from SQLServerCentral

Exploring Generative Adversarial Networks (GANs) for Butterfly Image Generation to gain some understanding of how this class of Artificial Intelligence technologies work.

External Article

XML Performance Tuning and Added Options

Additional Articles from SimpleTalk

SQL Server provides a variety of ways to tune XML so that it provides consistent performance, consumes less space, all while ensuring efficient access to critical data. At its core, the metadata-styled XML format runs counter to the data that SQL Server is optimized to manage.

External Article

Prompt+ EAP: your AI-powered database development partner in the making

Additional Articles from Redgate

Last year, we introduced AI capabilities to SQL Prompt via the Prompt+ Early Access Program (EAP). Using generative AI-powered insights and context-based awareness, Prompt+ takes natural language queries and turns them into SQL coding suggestions. Check out this blog to learn more about Prompt+ and how to join the EAP waitlist.

From the SQL Server Central Blogs - Query Store Keeps Switching Itself Off, Getting it Moving Again

david.fowler 42596 from SQL Undercover

I recently received a complaint that Query Store for a particular database was turned off, which was strange as that particular database has seen quite a few performance issues...

Blog Post

From the SQL Server Central Blogs - Modifying sp_QuickieStore Returned Column Order

hellosqlkitty from SQLKitty

I love QuickieStore, but I wanted some columns to be at the front end of the results returned. Namely, I wanted top_waits, query_sql_text, and query_plan right after database name....

The Unicorn Project

Site Owners from SQLServerCentral

In The Unicorn Project, we follow Maxine, a senior lead developer and architect, as she is exiled to the Phoenix Project, to the horror of her friends and colleagues, as punishment for contributing to a payroll outage. She tries to survive in what feels like a heartless and uncaring bureaucracy and to work within a system where no one can get anything done without endless committees, paperwork, and approvals.

 

 Question of the Day

Today's question (by Steve Jones - SSC Editor):

 

A Valid Type?

What happens when I run this code in SQL Server 2019?
DECLARE @variable CURSOR;

Think you know the answer? Click here, and find out if you are right.

 

 

 Yesterday's Question of the Day (by Steve Jones - SSC Editor)

What's Wrong with this Table?

What's wrong with this code and causes an error?

CREATE TABLE Customer
(
    CustomerID INT NULL,
    FirstName NVARCHAR(50) NOT NULL,
    LastName NVARCHAR(50) NULL,
    Email NVARCHAR(100) NOT NULL,
CONSTRAINT CustomerPK PRIMARY KEY (CustomerID, LastName)
);


Answer: The Primary Key must have all columns set to NOT NULL

Explanation: While the schema should be specified, this doesn't cause an error. The user's default schema is used. The problem is that a Primary Key in SQL Server must have all columns defined as not null. Ref: Create Primary Keys - https://learn.microsoft.com/en-us/sql/relational-databases/tables/create-primary-keys?view=sql-server-ver16

Discuss this question and answer on the forums

 

 

 

Database Pros Who Need Your Help

Here's a few of the new posts today on the forums. To see more, visit the forums.


SQL Server 2017 - Administration
side effects of giving permissions to run traces - Hi All, I would like to know the downsides of giving permissions to run traces in lower environments (DEV/QA/UAT). Our DB team has only two members: one supports PST hours, and the other supports IST hours. The PST resource is on leave for a month, so I have to cover both time zones as needed. […]
Select permission denied despite group having db_datareader - I am at a loss and could use a second pair of eyes. We have a Server with database A and database B, a windows security group (MyDom\MyGroupName) which is created with dbo schema as its default in both databases, and a service account in this group. MyDom\MyGroupName has db_datareader in both databases. There are […]
SQL Server 2017 - Development
Split fields sql 2017 - I am trying to split fields in sql 2017.  The column holds the following.  I am pulling the first 50 characters and now I need to split those into 2 fields. This is a test name1                          3006              […]
SQL Server 2016 - Administration
connection does not open from SSMS - Dear Friends, Would be grateful if anybody can advise why one of my servers, doesnt allow a connection anymore from SSMS on my terminal. The IP address is the unchanged. SQL server port was changed I remember, that i am anyways providing in with the IP as IP:portnumber..Thanks in advace.
How to improve index rebuild for very large table. - My online index rebuild job for a 200G table with 40mil rows took me up to 30hrs to complete, this is a once off activity done since we have an allocated downtime for server maintenance. I would want to know, is there any way for me to do it faster? I had an idea of […]
SQL 2012 - General
SQL code needed for the WHERE clause.. - I have a table with data appending on a daily basis. We plan to keep the set of data in this table for one full year and delete everything after August 1st of every year. I am stuck with the query logic to do this.  The situations are like this: If today's date is August […]
SQL Server 2019 - Administration
Database growth report by full back file and send by email in graph and or html - Hello Gurus, How I am in need of all databases growth in 10 instance and send the report by an email. What's the best way to accomplish such task. If anyone has scripts or point me to a website that would be very helpful. thanks in advance.
Backuips are failing - After  CU 27  update in sql 2019  backups are  failing and  all  mainnaytainence  jobs are  also  faling . we did not find  any  error on  sql logs , pls  assist on ho wto resolve this   Message Executed as user: NT Service\SQLSERVERAGENT. Microsoft (R) SQL Server Execute Package Utility Version 15.0.4375.4 for 64-bit Copyright (C) […]
Query Store with AOAG stuck on waiting - Hey Folks, I have a database on SQL 2019 CU24 in an AOAG with 1 primary and 2 secondary.  I have enabled Query Store but it will not open and just shows Waiting on the primary. I have QS setup on standalone SQL 2016 databases and they seem to be working fine. Any help is […]
SQL Server 2019 - Development
Web API Procedure will not run in SQL Server Agent Job - I have a stored procedure that pulls JSON data from a US government web API. The procedure executes fine manually but fails with the error below when executed from a job.  I've checked permissions of the SQL Agent and ensured it has permission to execute sp_OACreate and sp_OAMethod  procdures. Extremely grateful for any help or […]
Logic similar to EXIST to make all values the same if it meets a criteria - Hello everyone, I have a question. Please let me know if someone has any ideas. I need a logic with maybe EXIST(?) to check separately if for one Chain there is a field in REPLENTYPE with a value ‘SINGLE’ – I need all fields for this UPC at a specific Masterchainname to be ‘REPLENISHABLE’ for […]
Reporting Services
SSRS Header not Keeping with report - Hi all, I have an SSRS report issue is when printing to PDF. i have a subreport that works fine, but when there is ONLY enough space for the header, it will stay at the bottom then the rest of the subreport continues on the next page. here is a screenshot of the subreport, mainly […]
SQLServerCentral.com Website Issues
Weird redirect after reporting post - I just tried reporting this post: https://www.sqlservercentral.com/forums/topic/fuhrerschein-umtauschen-49-176-44678273-motorbike-fuhrerschein-2 After clicking the 'Report post' button, I was redirected to this post: https://www.sqlservercentral.com/forums/topic/null If I then return to the original spammy post, there is no indication that the report function worked.
SQL Server 2022 - Administration
SQL Server 2022 Std edition eval version - Hi We are planning to upgrade our SQL Server 2014 Ent edition to 2022 Standard and i want to test Basic Availability Groups future. Is there a free evaluation version for SQL Server 2022 Standard edition? At least i could not find one Thank You
SQL Server 2022 - Development
how to bulk insert a file on a azure sql server managed instance - Don't know if this is the right place for this topic. I have an internal azure sql server managed instance and am trying to read or write a file to the file system. As i understand, there is no file system on a sql server managed instance. Is there a way to add a file […]
 

 

RSS FeedTwitter

This email has been sent to {email}. 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.
©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved.
webmaster@sqlservercentral.com

 

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -