DECLARE @name sysname,
@sql nvarchar(4000),
@maxlen1 smallint,
@maxlen2 smallint,
@maxlen3 smallint
DECLARE @Table TABLE
(DBName VARCHAR(1000),UserName VARCHAR(1000),RoleName VARCHAR(1000))
IF EXISTS (SELECT TABLE_NAME
FROM tempdb.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE '#tmpTable%')
DROP TABLE #tmpTable
CREATE TABLE #tmpTable
(
DBName sysname NOT NULL ,
UserName sysname NOT NULL,
RoleName sysname NOT NULL
)
DECLARE c1 CURSOR for
SELECT name FROM master.sys.databases
OPEN c1
FETCH c1 INTO @name
WHILE @@FETCH_STATUS >= 0
BEGIN
SELECT @sql =
'INSERT INTO #tmpTable
SELECT N'''+ @name + ''', a.name, c.name
FROM [' + @name + '].sys.database_principals a
JOIN [' + @name + '].sys.database_role_members b ON b.member_principal_id = a.principal_id
JOIN [' + @name + '].sys.database_principals c ON c.principal_id = b.role_principal_id
WHERE a.name != ''dbo'''
EXECUTE (@sql)
FETCH c1 INTO @name
END
CLOSE c1
DEALLOCATE c1
SELECT @maxlen1 = (MAX(LEN(COALESCE(DBName, 'NULL'))) + 2)
FROM #tmpTable
SELECT @maxlen2 = (MAX(LEN(COALESCE(UserName, 'NULL'))) + 2)
FROM #tmpTable
SELECT @maxlen3 = (MAX(LEN(COALESCE(RoleName, 'NULL'))) + 2)
FROM #tmpTable
SET @sql = 'SELECT LEFT(DBName, ' + LTRIM(STR(@maxlen1)) + ') AS ''DB Name'', '
SET @sql = @sql + 'LEFT(UserName, ' + LTRIM(STR(@maxlen2)) + ') AS ''User Name'', '
SET @sql = @sql + 'LEFT(RoleName, ' + LTRIM(STR(@maxlen3)) + ') AS ''Role Name'' '
SET @sql = @sql + 'FROM #tmpTable '
SET @sql = @sql + 'ORDER BY DBName, UserName'
insert into @Table
EXEC(@sql)
select * from @Table
where UserName in ('UserNamesHere')
CLS
Import-Module sqlps -DisableNameChecking
#provide instance name
$instanceNames=@("Instance1","Instance2","Instance3","Instance4","Instance5")
$instanceNames|ForEach-Object{
$SingleInstance=$_
$server=New-Object `
-TypeName Microsoft.SQLServer.Management.Smo.Server `
-ArgumentList $SingleInstance
$server.Databases|
ForEach-Object{
$database=$_
$users=$_.Users
$users|
Where-Object{-not($_.IsSystemObject)}|
Select @{N="InstanceName";E={$server.DomainInstanceName}},
@{N="Login";E={$_.login}},
@{N="User";E={$_.Name}},
@{N="DatabaseName";E={$database}},
@{N="DBRoles";E={$_.Enumroles()}},
@{N="ObjectPermissions";E={$database.EnumObjectPermissions($_.Name)}}
}}|Where-Object {$_.User -like '*UserNameHere*'}|Format-Table -Property InstanceName,Login,User,DatabaseName,ObjectPermissions,DBRoles -Wrap
Below query will give the results at object level and at different databse level.
cls
Import-Module sqlps -DisableNameChecking
$servername="instanceNamehere"
$Query="SELECT DISTINCT db_name() as dbname,pr.principal_id, pr.name AS [UserName], pr.type_desc AS [User_or_Role], pr.authentication_type_desc AS [Auth_Type], pe.state_desc,
pe.permission_name, pe.class_desc, o.[name] AS 'Object'
FROM sys.database_principals AS pr
JOIN sys.database_permissions AS pe ON pe.grantee_principal_id = pr.principal_id
LEFT JOIN sys.objects AS o on (o.object_id = pe.major_id)"
$dbnames=@("db1","db2","db3")
$dbnames|
ForEach-Object{
$Inddbname=$_
Invoke-Sqlcmd `
-ServerInstance $servername `
-Query $Query `
-Database $Inddbname
}|Where-Object{$_.UserName -like "*UserNameHere*"}|Format-Table -AutoSize
Comments