Running the T-Sql script below will return a list of essentially all users from all databases in an instance. This is a particularly useful script if you are assessing the back end security matrix for a company. You may want to do this ahead of GDPR coming into effect to determine if your company is compliant . Data for the following fields are provided.
SET NOCOUNT ON;
DECLARE @Database TABLE (DbName SYSNAME);
DECLARE @DbName AS SYSNAME;
DECLARE @sql AS VARCHAR(MAX);
DECLARE @ServerName AS SYSNAME;
SET @ServerName = (
IF OBJECT_ID(N'tempdb..#User') IS NOT NULL
DROP TABLE #User
CREATE TABLE #User (
,UserName SYSNAME NULL
,LoginType VARCHAR(255) NULL
,Permission VARCHAR(255) NULL
,StateOf VARCHAR(255) NULL
,AccessLevel VARCHAR(255) NULL
,ObjectName SYSNAME NULL
SET @DbName = '';
INSERT INTO @Database (DbName)
WHERE NAME <> 'tempdb'
ORDER BY NAME ASC;
WHILE @DbName IS NOT NULL
SET @DbName = (
WHERE DbName > @DbName
PUT CODE HERE
SET @sql = '
INSERT INTO #User (
SELECT ''' + @ServerName + ''' AS ServerName
,''' + @DbName + ''' AS DbName
,princ.name AS UserName
,princ.type_desc AS LoginType
,perm.permission_name AS Permission
,perm.state_desc AS StateOf
,perm.class_desc AS AccessLevel
,object_name(perm.major_id) AS ObjectName
FROM ' + QUOTENAME(@DbName) + '.sys.database_principals princ
LEFT JOIN ' + QUOTENAME(@DbName) + '.sys.database_permissions perm ON perm.grantee_principal_id = princ.principal_id
DROP TABLE #User;
Server name is included in case you’d like to run this script across multiple environments. The results of each server can be combined in an excel file more easily.
DbName and UserName are self explanatory.
LoginType, whether the login is via Windows or SQL Server.
Permission, what actions the user can perform e.g. SELECT, UPDATE etc.
StateOf, whether permission to the object has been granted or denied.
AccessLevel, whether the user has access to the entire database or is restricted to objects etc. though it is good practice that a user should have locked down access. From a GDPR perspective it would be hard to make a case for allowing a user to have access to an entire database instead of a few columns, views or stored procedures.
ObjectName, if the user only has restricted access to specific objects this script will list them.
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.
DECLARE @database_name SYSNAME
DECLARE databases_cursor CURSOR
WHERE STATE IN (0)
AND database_id > 4
ORDER BY NAME
CREATE TABLE #guest_users_enabled (
WHILE @@FETCH_STATUS = 0
SET @sqlcmd = N'use ' + @database_name + ';
insert into #guest_users_enabled
SELECT ''' + @database_name + ''' as database_name, name,
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
ORDER BY database_name ASC
DROP TABLE #guest_users_enabled
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];
REVOKE CONNECT FROM GUEST;
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.
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;
DROP USER myUser;
What is the SQL Server Master Database used for?
The master database is used by SQL Server to contain all of the system level information:
- Linked servers
- Configuration settings
- Information about the other databases on this instance and the location of their files
If the master database is not present, SQL Server cannot start.
This is way you should always take regular backups of the master database.
as if SQL Server suffers a failure, those changes will be lost and you’ll be in a lot of trouble.
So the master database is needed for SQL Server to work. Logic and objects needed to enable your application should not be tangled up in there so you should always avoid creating objects in the master database. Use a user database instead.
In saying that everyone has made the mistake of not specifying the database they want to USE in a script and by default objects get written to the Master database. Or you might have inherited the responsibility for an application which did not conform to best practices and it uses objects created in Master.
To gain visibility of these objects just run the script below.
SELECT o.object_id AS ObjectId
,o.NAME AS ObjectName
,o.type_desc AS ObjectType
,o.create_date AS CreateDate
,o.modify_date AS ModifyDate
,SUM(st.row_count) AS RowCnt
,CAST(SUM(st.used_page_count) / 128.0 AS DECIMAL(36, 1)) AS DataSize_MB
FROM master.sys.objects o
LEFT JOIN master.sys.dm_db_partition_stats st ON st.object_id = o.object_id
AND st.index_id < 2
GROUP BY o.object_id
HAVING o.is_ms_shipped = 0
AND o.NAME <> 'sp_ssis_startup'
AND o.type_desc NOT LIKE '%CONSTRAINT%'
ORDER BY CAST(SUM(st.used_page_count) / 128.0 AS DECIMAL(36, 1)) DESC
When a developer is asked to create a business report it always involves a meeting of two disciplines.
The developer has the technical knowledge of pulling raw data, manipulating it and presenting interpreted data as information to be consumed by an end user. They may also know the location and source of the data to be used and how regularly this source is refreshed or updated with new data.
The Requester of the report will usually be the person with business domain knowledge. They understand the business rules which need to be applied to the data to transform it into information.
An obvious example for this is the relationship between a SQL developer working in an accounting environment. The developer is not an account and the account is not a developer but the two need to be able to collaborate and communicate to ensure that a report eventually outputs the correct information at the require period reliably.
Failure in achieving this goal can have disastrous consequences. For example projecting further revenue based on faulty data.
A good start for creating a foundation for effective communication and collaboration is for the developer and requester to step through a requirements template. This gives the report a structure and focus while also serving as a means of documenting the creation of the report from a business perspective and a future resource from a development perspective, i.e. the requirement of a future developer new to the report updating and modifying the report.
Attached is an example Template.
The Requirements sheet deals with questions like:
Who is the requester?
Why is the report needed?
What information will the report return?
This sheet will primarily be filled in by the requester.
The Report Fields sheet tries to plug the gap in knowledge between the requester and the developer by exploring what is the information required and what fields, calculations correspond to this information. The developer and requester may need to step through this sheet together to ensure their intended outcomes align.