SQLServer Module in PowerShell

SQLServer Module in PowerShell

If you are not already using this modal as a DBA it’s well worth a go.  In it you will find some great ways to administrate your database servers from your desk top.

To install it just open ISE and type:-

Install-Module –Name  SQLServer

Or to get the very latest (at the time of writing)

Install-Module -Name SqlServer -RequiredVersion 21.1.18080

You might find that you don’t have admin permissions to install it for all users, you can get round this by installing just for your user with:-

Install-Module -Name SqlServer -Scope CurrentUser

Also you may get an issue as parts of this modal may exist, so go for the below that will allow overwrite:-

Install-Module -Name SqlServer –AllowClobber

So the whole thing:-

Install-Module -Name SqlServer -RequiredVersion 21.1.18068  -Scope CurrentUser  -AllowClobber

So what can it do?

Well almost anything, have a look for yourself here:-

Get-Command -Module SqlServer -CommandType Cmdlet | Out-GridView

But here are a few of the top picks I think are worth an explore.

Get-SQLErrorLog

Use this to search the SQL error log for set words or strings, can be very useful for looking for stack dumps or log in failures.

New-SQLAvailbilityGroupListner

Does what it says on the tin, there are a number of availability group modules that are worth a look.

Start-SQLInstance , Stop-SQLinstance

Great for controlling SQL from afar.

Remove-SQlLogin.

Again great if you need to remove a login on all servers quickly.

Have a look about, and tell me your favourite commands.

Audit nightmare - show all users with write access to a database

we have all been asked that age old question by an auditor to show all users that have write access to a database.

We all know its not an easy thing to show.

I think this script answers that question:-

select 'below shows users who have explicit insert, update, delete, alter, or control permissions:'

Select P.name As Principal,

       class_desc As PermissionLevel,    permission_name As PermissionGranted,

       ObjectName = Case class When 0 Then DB_NAME()

              When 1 Then OBJECT_SCHEMA_NAME(major_id) + N'.' + OBJECT_NAME(major_id)

              End

From sys.database_permissions As DP

Inner Join sys.database_principals As P On P.principal_id = DP.grantee_principal_id

Where permission_name In ('insert', 'update', 'delete', 'control', 'alter')

And state = 'G'; -- Grant

select 'below shows users who have built-in role membership that allows write operations:'

DECLARE @command varchar(1000) 

SELECT @command = 'IF ''?'' NOT IN (''master'', ''model'', ''msdb'', ''tempdb'') 

BEGIN USE ? ;

Select ''?'' as databasename,  P1.name As Principal,

       P2.name As DBRole

From sys.database_principals As P1

Inner join sys.database_role_members As RM On RM.member_principal_id = P1.principal_id

Inner join sys.database_principals As P2 On P2.principal_id = RM.role_principal_id

Where P2.name in (''db_owner'', ''db_datawriter'', ''db_ddladmin'')

end'

CREATE TABLE #role (databasename varchar(400), principal varchar(400) , dbrole varchar (400))

INSERT INTO #role 

EXEC sp_MSforeachdb @command

SELECT * FROM #role

DROP table #role

select 'below shows users who have DBO on a database'

CREATE TABLE #rolemember (databasename varchar(400),  dbrole varchar (400))

SELECT @command = 'IF ''?'' NOT IN (''master'', ''model'', ''msdb'', ''tempdb'') 

BEGIN USE ? ;

Select ''?'' as databasename, SUSER_SNAME(owner_sid)

From sys.databases

Where database_id = DB_ID();

end'

INSERT INTO #rolemember 

EXEC sp_MSforeachdb @command

SELECT * FROM  #rolemember 

DROP TABLE #rolemember 

select 'Below shows users who have role membership at the server level that gives them write access to the database:'

Select P1.name As Principal,

P2.name As ServerRole

From sys.server_principals As P1

Inner join sys.server_role_members As RM On RM.member_principal_id = P1.principal_id

Inner join sys.server_principals As P2 On P2.principal_id = RM.role_principal_id

Where P2.name = 'sysadmin';

select 'Below shows users who have permissions granted at the server level that gives them write access to the database:'

Select P.name As Principal,

class_desc As PermissionLevel,

permission_name As PermissionGranted

From sys.server_permissions As SP

Inner join sys.server_principals As P On P.principal_id = SP.grantee_principal_id

Where permission_name In ('control server', 'alter any database')

And state = 'G'; -- Grant