13

I think there is no reliable solution for your problem since Application Name is modifiable parameter that cam be changed by any user. Here is how to change it within SSMS: In Connect to Database Object dialog choose Options, open Additional Connection Parameters and choose any name for Application Name like this: Now sys.dm_exec_sessions DMV and ...


11

You can use a server logon trigger to make custom logon validations and reject them whenever you see fit. You will see this trigger listed below "Server Objects" and inside "Triggers" if you are using SSMS. For example: CREATE TRIGGER strRejectSSMSConnectionForSQLLogin1 ON ALL SERVER FOR LOGON AS BEGIN IF ORIGINAL_LOGIN() = N'SQLLogin1' AND ...


9

Honestly, sa or a disabled SQL Server account with absolutely minimum permissions is the best choice. As to why? Well, I could write out three or four paragraphs on that, or I can share my favorite article on What account should own the databases and why. A very thorough explanation covering all the bases. Highly recommended. Here's a core excerpt: (...


8

Is there an underlying reason why databases cannot be owned by a secondary principal? I'm not 100% certain of the reason for this restriction, though I suspect it has to do with the ability to impersonate (i.e. become) that SID. There are certain operations that can reach outside of the instance (e.g. to the OS, file system, etc) and the logic is usually ...


4

In the ideal sense, this is a process / policy / management issue. Even if someone knows the password, if it is against company policy for anyone but a DBA to connect to Production (well, you might have a Release Engineering team and/or sys admins, etc), and there are penalties for breaking the rules, then that should be sufficient (assuming that such rules ...


4

You can't cut off a specific client, as already detailed in the other answers. The solution is to remove the access privileges to the production systems from the developer's accounts. Any change must be scripted and a dba will run the script. Deployment is performed by a sysadmin; devs produce a package they give to someone with proper privileges and devs ...


3

You can group several privileges into one Role. It is simpler to grant roles rather than dealing with a bunch of privileges. In a multi-tier architecture this might be no so important. You may have a user admin, backup, http - that's it. However, when your database is used by many (human) users distributed over several departments with different ...


3

Short Answer: you can't. If the database is being run on the customer's infrastructure, then the customer must have control over their own infrastructure. Without it, they cannot operate. If you really don't want them to have that control, then you cannot give them the database. This is the "Software-as-a-Service" Operating Model wherein you licence the ...


2

This is easy to accomplish with Module Signing. The concept is to create a certificate in the database that the user, BI_User, has access to (and contains the stored procedure), [ORMAN], which will then be used to sign the stored procedure, BI_DataDownload, that has the functionality the are currently restricted from accessing. The certificate is then ...


1

There is a simple and a complicated way. The complicated way is to join with pg_roles and get the user name from there. But since PostgreSQL hackers don't want to type more than necessary, they came up with something else: Internally, every object is not identified by its name (which is mutable) but by its “object ID”. This is the number that ...


1

I had meant to write a blog post where I explain this in detail (more than is appropriate here), with example scripts, etc, but just haven't had the time. But, rather than have this remain unanswered until I have time to write that post (test scripts are done but need to be cleaned up), I will post the basic info here and come back later to adjust once that ...


1

I previously worked for a company that had this problem with one developer. He was fired, but we also implemented a table that had the LoginName and AllowedMachine (Application Server) via a Login Trigger. This resolved our problems. Or maybe it was due to the firing.


1

I'm doing this a new way now. I set up views in database X that reference the tables I care about in Y, and then I granted SELECT permissions on those views to the role I care about in X. This change was made a while ago, and I may have also needed some linked-server magic to handle the authentication/security into Y to avoid needing to duplicate ...


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