DBA Diaries

Thoughts and experiences of a DBA working with SQL Server and MySQL

Revoking Access for All Users of a Database in SQL Server

Posted on May 9, 2016 Written by Andy Hayes Leave a Comment

I love Management Studio for SQL Server but sometimes it just doesn’t cut the mustard. I had a scenario recently where I needed to revoke access for all users from a database. I could delete them all but that would be more effort than necessary. I just wanted to deny access to a specific database.

To my knowledge, there is not a quick way to do this and so I had to write some T-SQL to do it.

You can revoke a database user using syntax REVOKE CONNECT, for example

REVOKE CONNECT FROM my_user;

What if you have many SQL Server users and you want to stop all of them accessing the database?

Identify them using this query from sysusers. In my case I am using an additional filter to pull out users with a specific prefix.

SELECT name FROM sysusers WHERE hasdbaccess = 1 AND name LIKE 'user{96f5cbaad583fb885ff5d18ecce5734bf1e8596949521e20c290401929518f75}';

In my case, this returns:

name
-----------
user_1
user_2
user_3

(3 row(s) affected)

To do this, I made a small modification to this query using some concatenation and square brackets in case of weird user names which contain hyphens:

SELECT 'REVOKE CONNECT FROM [' + name + '];' FROM sysusers WHERE hasdbaccess = 1 AND name LIKE 'user{96f5cbaad583fb885ff5d18ecce5734bf1e8596949521e20c290401929518f75}';

This returned and I know have a series of statements that I can run as one batch:

-------------------------------
REVOKE CONNECT FROM [user_1];
REVOKE CONNECT FROM [user_2];
REVOKE CONNECT FROM [user_3];

(3 row(s) affected)

Copy and paste these into a new query and execute them. Test that access has been revoked either by looking in Management Studio or querying sysusers for hasdbaccess = 1 again.

SELECT COUNT(*) FROM sysusers WHERE hasdbaccess = 1 AND name LIKE 'user{96f5cbaad583fb885ff5d18ecce5734bf1e8596949521e20c290401929518f75}';

If you’re looking in Management Studio at the users, then a red arrow indicates that the user has been revoked.

evoke access for all users in a sql server database

Filed Under: All Articles, SQL Server Administration Tagged With: sql server

Implementing a Simple SQL Server Backup Script

Posted on October 21, 2013 Written by Andy Hayes 1 Comment

sql server backup scriptI’ve used this simple SQL Server backup script a number of times to backup sql server to a file or network drive. It’s ideal if you are running SQL Server Express for example which does not come equipped with maintenance plans.  This script can be used in conjunction with SQLCMD to backup SQL Server from the command line.

So let’s take a look at this.

What we want to achieve here is a way of executing the same backup database command for each database in SQL Server. The “bare bones” T-SQL used might look like this. In this example, I am using my local drive as the backup destination.

BACKUP DATABASE dbname TO DISK =
'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\BACKUP\dbname.bak';

I say bare bones because the above command will backup a database to disk for you but there are a wide selection of different options which can be used to customize the backup. For more on that visit this link

We can query SQL Server for a list of the databases installed on the instance. You can filter out the databases which you are not interested in backing up.

SELECT Name FROM sys.databases WHERE Name NOT IN ('Model','TempDb');

So I get back this list..

Name
------------------------------
AdventureWorks2012
master
msdb
ReportServer$SQL2012
ReportServer$SQL2012TempDB
Test

I now want to write a simple loop to run through each of these names and execute the BACKUP DATABASE command producing a file on disk. For this, you could use a CURSOR or a WHILE LOOP. I’ll show you both ways.

Which one you choose is a matter of preference. Note that by default if you backup to the same file on disk, each backup is appended to the backup file. If you want to overwrite your backup files, use WITH INIT. Refer to the link above for more information on the syntax.

Backup SQL Server databases using a cursor

DECLARE @dbName VARCHAR(50)
DECLARE @backupPath VARCHAR(256)
DECLARE @backupFileName VARCHAR(256)

SET @backupPath =
'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\Backup\'

DECLARE backup_cursor CURSOR FOR
SELECT Name FROM sys.databases WHERE Name NOT IN ('Model','TempDB');
OPEN backup_cursor
FETCH NEXT FROM backup_cursor INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
SET @backupFileName = @backupPath + @dbName
BACKUP DATABASE @dbName TO DISK = @backupFileName
FETCH NEXT FROM backup_cursor INTO @dbname
END
CLOSE backup_cursor
DEALLOCATE backup_cursor

Backup SQL Server databases using a while loop

DECLARE @dbName VARCHAR(50)
DECLARE @backupPath VARCHAR(256)
DECLARE @backupFileName VARCHAR(256)
DECLARE @id INT

SET @backupPath =
'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\Backup\'

SET @id = (SELECT MIN(database_id) FROM sys.databases WHERE Name NOT IN ('Model','TempDB'))
WHILE @id IS NOT NULL
BEGIN
SET @dbName = (SELECT Name FROM sys.databases WHERE database_id = @id)
SET @backupFileName = @backupPath + @dbName
BACKUP DATABASE @dbName TO DISK = @backupFileName
SET @id = (SELECT MIN(database_id) FROM sys.databases WHERE Name NOT IN ('Model','TempDB') AND database_id > @id)
END

(Apologies if this code looks a little messy. I am looking for a better code formatting plugin – any suggestions? 🙂 )

Once you have customized the script to your needs, wrap it inside of a stored procedure. Depending on what version of SQL Server you running, you can either schedule this using SQL Server Agent or via the windows task scheduler by calling SQLCMD.

Backing up SQL Server using SQLCMD at the command line

Start a command prompt and type SQLCMD /? to get the help options up.

Connect to your SQL Server instance using the options available and then run the backup to test that it works ok.

backup sql server command line

You can set up a windows batch file to call SQLCMD. I could add the following line to a batch file and I am now ready to schedule this using windows task scheduler.

SQLCMD -E -S MACHINE\INSTANCE -Q "exec master.dbo.usp_backupDatabases"

Next Steps

It’s important that you get the right plan in place for your server. What I have provided is just a basic script, call it a template if you will. You can customize it to include differential backups, transaction log backups, different file locations, multiple backup sets, multiple files etc.

Have a read of the BACKUP DATABASE documentation to get a idea for what is possible. Communicate with the business to agree requirements, write a backup plan and test whatever code you write thoroughly to ensure that it is meeting the backup plan requirements.

Finally

Backing up using scripts is very handy for editions of SQL Server which do not come equipped with maintenance plans and SQL Server Agent. If you are running one of those editions that does have those features, then you might decide to choose those tools instead. Using backup scripts however still remain highly viable and are preferable to some DBA’s.

Filed Under: All Articles, SQL Server Administration Tagged With: backup and restore, sql server

Using DBCC SQLPERF

Posted on October 19, 2013 Written by Andy Hayes 3 Comments

dbcc sqlperfThere are a number of uses for the DBCC SQLPERF command. I touched upon one of them in my last post on how you can use the command to obtain information about transaction log growth.

This handy little DBCC command has been around for a while now. So lets look at the others ways you use it….

  1. Viewing wait statistics data
  2. Clearing wait statistics data
  3. Clearing latch wait statistics data
  4. Obtaining log space size and used data

Syntax and examples for DBCC SQLPERF


--syntax
DBCC SQLPERF(option)
WITH NO_INFOMSGS


--example uses

DBCC SQLPERF(waitstats)

DBCC SQLPERF('sys.dm_os_latch_stats' , CLEAR)

DBCC SQLPERF('sys.dm_os_wait_stats' , CLEAR)

DBCC SQLPERF(logspace)


Looking at each use case for DBCC SQLPERF in turn….

DBCC SQLPERF(waitstats)

There are newer ways to do this by reading from sys.dm_os_wait_stats but it can be used to obtain wait statistics from your SQL Server.

I wrote about waits stats and wait types in an earlier post so have a read of that if you want to know more about them.

DBCC SQLPERF(‘sys.dm_os_wait_stats’ , CLEAR)

This resets the wait statistics data that your SQL Server has collected since it was last restarted – very handy for performance tuning.

DBCC SQLPERF(‘sys.dm_os_latch_stats’ , CLEAR)

Latches are internal resource locks and wait data is recorded in a similar way to wait type data. To clear out the latch waits that your SQL Server has collected since it was last restarted then run this command.

DBCC SQLPERF(logspace)

Finally, in order to get log space data on how large your transaction logs are and what percentage of them is in use then execute this command. My last post was about this dbcc command, you can view that here.

Permissions needed for DBCC SQLPERF

In order to run this command, your user needs VIEW SERVER STATE permission on the server.

Filed Under: All Articles, SQL Server Administration Tagged With: dbcc, sql server

Monitoring SQL Server Transaction Log Growth Using DBCC SQLPERF(logspace)

Posted on October 13, 2013 Written by Andy Hayes 5 Comments

monitor transaction log growth with dbcc sqlperf(logspace)For this article, I am using DBCC SQLPERF(logspace) to get size information about the transaction logs. I hope you will find this post useful in helping to monitor the growth of your transaction logs.

The idea here is to capture and store the growth information at set intervals. This can provide a way to report on how much the logs are growing by and when. Reports are then delivered by an automated process.

If you issue DBCC SQLPERF(logspace), you will see output similar to the following:

dbcc sqlperf(logspace)

The output is self explanatory where for each database you can see the size of the transaction log and how much of it is in use currently.

So this is useful information on its own and even more useful if it can be captured on a regular basis and reported on.

So I wrote some T-SQL to do this and wrapped it up inside a stored procedure.

Firstly I will create a table to store the output of DBCC SQLPERF(logspace).

CREATE TABLE dbo.TransLogMonitor
(
LogID INT PRIMARY KEY IDENTITY(1,1),
LogDate DATETIME NOT NULL DEFAULT(GETDATE()),
DatabaseName VARCHAR(100) NOT NULL,
LogSizeMB DECIMAL(18, 2) NOT NULL,
LogSpaceUsed DECIMAL(18, 2) NOT NULL,
[Status] INT NOT NULL
)

CREATE INDEX IX_LogDate ON TransLogMonitor(LogDate);

Now this is the bit where we capture the output.

INSERT INTO TransLogMonitor(DatabaseName, LogSizeMB, LogSpaceUsed, [Status])
EXEC ('DBCC SQLPERF(logspace)')

DBCC execution completed. If DBCC printed error messages, contact your system administrator.
(5 row(s) affected)

Now run the code and then have a look at the data in your new table and it will look very similar to the output from running DBCC SQLPERF(logspace) to screen but with a couple of extra columns for the primary key values and the date that the data was inserted. The LogDate values are obviously very valuable enabling you to report on the data within any date range.

So lets say I wanted a report made available each day which gave me a comparison between yesterday’s and today’s growth, you could write something like this having the data collector running once a day.

DECLARE @Yesterday DATE = CONVERT(DATE, GETDATE()-1);
DECLARE @Today DATE = CONVERT(DATE, GETDATE());

SELECT COALESCE(Y.DatabaseName,T.DatabaseName) AS DatabaseName
, T.LogSizeMB AS TodayLogSizeMB
, Y.LogSizeMB AS YestLogSizeMB
, T.LogSizeMB - Y.LogSizeMB AS SizeMBDiff
, T.LogSpaceUsed AS TodayLSpaceUsed
, Y.LogSpaceUsed AS YestLSpaceUsed
, T.LogSpaceUsed - Y.LogSpaceUsed AS UsedDiff
FROM dbo.TransLogMonitor Y
FULL OUTER JOIN dbo.TransLogMonitor T ON T.DatabaseName = Y.DatabaseName
WHERE CONVERT(DATE, Y.LogDate) = @Yesterday AND CONVERT(DATE, T.LogDate) = @Today
ORDER BY TodayLogSizeMB DESC

Returning this output:

dbcc sqlperf(logspace)

Delivering the DBCC SQLPERF(logspace) report

Providing that you are running a version of SQL Server that is able to send email then you could deliver the report via this method. This example has attached the output as a text file but you can also format it up nicely as HTML if you want to.

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'DB1'
, @recipients = 'your smtp email address goes in here'
, @subject='Transaction Log Space Consumption Report'
, @query = 'EXEC DBName.dbo.usp_stored_procedure_name'
, @attach_query_result_as_file = 1

To wrap this up….

Once you have the code written, it could be scheduled to your preference. As I mentioned, this example is to run daily and compares yesterday and today but you could run it hourly if you wanted to and then write some T-SQL to analyze the hours of the day when you get the most growth. You could write daily reports for the whole week, something to report on the month etc. It could be a great report to help with capacity planning.

In order to deliver this, you could send the report by scheduling with SQL Server Agent, an SSIS package, Reporting Services or SQLCMD called from a batch file using windows task scheduler.

It’s really up to you 🙂

References

DBCC SQLPERF – link to documentation is here
sp_send_dbmail – link to documentation is here

Filed Under: All Articles, SQL Server Administration Tagged With: dbcc, sql server, transaction logs

  • « Previous Page
  • 1
  • 2
  • 3
  • 4
  • …
  • 6
  • Next Page »

Categories

  • All Articles (82)
  • Career Development (8)
  • MySQL Administration (18)
  • MySQL Performance (2)
  • SQL Server Administration (24)
  • SQL Server News (3)
  • SQL Server Performance (14)
  • SQL Server Security (3)
  • SQL Tips and Tricks (19)

Top 10 Popular Posts

  • Using sp_change_users_login to fix SQL Server orphaned users
  • How to shrink tempdb
  • MySQL SHOW USERS? – How to List All MySQL Users and Privileges
  • How to Transfer Logins to Another SQL Server or Instance
  • How to Delete Millions of Rows using T-SQL with Reduced Impact
  • T-SQL – How to Select Top N Rows for Each Group Using ROW_NUMBER()
  • New T-SQL features in SQL Server 2012 – OFFSET and FETCH
  • How to Kill All MySQL Processes For a Specific User
  • Using exec sp_who2 to help with SQL Server troubleshooting
  • How to move tempdb

Recent Posts

  • How to Setup MySQL Master Master Replication
  • How To Use SQL to Convert a STRING to an INT
  • How to set up MySQL Replication Tutorial
  • How to Use SQL CASE for Conditional Logic in Your SQL Queries
  • Using ISNULL in SQL Server to Replace NULL Values

Search

Connect

  • Twitter
  • Facebook
  • Google+
  • RSS

About

  • Cookie Policy
  • Disclaimer
  • About
Copyright © ‘2021’ DBA Diaries built on the Genesis Framework

This site uses cookies. We assume you are happy with cookies but click the link if you are not. Close