11 votes
Accepted

SQL Server with multiple databases (one per client) - what is the best security practice in terms of logins/users/permissions?

Should we create a separate login for each client and have the application connect using separate login credentials? Short answer: Yes. You've segregated each client's data into a separate database, ...
Phill  W.'s user avatar
  • 8,726
7 votes

What are some sensible approaches for dealing with 4,000 vulnerabilities across 700 databases in 70 servers

Nope I wouldn’t do anything. I’d bring the list of issues and servers to stakeholders and business owners and ask them what’s important to them, along with any pros/cons/technical challenges with ...
Erik Darling's user avatar
  • 40.8k
5 votes
Accepted

Can I change the owner of the cdc schema?

From the documenation: For Change data capture (CDC) to function properly, you shouldn't manually modify any CDC metadata such as CDC schema, change tables, CDC system stored procedures, default cdc ...
Ben Thul's user avatar
  • 1,947
5 votes

SQL Server with multiple databases (one per client) - what is the best security practice in terms of logins/users/permissions?

One option that I don't think was mentioned is contained users (contained authentication, partially contained databases, whatever you want to call it). You don't have a login. You either have a SQL ...
Tibor Karaszi's user avatar
2 votes

Tool for monitoring changes to data in a database

You can enable the audit plugin in MariaDB or MySQL servers. Configure it to store the logs to a file and backup these files in S3. This is better than using CDC or using the mysqlbinlog utility since ...
jerichorivera's user avatar
1 vote
Accepted

Logical replication security risk with run_as_owner = false

The risk is the other way around. If you are an unprivileged user and own a table to which data are replicated by the subscription, you could create a trigger on that table and define it as a replica ...
Laurenz Albe's user avatar
  • 51.6k
1 vote

Tool for monitoring changes to data in a database

You could use a change data capture tool like Debezium https://debezium.io/ or Maxwell’s Daemon https://maxwells-daemon.io/ to read the binary log where every row modification is logged as an event ...
Alexander Peresypkin's user avatar
1 vote

SQL Server DWH architecture question

Personally I would say it is good old fashioned way to kept it in a different database or schema for tight security implementation. If the tables are to be used in conjunction with the others tables ...
pramod m nair's user avatar
1 vote

What are some sensible approaches for dealing with 4,000 vulnerabilities across 700 databases in 70 servers

Where would you start? What would you do? What are the business objectives?..because that's really what will drive your decision making here on priorities. In my opinion, 70 servers sounds a little ...
J.D.'s user avatar
  • 37.5k
1 vote
Accepted

SAGE ODBC driver - minimum required folder permisions

I'll leave this here should anyone else encounter issues with SAGE ODBC driver. Answer: Since posting this question I've spoken to a SAGE dev. Basically because of the way that SAGE interconnects with ...
BLoB's user avatar
  • 153
1 vote

Can I change the owner of the cdc schema?

Alternate Solution? You asked: Because I want to leverage ownership chaining and read from this schema using a proc, can I change the owner to dbo? Instead of changing the ownership, why not just ...
John K. N.'s user avatar
  • 17.7k
1 vote

SQL Server with multiple databases (one per client) - what is the best security practice in terms of logins/users/permissions?

You can use individual login for individual database and make sure to sort out user mapping for each user so that it is assigned the dbowner of the database. Also be sure to use different password for ...
Rajin Tamrakar's user avatar
1 vote

SQL Server with multiple databases (one per client) - what is the best security practice in terms of logins/users/permissions?

dozens of databases - one per client I am guessing, that is the reason for down votes of the question. That is a very bad organizational decision. But if you "client" is actually a group of ...
White Owl's user avatar
  • 797
1 vote

Making a database publicly readable to anyone on the Internet

There's a difference between making the data publicly available and making the database publicly available. What is not acceptable is that someone manages to get root access to the system on which ...
pmdba's user avatar
  • 3,104
1 vote

Can I revoke database permissions on "NT AUTHORITY\SYSTEM" but still let it have the "sysadmin" role?

Is there a way to have both the SysAdmin role and also remove extraneous permissions on this account? The immediate and straight forward answer is, "no". It is possible to allow logins that ...
Sean Gallardy's user avatar
1 vote

Azure SQL ports - expect on 1433 - but finding more

Our security department has questioned why we have multiple ports open to our Azure SQL instance. You don't. Those ports aren't open on SQL. There's a regional gateway that either proxies or ...
David Browne - Microsoft's user avatar
1 vote

Logon failed for login due to trigger execution

I ended up using a batch file as follows: (admin command) net stop MSSQLSERVER net start MSSQLSERVER sqlcmd -E -S <servername> Make sure no cohort has SQL Server Management studio open and that ...
Jamie's user avatar
  • 141

Only top scored, non community-wiki answers of a minimum length are eligible