Remove all logins except mine

Hi all !

It looks like some people have taken it upon themselves to access SSMS using Windows logins. Is there any way for me to remove all logins except my own (which is through SQL Server Authentication) to ensure that nobody can access the data? Otherwise, what would be a good alternative?

Thanks!!!

In the security node , in ssms, do you see all these users listed?

Yes. Those are all the standard Windows users which get added during the original SQL Server installation. Now, I should also mention that what I would be looking for is some kind of TSQL command in order to delete the users because there is a SQL Server located at each of my clients' sites, and there are hundreds, so I can't do so manually through SSMS given that it would take weeks to complete such a task. I have the capability to send TSQL commands to all sites simultaneously though.

Thanks!

hi

please be very very careful using this script

exclude your login .. then run the script

declare @sql nvarchar(max)
set @sql = ''

SELECT @sql = @sql+
'
print ''Dropping '+name+'''
drop user '+name+'
'
FROM
   dbo.sysusers
WHERE
   name NOT IN('dbo','guest','INFORMATION_SCHEMA','sys','public')
   AND LEFT(name,3) <> 'db_'
   AND not your login .. !!!
order by
   name

--print @sql

execute ( @sql )

I think it would be brave not to a least disable a login for a few weeks before dropping it!

ALTER LOGIN YourLogin DISABLE;
1 Like

Just noticed this.
There is no way that you can stop an administrator of a Windows box adding themselves as a login in SQL server with sysadmin rights. They could also add a SQL login and set that as sysadmin. If you want to stop clients getting direct access to your application database then you will have to host the application.

1 Like

Thanks for the information folks! Greatly appreciated!

But Ifor, whoa, what you are suggesting entails huge security issues!!! This would represent a presumption by Microsoft that nobody can crack a Windows user password in order to then add a login and access the data in a SQL Server. Somehow, intuitively, this sounds incorrect.... or am I just being completely naive...?

SQL Server has always assumed that a Windows Administrator can gain access to SQL Server. ie The Microsoft view is that a Windows Administrator can gain access to anything.

I think Oracle may be better at splitting roles. The only way to completely secure SQL Server is for you to be the Windows Administrator. ie Host the system.

eg

ps I think the way most software vendors get around potential problems with this is to have the licence say that adding objects, directly querying the production database etc will invalidate the support agreement.
To be fair, most do allow for reporting queries as long as they do not consistantly cause blocking etc.

As the data belongs to the client, it is also reasonable for them to control their own backups, set up replication, always on etc.

"As the data belongs to the client, it is also reasonable for them to control their own backups, set up replication, always on etc."

That's rarely exactly true and, to me, that represents the fundamental issue. The databases (the container) belongs to the developer. The data entered by the client (content in the container) belongs to the client. However, much of the contents in the databases is proprietary information of the developer. In other words, the client can no more justify breaking into databases to get to his limited content than he can justify breaking into a bank to get to his money. That's why OJ Simpson went to jail.

So it seems to me that Microsoft's position is very tenuous and relinquishes the rights of developers directly to the end-client, irrespective of developer-client agreements. Seems to me like the management of SQL user logins should be tailored to allow developer-client agreements to be respected by the parties.

As I am not a lawyer I cannot comment on who has rights to what.

From a development prospective, if there is data in your database which you do not want anyone else to ever see, then the database should not be installed on your clients' machines with MS SQL Server.
Microsoft have always been upfront about how it's product works so it is up to you, as the developer, to choose the right product.

The only database I am aware of which might provide the level of security you want on a client's machine is Oracle. This is usually a lot more expensive to license.

Most software vendors, who want to protect their intellectual property, just have confidentially clauses written into the license contract.

1 Like

Are you running licensed version of MS SQL at client?
The other option might be encrypting the db. Which would mean you will have to make a few changes on your application side.

Maybe I am being a bit dense but how does this stop a sysadmin viewing the contents of a database?

I suppose the application could encrypt the contents of the sensitive columns. Maybe the OP could look at Always Encrypted but that may be complicated to setup.

Ifor: thanks for the comments. Very true, I did not read the fine print when selecting SQL Server years ago. And from experience, we've found it hard to enforce software licensing agreements because the clients don't read them, don't understand them even if they do read them, and you don't want to lose clients because of "misunderstandings". Hosting the DBs on a web platform is the solution but it just plain ruins performance.

Yosiasz: SQL Express at the clients's sites. Encryption is already used to a large extent even though it provides a great detriment to performance.

Thanks folks! Your insight is truly appreciated.

if encrypted then the app would have to also use some decryption mechanism. The customer wont need to know the decryption, they wont so they cant see the data.

On the sysadmin part: dont allow sysadmin. give the app user account (most probably SQL auth) only the perms it will need for app to run properly. Maybe dbreader dbwriter only

grant execute on procs, if OP is using procs.

You could maybe look at some sort of hybrid solution.

SQL Express suggests small databases with few users and not reading licensing agreements suggests small companies.

Depending on your application you might be able to host your sensitive data and do some sort of RPC (Web Services etc) to get relevant sensitive data before combining it with locally held client data.

Whoo, now that's an interesting idea. To put only the sensitive data on the web, leaving everything else local. The thing is that, as you surmised, these are all small clients who do not have redundant internet connections. So if their service or network is down, they're paralyzed. But I have to say, that idea stems from pure genius.