SELECT*

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&

The below query get the information from listed servers and all the databases that are existing under that particular instance. cls Import-Module sqlps -DisableNameChecking $Query = "SELECT @@servername as ServerName,DBName=DB_NAME(),DP1.name AS DatabaseRoleName, isnull (DP2.name, 'No members') AS DatabaseUserName FROM sys.database_role_members AS DRM RIGHT OUTER JOIN sys.database_principals AS DP1 ON DRM.role_principal_id = DP1.principal_id LEFT OUTER JOIN sys.database_principals AS DP2 ON DRM.member_principal_id = DP2.principal_id WHERE DP1.type = 'R' and DP2.name='NameHere' ORDER BY DP1.name" $instanceNames = @ ( "SERVER1" , "SERVER2" , "SERVER3" , "SERVER4" ) $instanceNames | ForEach -Object{ $IndInstance = $_ #[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SQLServer.Smo") $Server = New-object ` -TypeName "Microsoft.SQLserver.Management.Smo.Server

Usually we will encounter this error when we are trying to place the backup files(.BAK) in another windows server or in a network shared folder. This issue comes up because of permission issues.  operating system error 5(access is denied.) We need add below two accounts to that particular folder or drive(to where we are placing .BAK files) security path and give FULL permissions. SQL Server Database Engine Service Account SQL Server Agent Service Account Sometimes even above approach will not work, in that case run SSMS in "run as Administrator mode" and run the command.

One day in one of my Active-Passive cluster environments while patching SQL Server 2012 environment I found the below Error. Here are the observations 1)   Failover cluster Health is good, all the roles, drives and nodes are running fine. Both the nodes are running pinging one to another.      Though the Error message says cluster is disabled or needs to start, cluster service is running fine in the services.msc Below are the error screenshots. The below command did the trick in my environment. Seems to be issue with MOF files. mofcomp C:\Windows\System32\WBEM\cimwin32.mof