SQL-Server-Scripts - GitLite

KO | EN

gitlite — search

#python #hacktoberfest #react #golang #go #rust #linux #docker #cli #awesome-list #vue #security #ios #open-source

SQL-Server-Scripts

(★ 75)

My SQL Server Scripts

파일 탐색기

최종 버전 다운로드 (.zip)

- Notes - Database Mail.sql
- Notes - ExtendedEventsNotes.sql
- Notes - Hidden properties.sql
- Notes : Use BCP to copy data from one server to another
- Open a project when starting SSMS
- Other Peoples SP I use
- Query - Blocked Queries from System Health.sql
- Query - Database Mail Generate Script.sql
- Query - DB IO and IO Wait.sql
- Query - Default Trace - Permissions.sql
- Query - Default Trace - Schema Changes.sql
- Query - Drive Space.sql
- Query - Find missing Foreign Keys.sql
- Query - Foreign Keys and Constraints.sql
- Query - Identify failed queries XE.sql
- Query - Index Update Stats.sql
- Query - Lead Blocker.sql
- Query - Memory Info.sql
- Query - MissingIndexByQuery.sql
- Query - Performance.sql
- Query - SearchTheLog.sql
- Query - ShredTheDeadlockGraph.sql
- Query - Statistics Info.sql
- Query - Statistics properties.sql
- Query - TableSizes.sql
- Query - Track index progress.sql
- Query - View SysAudit.sql
- Query - Wait Stats.sql
- Query - WhatsGoingOn.sql
- Query - Where is my backup.sql
- Query - WhereIsMyBackup.sql
.gitattributes
CreateUsers.bat
LICENSE
README.md
sp_AzSQLDBPermissions.sql
sp_AzSYNDBPermissions.sql
sp_DBPermissions.sql
sp_SrvPermissions.sql

// repository documentation

Was this content helpful?

★ 0 (0 ratings)

# SQL Server Scripts
**Standard disclaimer: You use scripts off of the web at your own risk.
I fully expect this script to work without issue but I've been known to be wrong before.**

The code under the folder *SSMS Project* is the code that I put in my *SSMS Project* solution in SSMS for my personal work. Feel free to use whatever is there under the MIT license I have listed here. Of course if the code is not mine then all permissions go to the person who wrote it. That said:

**Note** *Not all of the code here is mine. These are the scripts I use on a regular basis and where I've gotten a script from someone else, if possible, I've included the link at the top of the script. If I didn't write it but can't find/remember where I got it from I'll put a note to that effect.*

Instruction Video: https://www.youtube.com/watch?v=dQw4w9WgXcQ

## Detail on the permissions scripts and how they work. (I did write these.)

## sp_SrvPermissions
This stored procedure returns 3 data sets.  The first dataset is the list of server
principals, the second is role membership, and the third is server level permissions.

The final 2 columns of each query are "Un-Do"/"Do" scripts.  For example removing a member
from a role or adding them to a role.  I am fairly confident in the role scripts, however, 
the scripts in the server principals query and server permissions query are works in
progress.  In particular certificates and keys are not scripted out.  Also while the scripts 
have worked flawlessly on the systems I've tested them on, these systems are fairly similar 
when it comes to security so I can't say that in a more complicated system there won't be 
the odd bug.
   
Notes on the create script for server principals:
1. I have included a hashed version of the password and the sid.
This means that when run on another server the password and the sid will remain the same.  
2. In SQL 2005 the create script on the server principals query DOES NOT WORK.  This is 
because the conversion of the sid (in varbinary) to character doesn't appear to work
as I expected in SQL 2005.  It works fine in SQL 2008 and above.  If you want to use
this script in SQL 2005 you can change the CONVERTs in the principal script to
`master.sys.fn_varbintohexstr`
    
```
Parameters:
    @Principal
        If NOT NULL then all three queries only pull for that server principal.  @Principal
        is a pattern check.  The queries check for any row where the passed in value exists.
        It uses the pattern '%' + @Principal + '%'
    @Role
        If NOT NULL then the roles query will pull members of the role.  If it is NOT NULL and
        @Principal is NULL then Server principal and permissions query will pull the principal 
        row for the role and the permissions for the role.  @Role is a pattern check.  The 
        queries check for any row where the passed in value exists.  It uses the pattern 
        '%' + @Role + '%'
    @Type
        If NOT NULL then all three queries will only pull principals of that type.  
        S = SQL login
        U = Windows login
        G = Windows group
        R = Server role
        C = Login mapped to a certificate
        K = Login mapped to an asymmetric key
    @DBName
        If NOT NULL then only return those principals and information about them where the 
        principal exists within the DB specified.
	@UseLikeSearch
		When this is set to 1 (the default) then the search parameters will use LIKE (and 
		%'s will be added around the @Principal and @Role parameters).  
        When set to 0 searchs will use =.
	@IncludeMSShipped
		When this is set to 1 (the default) then all principals will be included.  When set 
		to 0 the fixed server roles and SA and Public principals will be excluded.
	@DropTempTables
		When this is set to 1 (the default) the temp tables used are dropped.  If it's 0
		then the tempt ables are kept for references after the code has finished.
		The temp tables are:
			##SrvPrincipals
			##SrvRoles 
			##SrvPermissions
	@Output
		What type of output is desired.
		Default - Either 'Default' or it doesn't match any of the allowed values then the SP
					will return the standard 3 outputs.
		None - No output at all.  Usually used if you keeping the temp tables to do your own
					reporting.
		CreateOnly - Only return the create scripts where they aren't NULL.
		DropOnly - Only return the drop scripts where they aren't NULL.
		ScriptsOnly - Return drop and create scripts where they aren't NULL.
		Report - Returns one output with one row per principal and a comma delimited list of
					roles the principal is a member of and a comma delimited list of the 
					individual permissions they have.
    @Print
        Defaults to 0, but if a 1 is passed in then the queries are not run but printed
        out instead.  This is primarily for debugging.
        
Data is ordered as follows
    1st result set: SrvPrincipal
    2nd result set: RoleName, LoginName if the parameter @Role is used else
                    LoginName, RoleName
    3rd result set: GranteeName 
```

## sp_DBPermissions
This stored procedure returns 3 data sets. The first dataset is the list of database
principals, the second is role membership, and the third is object and database level
permissions.
    
The final 2 columns of each query are "Un-Do"/"Do" scripts.  For example removing a member
from a role or adding them to a role.  I am fairly confident in the role scripts, however, 
the scripts in the database principals query and database/object permissions query are 
works in progress.  In particular certificates, keys and column level permissions are not
scripted out.  Also while the scripts have worked flawlessly on the systems I've tested 
them on, these systems are fairly similar when it comes to security so I can't say that 
in a more complicated system there won't be the odd bug.

```
Parameters:
    @DBName
        If NULL use the current database, otherwise give permissions based on the parameter.
    
        There is a special case where you pass in ALL to the @DBName.  In this case the SP
        will loop through (yes I'm using a cursor) all of the DBs in sysdatabases and run
        the queries into temp tables before returning the results.  WARNINGS: If you use
        this option and have a large number of databases it will be SLOW.  If you use this
        option and don't specify any other parameters (say a specific @Principal) and have
        even a medium number of databases it will be SLOW.  Also the undo/do scripts do 
        not have USE statements in them so please take that into account.
    @Principal
        If NOT NULL then all three queries only pull for that database principal.  @Principal
        is a pattern check.  The queries check for any row where the passed in value exists.
        It uses the pattern '%' + @Principal + '%'
    @Role
        If NOT NULL then the roles query will pull members of the role.  If it is NOT NULL and
        @DBName is NULL then DB principal and permissions query will pull the principal row for
        the role and the permissions for the role.  @Role is a pattern check.  The queries 
        check for any row where the passed in value exists.  It uses the pattern '%' + @Role +
        '%'
    @Type
        If NOT NULL then all three queries will only pull principals of that type.  
        S = SQL login
        U = Windows login
        G = Windows group
        R = Server role
        C = Login mapped to a certificate
        K = Login mapped to an asymmetric key
    @ObjectName
        If NOT NULL then the third query will display permissions specific to the object 
        specified and the first two queries will display only those users with those specific
        permissions.  Unfortunately at this point only objects in sys.all_objects will work.
        This parameter uses the pattern '%' + @ObjectName + '%'
    @Permission
        If NOT NULL then the third query will display only permissions that match what is in
        the parameter.  The first two queries will display only those users with that specific
        permission.
    @LoginName
        If NOT NULL then each of the queries will only pull back database principals that
        have the same SID as a login that matches the pattern '%' + @LoginName + '%'
	@UseLikeSearch
		When this is set to 1 (the default) then the search parameters will use LIKE (and 
		%'s will be added around the @Principal, @Role, @ObjectName, and @LoginName parameters).  
        When set to 0 searchs will use =.
	@IncludeMSShipped
		When this is set to 1 (the default) then all principals will be included.  When set 
		to 0 the fixed server roles and SA and Public principals will be excluded.
	@DropTempTables
		When this is set to 1 (the default) the temp tables used are dropped.  If it's 0
		then the tempt ables are kept for references after the code has finished.
		The temp tables are:
			##DBPrincipals
			##DBRoles 
			##DBPermissions
	@Output
		What type of output is desired.
		Default - Either 'Default' or it doesn't match any of the allowed values then the SP
					will return the standard 3 outputs.
		None - No output at all.  Usually used if you keeping the temp tables to do your own
					reporting.
		CreateOnly - Only return the create scripts where they aren't NULL.
		DropOnly - Only return the drop scripts where they aren't NULL.
		ScriptsOnly - Return drop and create scripts where they aren't NULL.
		Report - Returns one output with one row per principal and a comma delimited list of
					roles the principal is a member of and a comma delimited list of the 
					individual permissions they have.
    @Print
        Defaults to 0, but if a 1 is passed in then the queries are not run but printed
        out instead.  This is primarily for debugging.
    
Data is ordered as follows
    1st result set: DBPrincipal
    2nd result set: RoleName, UserName if the parameter @Role is used else
                    UserName, RoleName
    3rd result set: ObjectName then Grantee_Name if the parameter @ObjectName
                    is used otherwise Grantee_Name, ObjectName
```