February 28, 2013 at 10:00 am
This week I noticed a SQL login on a SQL 2008 R2 production server I and the other DBAs here were not not familiar with. It is in the sysadmin group, which gives it full rights to the SQL instance. Based on the create date, I concluded it was created by a vendor engineer during an upgrade/migration a few years ago. I contacted the person who did this work, who replied he did not specifically recall creating this login, but he recognized it as a login that is sometimes created on cutomers' systems for vendor support work. He said there were no dependencies on it and that it could be safely disabled until it was needed for support work.
Is there a query or script I could run which would identify any owned objects or other dependencies on a login, so I can be close to 100 percent certain that disabling this logon will not break anything? I realize this might not identify hidden dependecies from vendor applications that might use this logon (we're aware of the logins they are configured to use), but at a certain point you have to risk some temporary disruption to secure your system.
February 28, 2013 at 10:26 am
If it's sysadmin level, likely it doesn't own any objects as it would have mapped in as dbo to each database. What you can do:
- query sys.database_principals in each DB to see if there are any users that have a matching SID to the login (sys.server_principals).
- If that shows up as positive, query sys.objects, sys.schemas, etc., to see if there are any owned objects, etc.
- Again, if that shows up as positive, query sys.database_permissions in each DB to see if there are explicit permissions for that user (also check roles and permissions assigned to those roles)
- Do the same thing against sys.server_permissions as you did against sys.database_permissions
Again, this only tells you anything explicitly assigned. If it had access as a member of the sysadmin role, chances are there are no explicit objects owned and no explicit permissions. In this case, your best bet is to use a server side trace filtered to capture the actions of just that login.
K. Brian Kelley
@kbriankelley
February 28, 2013 at 2:52 pm
I've been tinkering with a reverse engineering script that might help.
SET NOCOUNT ON;
DECLARE @login NVARCHAR(128)
, @SQL VARCHAR(MAX);
SET @login = 'applogin';
-- GRANT SERVER LEVEL PERMISSIONS --
SELECT[Login] = pri.name
, [GrantServerPermissionSQL] = per.state_desc collate database_default + ' ' + per.permission_name collate database_default + ' TO [' + pri.name collate database_default + '];'
FROMsys.server_permissions per
INNER JOIN sys.server_principals pri ON per.grantee_principal_id = pri.principal_id
WHEREper.class_desc = 'SERVER'
ANDpri.type IN ('S', 'U', 'G') -- SQL_LOGIN, WINDOWS_LOGIN, WINDOWS_GROUP
ANDpri.is_disabled = 0
ANDpri.name = ISNULL(@Login, pri.name)
ORDER BY pri.name;
-- GRANT SERVER LEVEL ROLES --
SELECT[Login] = pri.name
, [GrantServerRoleSQL] = 'EXEC sp_addrolemember @rolename = ''' + rpri.name + ''', @membername = ''' + pri.name + ''';'
FROMsys.server_principals pri
INNER JOIN sys.server_role_members rm ON pri.principal_id = rm.member_principal_id
INNER JOIN sys.server_principals rpri ON rpri.principal_id = rm.role_principal_id
WHEREpri.type IN ('S', 'U', 'G') -- SQL_LOGIN, WINDOWS_LOGIN, WINDOWS_GROUP
ANDpri.is_disabled = 0
ANDpri.name = ISNULL(@Login, pri.name);
-- GRANT DATABASE LEVEL PERMISSIONS --
CREATE TABLE #Logins (
sid VARBINARY(85)
);
INSERT INTO #Logins (sid)
SELECTsid
FROMsys.server_principals
WHEREtype IN ('S', 'U', 'G') -- SQL_LOGIN, WINDOWS_LOGIN, WINDOWS_GROUP
ANDis_disabled = 0
ANDname = ISNULL(@Login, name)
ANDprincipal_id != 1; -- sa
CREATE TABLE #DBSecurity (
RowId INT IDENTITY(1,1)
, DBName NVARCHAR(128)
, SQLStmt VARCHAR(MAX)
);
SET @SQL = 'USE [?];
INSERT INTO #DBSecurity (DBName, SQLStmt)
SELECTDB_NAME()
, ''USE [?];''
UNION ALL
SELECTDB_NAME()
, ''IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = '''''' + dp.name + '''''') AND EXISTS (SELECT 1 FROM sys.server_principals WHERE name = '''''' + sp.name + '''''') CREATE USER ['' + dp.name + ''] FOR LOGIN ['' + sp.name + ''];''
FROMsys.database_principals dp
INNER JOIN sys.server_principals sp ON dp.sid = sp.sid
INNER JOIN #Logins tmp ON tmp.sid = dp.sid
UNION ALL
SELECTDB_NAME()
, ''EXEC sp_addrolemember @rolename = '''''' + dr.name + '''''', @membername = '''''' + dp.name + '''''';''
FROMsys.database_principals dp
INNER JOIN sys.database_role_members rm ON rm.member_principal_id = dp.principal_id
INNER JOIN sys.database_principals dr ON rm.role_principal_id = dr.principal_id
INNER JOIN #Logins tmp ON tmp.sid = dp.sid
UNION ALL
SELECTDB_NAME()
, ''IF EXISTS (SELECT 1 FROM sys.objects WHERE name = '''''' + o.name + '''''') AND EXISTS (SELECT 1 FROM sys.database_principals WHERE name = '''''' + dp.name + '''''') '' + p.state_desc + '' '' + p.permission_name + '' ON ['' + s.name + ''].['' + o.name collate database_default + ''] TO ['' + dp.name + ''];''
FROMsys.database_principals dp
INNER JOIN sys.database_permissions p on p.grantee_principal_id = dp.principal_id
INNER JOIN sys.objects o on p.major_id = o.object_id
INNER JOIN sys.schemas s on o.schema_id = s.schema_id
INNER JOIN #Logins tmp ON tmp.sid = dp.sid;
';
EXEC sp_msforeachdb @SQL;
SELECTDBName
, SQLStmt
FROM#DBSecurity
order by RowId;
DROP TABLE #Logins;
DROP TABLE #DBSecurity;
-- SET DEFAULT DB --
SELECT[Login] = pri.name
, [SetDefaultDBSQL] = 'ALTER LOGIN [' + pri.name + '] WITH DEFAULT_DATABASE = [' + pri.default_database_name + '];'
FROMsys.server_principals pri
WHEREpri.type IN ('S', 'U', 'G') -- SQL_LOGIN, WINDOWS_LOGIN, WINDOWS_GROUP
ANDpri.is_disabled = 0
ANDpri.name = ISNULL(@Login, pri.name)
ORDER BY pri.name;
_____________________________________________________________________
- Nate
February 28, 2013 at 10:11 pm
In addition to object ownership within a database make sure the login does not own any databases themselves or any Agent jobs.
SELECT *
FROM sys.databases
WHERE SUSER_SNAME(owner_sid) = N'login_name_in_question';
SELECT *
FROM msdb.dbo.sysjobs
WHERE SUSER_SNAME(owner_sid) = N'login_name_in_question';
After ruling out ownership of any kind consider creating a server-trigger that writes a row to a table any time the login in question is used to connect to the instance. This would help know if any application or person was actively using the login. If nothing turns up, from there I might disable the login for a while to see if anyone steps forward asking about the login. I would hope to either leave it disabled in between the vendor's maintenance needs or drop it and re-add when the vendor needs it, then drop it again when they're done.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply