Skip to content

Commit 593ef51

Browse files
AlexTitenkoAlexTitenko
committed
Add scripts and procedure
1. Add 2 sql script - Finding_Blocking_Queries.sql, SQL_Server_Fixed_Role_Permissions.sql 2. Add 1 powershell script - Add-UserToRole.ps1 3. Add 1 stored procedure - sp_sizing.sql
1 parent ebd5c88 commit 593ef51

4 files changed

Lines changed: 385 additions & 0 deletions

File tree

‎PowerShell/Add-UserToRole.ps1‎

Lines changed: 93 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,93 @@
1+
#Requires -module sqlserver
2+
#Requires -module dbatools
3+
4+
#Link: https://sqldbawithabeard.com/2017/03/06/quickly-creating-test-users-in-sql-server-with-powershell-using-the-sqlserver-module-and-dbatools
5+
6+
### Define some variables
7+
$server = ''
8+
$Password = "Password"
9+
$Database = 'TheBeardsDatabase'
10+
$Admins = Get-Content 'C:\temp\Admins.txt'
11+
$Users = Get-Content 'C:\temp\Users.txt'
12+
$LoginType = 'SQLLogin'
13+
$userrole = 'Users'
14+
$adminrole = 'Admin'
15+
16+
# Create a SQL Server SMO Object
17+
$srv = Connect-DbaSqlServer -SqlServer $server
18+
$db = $srv.Databases[$Database]
19+
20+
function Add-UserToRole
21+
{
22+
param
23+
(
24+
[Parameter(Mandatory=$true,
25+
ValueFromPipeline=$true,
26+
ValueFromPipelineByPropertyName=$true,
27+
ValueFromRemainingArguments=$false)]
28+
[ValidateNotNullOrEmpty()]
29+
[string]$Password,
30+
[Parameter(Mandatory=$true,
31+
ValueFromPipeline=$true,
32+
ValueFromPipelineByPropertyName=$true,
33+
ValueFromRemainingArguments=$false)]
34+
[ValidateNotNullOrEmpty()]
35+
[string]$User,
36+
[Parameter(Mandatory=$true,
37+
ValueFromPipeline=$true,
38+
ValueFromPipelineByPropertyName=$true,
39+
ValueFromRemainingArguments=$false)]
40+
[ValidateNotNullOrEmpty()]
41+
[string]$Server,
42+
[Parameter(Mandatory=$true,
43+
ValueFromPipeline=$true,
44+
ValueFromPipelineByPropertyName=$true,
45+
ValueFromRemainingArguments=$false)]
46+
[ValidateNotNullOrEmpty()]
47+
[string]$Role,
48+
[Parameter(Mandatory=$true,
49+
ValueFromPipeline=$true,
50+
ValueFromPipelineByPropertyName=$true,
51+
ValueFromRemainingArguments=$false)]
52+
[ValidateSet("SQLLogin", "WindowsGroup", "WindowsUser")]
53+
[string]$LoginType
54+
)
55+
56+
if(!($srv.Logins.Contains($User)))
57+
{
58+
if($LoginType -eq 'SQLLogin')
59+
{
60+
$Pass = ConvertTo-SecureString -String $Password -AsPlainText -Force
61+
$Credential = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $User, $Pass
62+
Add-SqlLogin -ServerInstance $Server -LoginName $User -LoginType $LoginType -DefaultDatabase tempdb -Enable -GrantConnectSql -LoginPSCredential $Credential
63+
}
64+
elseif($LoginType -eq 'WindowsGroup' -or $LoginType -eq 'WindowsUser')
65+
{
66+
Add-SqlLogin -ServerInstance $Server -LoginName $User -LoginType $LoginType -DefaultDatabase tempdb -Enable -GrantConnectSql
67+
}
68+
}
69+
if (!($db.Users.Contains($User)))
70+
{
71+
72+
# Add user to database
73+
74+
$usr = New-Object ('Microsoft.SqlServer.Management.Smo.User') ($db, $User)
75+
$usr.Login = $User
76+
$usr.Create()
77+
78+
}
79+
#Add User to the Role
80+
$db.roles[$role].AddMember($User)
81+
}
82+
83+
foreach($User in $Users)
84+
{
85+
Add-UserToRole -Password $Password -User $user -Server $server -Role $Userrole -LoginType SQLLogin
86+
}
87+
88+
foreach($User in $Admins)
89+
{
90+
Add-UserToRole -Password $Password -User $user -Server $server -Role $adminrole -LoginType SQLLogin
91+
}
92+
93+
Get-DbaRoleMember -SqlInstance $server |ogv

‎Scripts/Finding_Blocking_Queries.sql‎

Lines changed: 54 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,54 @@
1+
--Finding Blocking Queries
2+
--Author: Steve Stedman
3+
--One of the common problems with when diagnosing what appears to be a slow scenario where there may be blocking involved is determining what is blocking on SQL Server
4+
--This script works on SQL Server 2008, 2008R2, 2012, 2014, and 2016
5+
--If there is no blocking occurring then this query will return nothing
6+
7+
SELECT *
8+
INTO #runningQueries
9+
FROM master..sysprocesses WITH (NOLOCK);
10+
11+
12+
;WITH BlockingCTE as
13+
(
14+
SELECT q1.blocked as spid
15+
FROM #runningQueries q1
16+
WHERE q1.blocked != 0
17+
AND q1.blocked not in (SELECT spid FROM #runningQueries q2 WHERE q2.blocked != 0)
18+
)
19+
, recursiveBlocking AS
20+
(
21+
SELECT b.spid, cast(0 as SMALLINT) as blocked,
22+
cast(b.spid as varchar(1000)) as treePath, 0 as level,
23+
sp1.sql_handle, b.spid as topBlock
24+
FROM BlockingCTE b
25+
INNER JOIN #runningQueries sp1 on sp1.spid = b.spid
26+
27+
UNION ALL
28+
29+
SELECT sp.spid, rb.spid as blocked,
30+
cast(rb.treePath + '->' + cast(sp.spid as varchar(1000)) as VARCHAR(1000)) as treePath,
31+
level + 1 as level, sp.sql_handle, topBlock
32+
FROM #runningQueries sp
33+
INNER JOIN recursiveBlocking as rb ON rb.spid = sp.blocked
34+
)
35+
, topBlockCount AS
36+
(
37+
SELECT *, count(1) over(partition by topBlock) as NumBlocked
38+
FROM recursiveBlocking
39+
)
40+
SELECT DISTINCT
41+
tb.SPID,
42+
tb.blocked,
43+
tb.treePath as blockingChain,
44+
tb.level,
45+
tb.topBlock,
46+
tb.NumBlocked,
47+
LTRIM(REPLACE(REPLACE(st.text, char(10), ' '), char(13), ' ')) as theQuery
48+
FROM topBlockCount tb
49+
CROSS APPLY sys.dm_exec_sql_text(tb.sql_handle) AS st
50+
ORDER BY NumBlocked DESC, treePath
51+
OPTION (RECOMPILE);
52+
53+
54+
DROP TABLE #runningQueries;

‎Scripts/SQL_Server_Fixed_Role_Permissions.sql‎

Lines changed: 24 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,24 @@
1+
--SQL Server Fixed Role Permissions
2+
--Author: Jason Brimhall
3+
--In this script, I have taken the results from each of the stored procedures and dumped them into a temp table.
4+
--Using this temp table, I can now join to this table to get a more complete list of the permissions in effect for various principals.
5+
6+
CREATE TABLE #role_permission
7+
(
8+
DBFixedRole VARCHAR(128)
9+
, Permission VARCHAR(128)
10+
)
11+
INSERT INTO #role_permission
12+
( DBFixedRole, Permission )
13+
EXECUTE sp_dbfixedrolepermission
14+
GO
15+
16+
INSERT INTO #role_permission
17+
( DBFixedRole, Permission )
18+
EXECUTE sp_srvrolepermission
19+
GO
20+
21+
SELECT *
22+
FROM #role_permission r;
23+
24+
DROP TABLE #role_permission;

0 commit comments

Comments
 (0)