Category Archives: Database Administrator

How to check SQL Server schema user permissions

December 2, 2021Database Administrator, dba, sql, SQL Server, T-SQLguide, how check schema permissions, how check sql server schema permissions, How To, How to check SQL Server schema user permissions, howto, lesson, schema permissions, tutorialTidbytez

The script below will, providing the login you are using has adequate permissions, return the schema permissions a user has.

Commented out at the end of the script are examples of the types of permission you can assign, again providing the login you are using has adequate permissions.

SELECT ClassDescription
	,StateDescription
	,PermissionName
	,SchemaName
	,UserName
FROM (
	SELECT class_desc AS ClassDescription
		,state_desc AS StateDescription
		,permission_name AS PermissionName
		,SCHEMA_NAME(major_id) AS SchemaName
		,USER_NAME(grantee_principal_id) AS UserName
	FROM sys.database_permissions AS PERM
	JOIN sys.database_principals AS Prin ON PERM.major_ID = Prin.principal_id
		AND class_desc = 'SCHEMA'
	) AS schemaPermissions
WHERE 1=1 
/*Uncomment below to check permissions on a specific schema and/or specific user*/
--	AND SchemaName = 'dbo'
--	AND UserName = 'SomeGuy'
ORDER BY UserName ASC
,SchemaName ASC
GO



/*
--Grant schema permission examples
GRANT SELECT ON SCHEMA::dbo TO SomeGuy;
GRANT UPDATE ON SCHEMA::dbo TO SomeGuy;
GRANT ALTER ON SCHEMA::dbo TO SomeGuy;
GRANT DELETE ON SCHEMA::dbo TO SomeGuy;
*/

How to set up Logins and Users in Azure SQL Server using T-SQL

November 2, 2021Azure, Database Administrator, sql, SQL Server, T-SQLcreate azure sql login, create azure sql user, guide, How to set up Logins and Users in Azure SQL Server using T-SQL, howto, lesson, set up azure sql server login, set up azure sql server user, tutorialTidbytez

Below is a script you can modify to create a Login for an Azure SQL Server instance along with Users from that Login within databases.

To use the script don’t execute it all at once.

Execute the script in stages:

Section 1 & 2 can be executed together.

Section 3 requires you to switch to the target database.

Section 4 relates to limiting data access to a particular role or schema.

/*
Find & replace
the following with a 
login name and user name
*/
/*
ServerLogin
DbUser
*/

/*
1: Create SQL Login on master database 
(connect with admin account to master database)
*/
CREATE LOGIN ServerLogin
	WITH PASSWORD = '<strong_password>';

/*2: Create SQL User on the master database 
(this is necessary for login attempt to the <default> database,
 as with Azure SQL you cannot set the DEFAULT_DATABASE property 
 of the login so it always will be [master] database.)
 */
CREATE USER DbUser
FROM LOGIN ServerLogin;

/***************************/
/*SWITCH TO TARGET DATABASE*/
/***************************/

/*
3: Create SQL User on the user database 
(connect with admin account to user database)
*/
CREATE USER DbUser
FROM LOGIN ServerLogin;

/*************************/
/*SECURITY CONSIDERATIONS*/
/*************************/

/*Set up role example*/
/*4. Grant permissions to the user by assigning them to a database role*/
ALTER ROLE db_datareader ADD MEMBER DbUser;

/*Limit access to schema*/
/*Or instead of step four you can use the below:*/
GRANT SELECT
	ON SCHEMA::schemaName
	TO DbUser;

How to update/replace or remove an email address from all SSRS subscriptions with T-SQL

October 2, 2021Database Administrator, dba, SQL Server, SSRS, SSRS Reporting, T-SQL, tsqlchange ssrs subscription with sql, guide, How To, how to remove ssrs email address, how to update ssrs email address, how to update ssrs subscription, howto, lesson, ssrs subscriptions sql, T-SQL, tsql, tutorialTidbytez

Updating/replacing or removing an email address from SSRS subscriptions manually is far too time consuming and tedious. Use the below script instead to either update/replace an email address with a new one across all subscriptions or remove the email address from all subscriptions.

To exclude subscriptions, i.e. keep the email address active for a particular subscription, find the subscription Id for that subscription and include it in the WHERE clause. Remember to uncomment that line in order for the clause to be active.

If you’re worried about messing anything up then back up the table before running the script!

Create backup:

SELECT *
INTO [dbo].[Subscriptions_bk]
FROM [dbo].[Subscriptions]

Update/replace or remove an email address:

/*
To replace an email address with another email address:
Find and replace the following email addresses (Ctrl+H) with the new email address*/
/*
Email address to update/replace:
replaceSomeGuy@someCompany.com

Email address replacement:
newGuy@someCompany.com
*/
UPDATE [dbo].[Subscriptions]
SET ExtensionSettings = REPLACE(CONVERT(VARCHAR(MAX), ExtensionSettings), 'replaceSomeGuy@someCompany.com', 'newGuy@someCompany.com')
WHERE CHARINDEX('replaceSomeGuy@someCompany.com', ExtensionSettings) <> 0
--AND SubscriptionID NOT IN ('FindTheSubscriptionId')
;

/*
To remove an email address:
NOTE:Run both of the following scripts as an email address may or may not end with ";"

Find and replace the following email address (Ctrl+H)
*/
/*
Email address to remove
removeSomeGuy@otherCompany.com
*/
UPDATE [dbo].[Subscriptions]
SET ExtensionSettings = REPLACE(CONVERT(VARCHAR(MAX), ExtensionSettings), 'removeSomeGuy@otherCompany.com;', '')
WHERE CHARINDEX('removeSomeGuy@otherCompany.com;', ExtensionSettings) <> 0
--AND SubscriptionID NOT IN ('FindTheSubscriptionId')
;


UPDATE [dbo].[Subscriptions]
SET ExtensionSettings = REPLACE(CONVERT(VARCHAR(MAX), ExtensionSettings), 'removeSomeGuy@otherCompany.com', '')
WHERE CHARINDEX('removeSomeGuy@otherCompany.com', ExtensionSettings) <> 0
--AND SubscriptionID NOT IN ('FindTheSubscriptionId')
;

How to write T-SQL Geography data to a table

July 2, 2021Database Administrator, dba, How To, IT, sql, SQL Server, T-SQL, tsqlgeography data type, guide, how to convert binary to string geo data, how to geocode data, how to read geo, how to read geo data type tsql, how to write geo code data to table tsql, how to write geo data to table sql, how to write geography data type, How to write T-SQL Geography data to a table, howto, lesson, sql server geography, tsql geo, tutorialTidbytez

Below is some example code for writing the SQL Server geography data type to a table. Note by default geography data is stored in a binary format but it can be converted to a string to make it human readable.

Note: Pass in Longitude and Latitude values in that order.

/*Demo of geo data*/
DECLARE @g GEOGRAPHY;

SET @g = GEOGRAPHY::STPointFromText('POINT(53.578741 -6.611670)', 4326);

/*Geography data is in binary format*/
SELECT @g AS 'GeoBinaryFormat';

/*Convert binary data to a string*/
SELECT @g.ToString() AS 'ConvertingDataToString';


/*Inserting geo data into Table*/
CREATE TABLE #GeoTest ([CoordinateLocation] [geography] NULL);

INSERT INTO #GeoTest (CoordinateLocation)
SELECT GEOGRAPHY::STPointFromText('POINT(53.578741 -6.611670)', 4326);

SELECT *
FROM #GeoTest;

DROP TABLE #GeoTest;

How to format SQL Server datetime as dd/mm/yyyy hh:mm:ss

February 2, 2021Coding, Data, Database Administrator, Development, How To, sql, SQL Server, T-SQLdatetime, dd/mm/yyyy, dd/mm/yyyy hh:mm, dd/mm/yyyy hh:mm:ss, formatting, guide, How To, howto, lesson, SQL Server, tutorialTidbytez

If you are exporting the results of a SQL Server query to excel typically the recipient of the file wants the dates referenced in the format “dd/mm/yyyy hh:mm:ss” or “dd/mm/yyyy” not in the usual database format yyyy-mm-dd.

The below query formats the datetime as desired. Note that the letter m representing month is capitalised. If they are not the engine will interpret the lowercase letter m as minute so you will end up with days, minutes, years.

Also not that the letter h representing the hours is also capitalised. Capitalising the h makes the time output with the 24 hour format. Lowercase h will be 12 hour format. It is highly recommended not to use the lowercase h.

SELECT FORMAT(GETDATE(), 'dd/MM/yyyy HH:mm:ss', 'en-us')

If you only want the date and not time just remove the relevant text, i.e. just date dd/MM/yyyy or datetime without second dd/MM/yyyy HH:mm.

How to list login details, such as name and role membership, for all logins in a SQL Server Instance

January 2, 2020Database Administrator, dba, sql, SQL Server, T-SQLall logins, created date, details, disabled, enabled, guide, How To, howto, instance, lesson, list, logins, modified date, name, role membership, tutorialTidbytez 1

The script below will provide SQL Server Instance login details such as:

The login name
The role membership the login is part of such as sysadmin etc.,
Whether or not the login is enabled
Whether or not the login is a SQL Server Login or Windows Login etc.
Login created date
Login modified date
Etc.

SELECT SP.principal_id
	,SP.name
	,SUSER_NAME(role_principal_id) server_role
	,SP.is_disabled
	,SP.type_desc
	,SP.sid
	,SP.type
	,SP.create_date
	,SP.modify_date
	,SP.default_database_name
	,SP.default_language_name
	,SP.credential_id
	,SP.owning_principal_id
	,SP.is_fixed_role
FROM sys.server_principals AS SP
LEFT JOIN sys.server_role_members AS RM ON SP.principal_id = RM.member_principal_id;

How to set every database in a SQL Server instance to read only

December 2, 2019Database Administrator, dba, T-SQLdatabases, guide, how set all databases to read only, How To, howto, lesson, read only, tutorialTidbytez 1

The following TSQL when executed will create the TSQL needed to set all databases in a SQL Server instance to read only mode.

Typical use cases for this might include creating a copy of databases for reporting purposes or when migrating databases from one server to another.

SELECT 'ALTER DATABASE [' + NAME + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE [' + NAME + '] SET READ_ONLY WITH NO_WAIT
ALTER DATABASE [' + NAME + '] SET MULTI_USER
GO'
FROM sys.databases
/*Setting database id > 4 excludes the system databases*/
WHERE database_id > 4

Before you run the outputted script you may need to kill open connections to the databases or certain running activities. Obviously you would want to make sure it is safe to do so before killing any activity.

To kill activities on mass you can use the script in this post.

How to create a job that will test whether SQL Server database mail is working

November 2, 2019Coding, Database Administrator, dba, Development, IT, SQL Server, System, T-SQL, tsqldatabase mail, guide, How To, howto, Job, lesson, sql agent, SQL Server, SQL Server test email, SQL Server test email is working, SQL Server test mail, sql sever agent, test, tutorial, workingTidbytez

The following script will create a job that will run every minute to test if database mail can be sent from a job scheduled to run by the Sql Server Agent.

Simply find and replace the email address below with the email address you want to target:

testoperator@mail.com

Then run the script.

The operator ‘Test Operator’ and job ‘MailTest’ will be created.

The job is disabled by default, enable it to begin testing.

When you are finished run the commented out section at the bottom of the script to remove the test operator and job.

If you have just setup database mail for the first time the SQL Server Agent will need to be restarted.

/*
FIND AND REPLACE

testoperator@mail.com

*/
USE msdb;
GO

EXEC dbo.sp_add_operator @name = N'Test Operator'
	,@enabled = 1
	,@email_address = N'testoperator@mail.com'
GO

USE [msdb]
GO

BEGIN TRANSACTION

DECLARE @ReturnCode INT

SELECT @ReturnCode = 0

/****** Object:  JobCategory [[Uncategorized (Local)]]    Script Date: 31/07/2019 11:35:43 ******/
IF NOT EXISTS (
		SELECT NAME
		FROM msdb.dbo.syscategories
		WHERE NAME = N'[Uncategorized (Local)]'
			AND category_class = 1
		)
BEGIN
	EXEC @ReturnCode = msdb.dbo.sp_add_category @class = N'JOB'
		,@type = N'LOCAL'
		,@name = N'[Uncategorized (Local)]'

	IF (
			@@ERROR <> 0
			OR @ReturnCode <> 0
			)
		GOTO QuitWithRollback
END

DECLARE @jobId BINARY (16)

EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name = N'MailTest'
	,@enabled = 0
	,@notify_level_eventlog = 0
	,@notify_level_email = 3
	,@notify_level_netsend = 0
	,@notify_level_page = 0
	,@delete_level = 0
	,@description = N'No description available.'
	,@category_name = N'[Uncategorized (Local)]'
	,@owner_login_name = N'sa'
	,@notify_email_operator_name = N'Test Operator'
	,@job_id = @jobId OUTPUT

IF (
		@@ERROR <> 0
		OR @ReturnCode <> 0
		)
	GOTO QuitWithRollback

/****** Object:  Step [Step 1]    Script Date: 31/07/2019 11:35:44 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @jobId
	,@step_name = N'Step 1'
	,@step_id = 1
	,@cmdexec_success_code = 0
	,@on_success_action = 1
	,@on_success_step_id = 0
	,@on_fail_action = 2
	,@on_fail_step_id = 0
	,@retry_attempts = 0
	,@retry_interval = 0
	,@os_run_priority = 0
	,@subsystem = N'TSQL'
	,@command = N'SELECT 1'
	,@database_name = N'master'
	,@flags = 0

IF (
		@@ERROR <> 0
		OR @ReturnCode <> 0
		)
	GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId
	,@start_step_id = 1

IF (
		@@ERROR <> 0
		OR @ReturnCode <> 0
		)
	GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @jobId
	,@name = N'Job Schedule'
	,@enabled = 1
	,@freq_type = 4
	,@freq_interval = 1
	,@freq_subday_type = 4
	,@freq_subday_interval = 1
	,@freq_relative_interval = 0
	,@freq_recurrence_factor = 0
	,@active_start_date = 20190731
	,@active_end_date = 99991231
	,@active_start_time = 0
	,@active_end_time = 235959
	,@schedule_uid = N'f0741db6-488e-44da-8f5e-a3f0ed13835e'

IF (
		@@ERROR <> 0
		OR @ReturnCode <> 0
		)
	GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId
	,@server_name = N'(local)'

IF (
		@@ERROR <> 0
		OR @ReturnCode <> 0
		)
	GOTO QuitWithRollback

COMMIT TRANSACTION

GOTO EndSave

QuitWithRollback:

IF (@@TRANCOUNT > 0)
	ROLLBACK TRANSACTION

EndSave:
GO

/*
REMOVE OPERATOR AND JOB
*/
/*
USE msdb;
GO

EXEC sp_delete_operator @name = 'Test Operator';

EXEC sp_delete_job @job_name = N'MailTest';

GO
*/

How to fix the SQL Server Job Error “Failed to decrypt an encrypted XML node because the password was not specified or not correct” encountered with SSIS packages

October 2, 2019Database Administrator, dba, SQL Server, SSISEnter decryption password: Missing argument for option decrypt, Failed to decrypt an encrypted XML node because the password was not specified or not correct, failing job, guide, How To, howto, lesson, tutorialTidbytez 1

If you have a password protected SSIS package you may encounter this error.

First off this seems like a very buggy problem and the solution may not work for everyone. Also, as typical, you may find that you are trying to solve more than one problem at once, for instances going from one domain to another seems to confuse matters. So if this solution doesn’t work it may be just part of the solution to your particular puzzle.

If you go to the job step properties you actually see that there are two spaces between /DECRYPT and /CHECKPOINTING.

CommandLine

If you reduce the space to one and run the job again you might get this error:

“Enter decryption password: Missing argument for option “decrypt””

This is because you’ve removed the password from the job associated with the package.

I’ve found that when you edit the command manually by first removing the spaces and then retyping the command with the package password this solves the error.

EditCommand

For example:

/DECRYPT YourPackagePassword /CHECKPOINTING

If this doesn’t work try scripting out the job, deleting it, and then running the script again.

Good Luck.

How to get SQL Server Network Information using SSMS

September 2, 2019Database Administrator, dba, IT, sql, SQL Server, System architecture, T-SQL, tsqlGET, Get network info using sql, guide, How get SQL Server Network info, How To, how to get network info using sql, howto, ip, ip address, lesson, SQL network info, sql sever, tutorialTidbytez

The following code will work for a remote client request to SQL 2008 and newer.

Note: The local machine address (local_net_address) is that of the SQL Server while client_net_address is the address of the remote computer you have used to make the request.

SELECT @@SERVERNAME AS ServerName
	,CONNECTIONPROPERTY('net_transport') AS net_transport
	,CONNECTIONPROPERTY('protocol_type') AS protocol_type
	,CONNECTIONPROPERTY('auth_scheme') AS auth_scheme
	,CONNECTIONPROPERTY('local_net_address') AS local_net_address
	,CONNECTIONPROPERTY('local_tcp_port') AS local_tcp_port
	,CONNECTIONPROPERTY('client_net_address') AS client_net_address

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this: