![Jon Culp [.com]](https://secureservercdn.net/198.71.233.189/cg7.7e8.myftpupload.com/wp-content/uploads/2017/11/phoenix_wjon3.jpg?time=1652463445)
Visit Streetwyze on Facebook: https://www.facebook.com/Streetwyze-442494669144227/
Monthly Archives: February 2016
Drop Login and Users in MS SQL Server
This code will drop the login and users for all databases for a provided user name. and reports on its progress. This only works where the user and login match. This script does not check if users own any objects.
|
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 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 |
/********************************************* DROP LOGIN AND -- USERS FOR ALL DBs IN A SERVER Possible errors could be if a user is the owner of a database object. *********************************************/ SET NOCOUNT ON -- USE master --DECLARE VARIABLES DECLARE @Dropping_Windows_Logon_Name NVARCHAR(MAX) = N'starwars\amlegault' --SPECIFY -- USER TO DROP, typical format: domain\user DECLARE @tbl_Databases TABLE (Name NVARCHAR(MAX)) DECLARE @Database_Name NVARCHAR(MAX) DECLARE @SQL_Drop_Login NVARCHAR(MAX) DECLARE @SQL_Drop_DB_Users NVARCHAR(MAX) PRINT 'Begin Login and User Drop Procedure for Windows Logon Name ' + @Dropping_Windows_Logon_Name + ' on server ' + @@SERVERNAME --DROP LOGIN FROM SERVER PRINT CHAR(0x0D) + CHAR(0x0A) + 'Begin Server Principal Drop Procedure' IF EXISTS (SELECT * FROM sys.server_principals WHERE name = @Dropping_Windows_Logon_Name) BEGIN TRY SET @SQL_Drop_Login = 'DROP LOGIN [' + @Dropping_Windows_Logon_Name +']' EXEC (@SQL_Drop_Login) PRINT 'Server Principal Dropped' END TRY BEGIN CATCH PRINT 'Server Principal Found But Could Not Drop' END CATCH ELSE PRINT 'Server Principal Not Found' ---- USER DROP PROCEDURE PRINT CHAR(0x0D) + CHAR(0x0A) + 'Begin User Drop Procedure' --LOAD DATABASE NAMES INTO TEMP TABLE TO -- USE FOR LOOP INSERT INTO @tbl_Databases (Name) SELECT Name FROM master.sys.databases WITH(NOLOCK) --LOOP THROUGH EACH DATABASE AND DROP -- USER IF THEY EXIST WHILE EXISTS (SELECT TOP 1 * FROM @tbl_Databases) BEGIN SET @Database_Name = (SELECT TOP 1 Name FROM @tbl_Databases) SET @SQL_Drop_Login ='-- USE '+@Database_Name +' IF EXISTS (SELECT * FROM sys.database_principals WHERE name = ''' + @Dropping_Windows_Logon_Name + ''' AND sys.database_principals.type = ''U'') BEGIN TRY DROP -- USER [' + @Dropping_Windows_Logon_Name +'] PRINT '' Dropped User in '+@Database_Name+''' END TRY BEGIN CATCH PRINT '' User Found But Could Not Drop in '+@Database_Name+''' END CATCH ELSE PRINT '' User Not Found in '+@Database_Name+''' ' EXEC (@SQL_Drop_Login) DELETE FROM @tbl_Databases WHERE Name = @Database_Name END PRINT 'End User Drop Procedure' --END PRINT CHAR(0x0D) + CHAR(0x0A) + 'End Login and User Drop Procedure for Windows Logon Name ' + @Dropping_Windows_Logon_Name + ' on server ' + @@SERVERNAME SET NOCOUNT OFF |
Orphan Users in MS SQL Server
Checks all databases on a server for any users who do not have a correlated login, based on SID. Uses the undocumented command of sp_msforeachdb. You could put in a loop if you want to avoid this command.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
DECLARE @orphan_users TABLE (Server NVARCHAR(MAX),DBName sysname, [user_name] sysname,type_desc nvarchar(60),default_schema_name sysname, create_date datetime, modify_date DATETIME, [sid] VARBINARY(85)) INSERT INTO @orphan_users (Server, DBname,[user_name],type_desc,default_schema_name,create_date,modify_date,[sid]) EXEC sp_msforeachdb 'use [?] SELECT @@SERVERNAME ,DB_NAME() DBname ,name ,type_desc ,default_schema_name ,create_date ,modify_date ,[sid] from sys.database_principals where type in (''G'',''S'',''U'') and [sid] not in ( select [sid] from sys.server_principals where type in (''G'',''S'',''U'') ) and name not in (''dbo'',''guest'',''INFORMATION_SCHEMA'',''sys'',''MS_DataCollectorInternalUser'')' SELECT * FROM @orphan_users --WHERE user_name LIKE '%SomeUser%' /*optional*/ ORDER BY user_name |
Publish Google Analytics Reports to the Web using Power BI
There is a feature in Power BI which allows you to connect to your google analytics data, and there is a new preview feature which allows publishing content to the web in an iframe. In this post I have combined both features together to display my website statistics to the web.
- Connect to Google Analytics: https://powerbi.microsoft.com/en-us/documentation/powerbi-content-pack-google-analytics/
- Publish to Web: https://powerbi.microsoft.com/en-us/blog/announcing-power-bi-publish-to-web/
Here is the result:
(View Larger)
Note: PowerBI has trouble rendering in Chrome in some cases. If you are using Chrome and can not see the dashboard then try another browser. Hopefully Microsoft will resolve this problem.
Video: Business Intelligence with Power BI
I’ve been catching myself up on what Microsoft is doing lately regarding Business Intelligence. After spending days reading Microsoft documentation and testing some various configurations of Power BI, Gateway,Azure, and SSRS, I came across this video that goes over some history and then gets right into Power BI and some competing tools. I suspect Microsoft is working toward merging Power BI and SSRS based on some of their SQL Server public previews.
Get Database Sizes in PestgreSQL
Returns the database name, owner, and size on a given server. Tested in PostgrSQL 9.2 and 9.3.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
SELECT d.datname AS Name ,pg_catalog.pg_get_userbyid(d.datdba) AS Owner ,CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT') THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname)) ELSE 'No Access' END AS SIZE FROM pg_catalog.pg_database d ORDER BY CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT') THEN pg_catalog.pg_database_size(d.datname) ELSE NULL END DESC |
SQL Server Job to Capture Reads
We needed to capture physical reads over time from our SQL server. One method we decided to deploy was to check sys.dm_exec_query_stats periodically and capture the differences between each check. The way I am doing it below is not 100% accurate because sys.dm_exec_query_stats is based on cached plans and the server will let clear the plan data over time. This means that when the differences are calculated any plan that was cleared from cache would not be included in the current check, but would still be in the comparison record. This could be solved by storing and comparing at the plan level but for my purposes I prefer to store summary information and do not mind this issue because I am running my comparison every hour and then will be averaging the results over time, and the result does not need to be exactly precise, just close enough to give us a general understanding.
This is the query inside the job:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
IF NOT EXISTS (SELECT * FROM dba.sys.objects WHERE object_id = OBJECT_ID(N'dba.[dbo].[dm_exec_query_Stats_summary]') AND type in (N'U')) CREATE TABLE dba.dbo.dm_exec_query_Stats_summary ( SysDateTime DATETIME NOT NULL, PhysicalReads BIGINT NOT NULL, LogicalReads BIGINT NOT NULL , PhysicalReads_Delta BIGINT NULL, LogicalReads_Delta BIGINT NULL ) INSERT INTO dba.dbo.dm_exec_query_Stats_summary (SysDateTime, PhysicalReads, LogicalReads, PhysicalReads_Delta, LogicalReads_Delta) SELECT A.SYSDATETIME, A.PhysicalReads, A.LogicalReads ,A.PhysicalReads - B.PhysicalReads ,A.LogicalReads - B.LogicalReads FROM( SELECT SYSDATETIME() AS SYSDATETIME ,SUM(total_physical_reads) as PhysicalReads ,sum(total_logical_Reads) as LogicalReads FROM sys.dm_exec_query_Stats A )A OUTER APPLY ( SELECT TOP 1 B.PhysicalReads, B.LogicalReads FROM dba.dbo.dm_exec_query_Stats_summary B WHERE B.sysdatetime < A.SYSDATETIME ORDER BY B.SysDateTime DESC )B |
This is what the table looks like with a few records. Note that this is a result of running the job multiple times with a few seconds between each interval.
