Find a specific user across all databases in a SQL instance using SP_MSForeachdb.

The following is used to find a specific user across all databases in a SQL instance using SP_MSForeachdb.  Roles for each user are concatenated per database in one row.  A user can be listed multiple times
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
DECLARE @DB_USers TABLE
(DBName sysname, UserName sysname, LoginType sysname, MemberRole varchar(max),create_date datetime,modify_date datetime)
INSERT @DB_USers
EXEC sp_MSforeachdb 
'use [?]
SELECT ''?'' AS DB_Name,
case prin.name when ''dbo'' then prin.name + '' (''+ (select SUSER_SNAME(owner_sid) from master.sys.databases where name =''?'') + '')'' else prin.name end AS UserName,
prin.type_desc AS LoginType,
isnull(USER_NAME(mem.role_principal_id),'''') AS MemberRole ,create_date,modify_date
FROM sys.database_principals prin
LEFT OUTER JOIN sys.database_role_members mem ON prin.principal_id=mem.member_principal_id
WHERE prin.sid IS NOT NULL and prin.sid NOT IN (0x00) and
prin.is_fixed_role <> 1 AND prin.name NOT LIKE ''##%'''
SELECT dbname,username ,logintype ,create_date ,modify_date,
STUFF((SELECT ',' + CONVERT(VARCHAR(254),Memberrole)
FROM @DB_USers user2
WHERE
user1.DBName=user2.DBName AND user1.UserName=user2.UserName
FOR XML PATH(''))
,1,1,'') AS Permissions_user
FROM @DB_USers user1 WHERE user1.UserName LIKE '%vall%' ----- UPDATE this row to a like name you are looking for
GROUP BY
dbname,username ,logintype ,create_date ,modify_date
ORDER BY DBName,username
Omit the following users:
– No Guest Acxcounts, Skip fixed database roles such as DB_OWNER , db_datawriter, db_datareader and Principals with NULL SID ; those are internal to the DB such as INFORMATION_SCHEMA & SYS listed as ##

SYSADMINS are not part of a users list as they have access to everything on server. You can get list SYSADMIN users by running following code
1
2
3
4
5
SELECT p.name AS [Name] ,r.type_desc,r.is_disabled,r.create_date , r.modify_date,r.default_database_name
FROM sys.server_principals r
INNER JOIN sys.server_role_members m ON r.principal_id = m.role_principal_id
INNER JOIN sys.server_principals p ON p.principal_id = m.member_principal_id
WHERE r.type = 'R' and r.name = N'sysadmin'
– If a windows login is part of a windows group then you may need to look at the members of this Windows group to identify who can access this database.

Comments

Post a Comment

Popular posts from this blog

How to mass update all or some sql server agent job step commands

How to mass update all or some sql server agent job step and commands using TSQL.   This will use the built-in Replace function to search text in SQL agent jobs  SQL Agent Job information is stored in MSDB and can reviewed using some of the following queries: SELECT * FROM msdb.dbo.SysJobs SELECT * FROM msdb.dbo.SysJobSteps -- OR SELECT a.name as job_name, b.step_name, b.subsystem, b.command, b.database_name, a.enabled       ,a.description, a.date_created, b.job_id, b.step_id   FROM [dbo].[sysjobsteps] as b   INNER JOIN sysjobs as a ON a.job_id = b.job_id Since I will be focusing on the Commands column I will look at using this query to extract data into a temp table so that I can test any updates with the replace step before doing an update to MSDB. This would be your opportunity to archive or save any commands as needed.  Some people may prefer to generate all the syntax necessary to use EXEC sp_update_jobstep so you a...
Read more

SQL Monitoring for Blocking and Locking Capture and Log to table with sp_WhoIsActive or sp_BlitzWho

Image
You can use  sp_WhoIsActive or sp_BlitzWho and log the capture data to a table.   In my example I will use sp_whoisactive as I am more familiar with its use and data collection, however I will look at creating a future post using BlitzWho.  I will provide a few examples that were used to determine that the paid SQL monitoring products can be fancy tools but can be useless if you don't understand the basic concepts or even an understanding of how we capture similar data or in some cases more finely tuned to a specific scenario or use case.  Sometimes these paid solutions are incorrect or things can simply go wrong (Services Fail to start or the data is aggregated and misses something like the query commands and SQL Fragments) and it's always good to plan ahead and be prepared.   Sp_WhoIsActive is a procedure written by Adam Machanic. It can be found here:  sp_whoisactive downloads Sp_BlitzWho is a procedure written by the Brent Ozar team....
Read more

Trace Flag 8048 SQL Server 2012 prior to SP and Trace Flag 3449 after SP4

If you were using SQL Trace Flag 8048 and are now on SQL 2012 SP4 you don't technically need to be using this trace flag any more.  You can also enable SQL Trace Flag 3449. If you enable global Trace Flag 3449 (and you are on SQL Server 2012 SP4 or later or SQL Server 2014 SP2 or later), you will get much better performance by avoiding a FlushCache call in a number of different scenarios. Global Trace Flag 3449 takes effect immediately, with no restart required. You can set TF 3449 as a startup trace flag or in an Agent job using the following syntax:  DBCC TRACEON (3449, -1) Some of the main use case for Trace Flag 8048 in SQL Server 2012  as stated by CSS SQL Server Engineers : Backup Database Backup LOG Create Database Add file to Database Restore LOG (2 flushcache per restore log without recovery) Recover Database Shrink Database File SQL Server ‘graceful’ Shutdown SQL Trace Flag 8048 was first implemen...
Read more