Hi folks,
We have a medical office management program which uses SQL Server Express on the back end. In order to meet American Medical Association standards and ensure that data is not manipulated by users, we must restrict SSMS login access using Windows authentication for all windows users. Is there any way to do this using TSQL commands? Thanks!
Are all users now able to login to the sql server using SSMS?
SQL Server Express - by default - is configured to use Windows Authentication only. Unless you modify that during installation to use both then it will only allows windows authentication.
If users have access to SQL Server and the databases directly - regardless of type of account, and those users have INSERT/UPDATE/DELETE access directly to the tables (or through roles) then you won't meet the requirement.
The only way to be absolutely sure that end users cannot manually manipulate the data in a database is to not give them access in the first place. Access would only be given to the application account...
The next best option is to only grant access to execute stored procedures. No direct access to any tables in the database.
When creating the instance of SQL Server, we add a login that uses SQL Server Authentication for our own access. However, we haven't figured out how to remove all logins which are under Windows Authentication. Currently, after the creation of the SQL Server instance, we execute a Drop Login [BUILTIN\Administrators] and Drop Login [BUILTIN\Users] but this does not appear to be sufficient to remove Windows login access for all existing (and perhaps future) Windows users.
What version are you installing? Those groups haven't been included in SQL Server in a very long time and would only exist on instances that were upgraded from a prior version where they were included.
As far as I am aware - there are no other windows users automatically included. During installation you are prompted to add logins that will be assigned sysadmin - and those can be individuals or groups.
If you have other windows logins - those are being added through some other process.
Some prior installations were made some time ago with SQL Server 2014 but all our current installs are on SQL Server 2019. My understanding is that anybody can currently use SSMS with Windows Authentication unless it is otherwise specified upon installation, not the other way around where logins would have to be created for each Windows user. In other words, you have to create SQL logins but the SA login and the Windows logins are there by default. I may be wrong on this but that's my observation.
During installation - if you select mixed authentication you have to specify the sa password. If you don't specify mixed, then all you have is windows authentication.
During installation - you are prompted to add accounts as sysadmin. If you add a group or individual then that group/individual will be added as a login and granted sysadmin.
Windows users are not granted access by default. The only logins created are those that are required to run SQL Server. There will be several ## accounts which are disabled and used for things like policy based management, SSIS cleanup jobs (if installed), etc.
NT AUTHORITY\SYSTEM and the NT SERVICE... accounts are also included. These are used by each service to access SQL Server.
All windows users are not able to access SQL Server. Only logins added during the installation (as sysadmins) or after installation (by sysadmin or securityadmin). And those logins will only have permissions that are specifically granted. If a login is added but a user isn't created in any database then that login will only have public access - which by default is very limited.
To further limit access - the guest account is disabled by default in all databases created on that new instance. If the databases are restored and the guest account is enabled in the source database - then any login on that instance will have guest access to the database. That account should not be enabled - ever.
To your original question - if the login hasn't been added to SQL Server, then it doesn't have access. If the login has been added but no user in any database has been added - the login only has access to the server and defaults to public access. Any additional access needed must be specifically granted.
If you have logins you want to limit - you can also REVOKE or DENY depending on the privilege. But you could also just remove or disable the login if the goal is to prevent that login from accessing SQL Server.
Thanks for that very detailed explanation because that's not how I had perceived it. Now, when you say:
But you could also just remove or disable the login if the goal is to prevent that login from accessing SQL Server.
How can we do this programmatically using TSQL? The idea would be to disable all logins which are not the login that we created or any of the following:
You can disable a login using ALTER LOGIN:
Thanks! Here I was trying to figure out how to remove the Windows logins when I could instead just disable them. This said, for anyone who might read this post in the future, all I had to do from this point on was to list the Windows logins using the following and then disabling them:
select sp.name as login,
sp.type_desc as login_type,
sl.password_hash,
sp.create_date,
sp.modify_date,
case when sp.is_disabled = 1 then 'Disabled'
else 'Enabled' end as status
from sys.server_principals sp
left join sys.sql_logins sl
on sp.principal_id = sl.principal_id
where sp.type not in ('G', 'R') and sp.type_desc = 'Windows_Login' and not left(sp.name,2) = 'NT'
order by sp.name
Thanks a lot for taking the time to help. Really, really appreciated !!!
Just one more point - you can generate the code to alter the logins in that same query. You can add another column using something like: concat('Alter Login ', quotename(sp.name), ' Disable;')
Then you can just copy/paste that column into a query window, validate and execute.