February 29, 2016 at 8:02 am
I just need help to get a proper query with which I can generate the report for direct users connecting to my SQL instance,
Is there any way to see the list of applications connecting to SQL instance please,
Help is appreciated,
Regards,
Bubby
February 29, 2016 at 8:07 am
Does sp_who or sp_who2 work.
February 29, 2016 at 8:31 am
Thanks it is useful to list some of the stuff, could you please assist with syntax with which I can pull all SQL and Windows User Accounts,
Thanks,
Bubby
February 29, 2016 at 9:27 am
Something like this will give a list of users: http://sqlserverzest.com/2013/08/06/sql-server-get-all-login-accounts-using-t-sql-query-sql-logins-windows-logins-windows-groups/
Something like this may give you what your after in terms of connections - http://sqlblog.com/blogs/adam_machanic/archive/2009/08/20/who-is-active-v8-82-harder-better-faster-stronger.aspx
February 29, 2016 at 12:04 pm
Thanks a Lot
February 29, 2016 at 2:25 pm
bubby (2/29/2016)
I just need help to get a proper query with which I can generate the report for direct users connecting to my SQL instance,Is there any way to see the list of applications connecting to SQL instance please,
Help is appreciated,
Regards,
Bubby
This may help you
/*
Script Date19th February 2010
Script AuthorPerry Whittle
Script DescriptionThis script returns all server logins, any Server Roles they hold
and server level permissions assigned*/
SELECT sp.nameAS ServerPrincipal,
sp.type_descAS LoginType,
CASE sp.is_disabled
WHEN 0 THEN 'No'
WHEN 1 THEN 'Yes'
ENDAS UserDisabled,
sp.create_dateAS DateCreated,
sp.modify_dateAS DateModified,
sp.default_database_nameAS DefaultDB,
sp.default_language_nameAS DefaultLang,
ISNULL(STUFF((SELECT ',' + ssp22.name
FROM sys.server_principals ssp2
INNER JOIN sys.server_role_members ssrm2
ON ssp2.principal_id = ssrm2.member_principal_id
INNER JOIN sys.server_principals ssp22
ON ssrm2.role_principal_id = ssp22.principal_id
WHERE ssp2.principal_id = sp.principal_id
ORDER BY ssp2.name
FOR XML PATH(N''), TYPE).value(N'.[1]',
N'nvarchar(max)'), 1, 1, N''), 'NoRolesHeld')AS ListofServerRoles,
ISNULL(STUFF((SELECT ';' + ' Permission [' + sspm3.permission_name + '] is [' +
CASE
WHEN state_desc = 'GRANT' THEN 'Granted]'
WHEN state_desc = 'DENY' THEN 'Denied]'
END AS PermGrants
FROM sys.server_principals ssp3
INNER JOIN sys.server_permissions sspm3
ON ssp3.principal_id = sspm3.grantee_principal_id
WHERE sspm3.class = 100 AND
sspm3.grantee_principal_id = sp.principal_id
FOR XML PATH(N''), TYPE).value(N'.[1]',
N'nvarchar(max)'), 1, 1, N''), 'NoServerPermissions')AS PermGrants
FROM sys.server_principals sp
WHERE sp.type IN ('S','G','U') AND sp.name NOT LIKE '##%##'
ORDER BY ServerPrincipal
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply