Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Find dependencies on a Login Expand / Collapse
Author
Message
Posted Thursday, February 28, 2013 10:00 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 11:06 AM
Points: 557, Visits: 1,648
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.
Post #1425156
Posted Thursday, February 28, 2013 10:26 AM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Friday, November 14, 2014 7:14 AM
Points: 6,625, Visits: 1,876
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, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog | LinkedIn | Twitter
Post #1425176
Posted Thursday, February 28, 2013 2:52 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, November 20, 2014 2:30 PM
Points: 628, Visits: 870
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 + '];'
FROM sys.server_permissions per
INNER JOIN sys.server_principals pri ON per.grantee_principal_id = pri.principal_id
WHERE per.class_desc = 'SERVER'
AND pri.type IN ('S', 'U', 'G') -- SQL_LOGIN, WINDOWS_LOGIN, WINDOWS_GROUP
AND pri.is_disabled = 0
AND pri.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 + ''';'
FROM sys.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
WHERE pri.type IN ('S', 'U', 'G') -- SQL_LOGIN, WINDOWS_LOGIN, WINDOWS_GROUP
AND pri.is_disabled = 0
AND pri.name = ISNULL(@Login, pri.name);

-- GRANT DATABASE LEVEL PERMISSIONS --
CREATE TABLE #Logins (
sid VARBINARY(85)
);

INSERT INTO #Logins (sid)
SELECT sid
FROM sys.server_principals
WHERE type IN ('S', 'U', 'G') -- SQL_LOGIN, WINDOWS_LOGIN, WINDOWS_GROUP
AND is_disabled = 0
AND name = ISNULL(@Login, name)
AND principal_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)
SELECT DB_NAME()
, ''USE [?];''
UNION ALL
SELECT DB_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 + ''];''
FROM sys.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
SELECT DB_NAME()
, ''EXEC sp_addrolemember @rolename = '''''' + dr.name + '''''', @membername = '''''' + dp.name + '''''';''
FROM sys.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
SELECT DB_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 + ''];''
FROM sys.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;

SELECT DBName
, 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 + '];'
FROM sys.server_principals pri
WHERE pri.type IN ('S', 'U', 'G') -- SQL_LOGIN, WINDOWS_LOGIN, WINDOWS_GROUP
AND pri.is_disabled = 0
AND pri.name = ISNULL(@Login, pri.name)
ORDER BY pri.name;



_____________________________________________________________________
- Nate

@nate_hughes
Post #1425291
Posted Thursday, February 28, 2013 10:11 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:01 PM
Points: 7,139, Visits: 12,762
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
Post #1425343
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse