SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Find dependencies on a Login


Find dependencies on a Login

Author
Message
dan-572483
dan-572483
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3607 Visits: 2000
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.
K. Brian Kelley
K. Brian Kelley
Keeper of the Duck
Keeper of the Duck (24K reputation)

Group: Moderators
Points: 24994 Visits: 1917
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
RP_DBA
RP_DBA
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1513 Visits: 1070
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
Orlando Colamatteo
Orlando Colamatteo
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39964 Visits: 14412
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search