Monthly Archives: November 2016

How to identify databases with Guest user enabled

Database Administrator, dba, sql, SQL Server, T-SQL, , , , , , , , , , , , ,

As best practice it is recommended to disable guest user in every user database, i.e. not master, msdb and tempdb, to improve the security of SQL Server. Guest user permits access to a database for any logins that are not mapped to a specific database user. The guest user cannot be dropped but it can be disabled by revoking the CONNECT permission.

Use the script below to identify which databases have guest user enabled. 

USE master;
GO

DECLARE @database_name SYSNAME
	,@sqlcmd NVARCHAR(4000)

DECLARE databases_cursor CURSOR
FOR
SELECT NAME
FROM sys.databases
WHERE STATE IN (0)
	AND database_id > 4
ORDER BY NAME

CREATE TABLE #guest_users_enabled (
	database_name SYSNAME
	,user_name SYSNAME
	,permission_name NVARCHAR(128)
	,state_desc NVARCHAR(6)
	)

OPEN databases_cursor;

FETCH NEXT
FROM databases_cursor
INTO @database_name;

WHILE @@FETCH_STATUS = 0
BEGIN
	SET @sqlcmd = N'use ' + @database_name + ';

        insert into #guest_users_enabled

        SELECT ''' + @database_name + ''' as database_name, name,

        permission_name, state_desc

        FROM sys.database_principals dpr

        INNER JOIN sys.database_permissions dpe

        ON dpr.principal_id = dpe.grantee_principal_id

        WHERE name = ''guest'' AND permission_name = ''CONNECT'''

	EXEC sp_executesql @sqlcmd

	FETCH NEXT
	FROM databases_cursor
	INTO @database_name;
END

SELECT database_name
	,user_name
	,permission_name
	,state_desc
FROM #guest_users_enabled
ORDER BY database_name ASC

DROP TABLE #guest_users_enabled

CLOSE databases_cursor;

DEALLOCATE databases_cursor;
GO
Use the script below, changing the placeholder database with the database identified in the script above, to revoke permission for the guest user to connect to that database.
USE [database name];

GO

REVOKE CONNECT FROM GUEST;

GO

How to resize TempDB in SQL Server

Database Administrator, dba, sql, SQL Server, T-SQL, , , , , , , , , ,

SQL Server Tempdb is used to store temporary objects. By default the initial size of the tempdb is too small for a production database engaged in any significant enterprise activities, therefore it should be sized accordingly on setup.

Failure to do so will have a negative performance impact when the database is first put into operation as it will need to grow to a more fitting size. Worse still, every time SQL Server is restarted the  tempdb will be recreated with its initial size. So SQL Server has to initiate autogrowth steps to grow the database file again and performance will be impacted negatively while it grows.

To compare the initial tempdb size to the current size run the script below.

USE master;
GO

SELECT mf.database_id
	,mf.NAME
	,mf.size * 8 / 1024 AS Initial_Size
	,df.size * 8 / 1024 AS Current_Size
FROM sys.master_files mf
/*adding info about current file size*/
INNER JOIN tempdb.sys.database_files df ON mf.NAME = df.NAME
/*filtering for tempdb only*/
WHERE mf.database_id = 2;
GO
You can set the tempdb initial size to the displayed current size if you think it will need to grow to this size again or take it as a simple guide and set the initial size less than its current size and allow it to grow as it needs.
To change the tempdb size run the script below replacing all the values in the placeholders with your specific values, use the example script further down as a guide. The files tempdev and templog are typically what the tempdb files are called in a default installation. (You can run the first script again to confirm success)

USE master;
GO

ALTER DATABASE TempDB MODIFY FILE (
	NAME = [logical file name of the tempdb data file]
	,SIZE = [value] MB
	);
GO


USE master;
GO

ALTER DATABASE TempDB MODIFY FILE (
	NAME = [tempdev]
	,SIZE = 4 MB
	);
GO


ALTER DATABASE TempDB MODIFY FILE (
	NAME = [templog]
	,SIZE = 3 MB
	);
GO

How to drop a user from a SQL Server database when you encounter the error message “The database principal owns a schema in the database, and cannot be dropped”

Database Administrator, dba, sql, SQL Server, T-SQL, , , , , , , , , , ,

principalowner

So if you have encountered the error above “The database principal owns a schema in the database, and cannot be dropped” you will not be able to drop the user until ownership of the effected schema has been transferred to another user/role. In order to drop the user, you have to find the schema that is assigned first. You can do this by running the script below replacing myUser with the user name in question. 

SELECT name 
FROM  sys.schemas 
WHERE principal_id = USER_ID(‘myUser’)
Then, use the schema found from the above query in place of the SchemaName below. This transfers ownership to dbo. You may need to alter authorization for multiple schema. Just run the statement for each returned schema replacing SchemaName. You can then drop your user.
ALTER AUTHORIZATION ON SCHEMA::SchemaName TO dbo;
 
GO

DROP USER myUser;