It was used by an application to access a database in which that account did not have any rights.
That was weird and after an investigation I've found that account in the database and the one used by the application are completely different, even though they were the same.
Here I will show you how that could happened:
DO NOT RUN FOLLOWING SCRIPTS IN PRODUCTION!!!
1. That script creates new 'TestDB' (you get an error if already have one with the same name) and creates a 'TestLogin' user with no password in that database.
| USE [MASTER]; GO CREATE DATABASE [TestDB]; GO CREATE LOGIN [TestLogin] WITH PASSWORD=N'', DEFAULT_DATABASE=[TempDB], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF; GO USE [TestDB]; GO CREATE USER [TestLogin] FOR LOGIN [TestLogin]; |
2. Here we restrict that user from reading and writing within the database.
| EXEC sp_addrolemember N'db_denydatareader', N'TestLogin'; GO EXEC sp_addrolemember N'db_denydatawriter', N'TestLogin'; GO |
3. (You can skip that script) In anther tab login to your SQL Server under newly created "TestLogin" account and try following script to get an error:
| USE [TestDB]; GO SELECT * FROM sys.objects GO |
The SELECT permission was denied on the object 'tables', database 'mssqlsystemresource', schema 'sys'.
4. Now in the main delete that account from the server. (you have to close "TestLogin" tab before).
| GO USE [MASTER]; GO DROP LOGIN [TestLogin]; GO |
5. Note: We do not delete that account from the database!!!
Login is still sitting in the database.
| SELECT * FROM [TestDB].sys.database_principals WHERE name = 'TestLogin' GO |
6. Will create the same account again. This time will not assign it to the database, but will give some extra permissions:
| CREATE LOGIN [TestLogin] WITH PASSWORD=N'', DEFAULT_DATABASE=[TempDB], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF; GO GRANT CONTROL SERVER TO [TestLogin] GO |
7. Go to another tab and login to your SQL Server under newly created "TestLogin" account, then try following script. You won't have an error!!!
| USE [TestDB]; GO SELECT * FROM sys.objects GO |
8. Run following script to verify that your "TestLogin" is still restricted user in "TestDB" database:
| SELECT mp.name, rp.name FROM [TestDB].sys.database_principals AS rp INNER JOIN [TestDB].sys.database_role_members AS rm ON rp.principal_id = rm.role_principal_id INNER JOIN [TestDB].sys.database_principals AS mp on rm.member_principal_id = mp.principal_id WHERE mp.name = 'TestLogin' GO |
You've got a situation when in "TestDB" you have restricted user "TestLogin" which is not allowed to do anything, but it does!
----------------------------------------------------------------------------
9. Here is the script to find out if you possible have similar situation with other accounts:
That script is safe to run in production.
| SET NOCOUNT ON GO DECLARE @Database_Name NVARCHAR(128); DECLARE @UnassignedUsers TABLE( Database_Name NVARCHAR(128), [Database User] NVARCHAR(128), [Server User] NVARCHAR(128) ); DECLARE @SQL NVARCHAR(1000); DECLARE database_list CURSOR FOR SELECT name FROM sys.databases; OPEN database_list; FETCH NEXT FROM database_list INTO @Database_Name; WHILE (@@fetch_status <> -1) BEGIN SET @SQL = N'SELECT ''' + @Database_Name + ''' COLLATE DATABASE_DEFAULT as Database_Name, ' + ' d.name COLLATE DATABASE_DEFAULT as [Database User], ' + ' s.name COLLATE DATABASE_DEFAULT as [Server User] ' + ' FROM [' + @Database_Name + '].sys.database_principals AS d ' + ' INNER JOIN [master].sys.server_principals AS s ' + ' ON d.name COLLATE DATABASE_DEFAULT = s.name COLLATE DATABASE_DEFAULT and d.sid != s.sid ' + ' WHERE d.name != ''public'' '; INSERT INTO @UnassignedUsers(Database_Name, [Database User], [Server User]) EXEC(@SQL); FETCH NEXT FROM database_list INTO @Database_Name; END CLOSE database_list; DEALLOCATE database_list; If EXISTS( SELECT TOP 1 1 FROM @UnassignedUsers) SELECT *, 'Account collision Detected' as Info FROM @UnassignedUsers; Else PRINT 'No Account collisions on the server'; GO SET NOCOUNT OFF GO |
10. Now clean the mess on your SQL Server (otherwise you have account with full server control and empty password)
| USE [MASTER]; GO DROP LOGIN [TestLogin]; GO DROP DATABASE [TestDB]; GO |
That kind of situation on the server appeared after restoring a database and not fixing orphaned accounts.
There is a easy fix for that:
| EXEC sp_change_users_login 'Auto_Fix', 'useraccount' |
People who did not know that solution and created new super-user did not have anything in mind, but imagine the whole it opened in SQL Server security.
These investigations inspired me to create another script to report all server users and their rights on the server. Run it and verify that you do not have too many administrators in your system:
That script is also safe to run in production.
| SELECT IsNull(l.name,pr.Name) as [Login/User Name] , pr.type_desc as [Login/User Type] , pr.default_database_name , IsNull(SUBSTRING(( SELECT ', ' + p.name FROM master.sys.server_role_members as rm INNER JOIN master.sys.server_principals as p ON rm.role_principal_id = p.principal_id and p.type = 'R' WHERE rm.member_principal_id = pr.principal_id FOR XML PATH('') ),3,8000),'NO ROLE ASSIGNED') as [Assigned Roles] , SUBSTRING(( SELECT ', ' + permission_name + '(' + ip.name + ')' FROM master.sys.server_permissions as sp INNER JOIN master.sys.server_principals as ip ON ip.principal_id = sp.grantor_principal_id WHERE sp.grantee_principal_id = pr.principal_id FOR XML PATH('') ),3,8000) as [Special Permissions] , IsNull(CAST(pr.is_disabled as varchar),'N/A') AS is_disabled , IsNull(CAST(l.is_policy_checked as varchar),'N/A') AS is_policy_checked , IsNull(CAST(l.is_expiration_checked as varchar),'N/A') AS is_expiration_checked , CASE WHEN PWDCOMPARE(l.name,l.password_hash) = 1 THEN 'Login With Password Same AS Name' WHEN PWDCOMPARE('',l.password_hash) = 1 THEN 'Login With Empty Password' ELSE 'No Problem Found' END as [Password Problem] FROM master.sys.sql_logins as l RIGHT JOIN master.sys.server_principals as pr ON l.principal_id = pr.principal_id WHERE pr.type in ('S','U','G') |
In our case it would return following record:
Login, without password and not assigned to any role has control over the server!!!
Hope you do not have accounts like this on any of your servers.
No comments:
Post a Comment