Who can do what?
One of the key things that a DBA should know in their database is what can users actually do.
In this post I want to focus on one of the many checks performed in the sp_WOxCompliant script I have available for download here.
This script has been modified so you can run it on its own to check your systems and report what specific users may have excessive permissions in your databases.
This code uses the function sys.fn_my_permissions as well as the EXECUTE AS methods to loop through all users and then run the function as the particular user.
The script then looks for certain key word permissions that are considered “excessive”. Permissions such as ALTER, DELETE, CREATE, TAKE, etc.
You then get a nice dump of what that user could do in your database and can take action to implement changes if required.
The Script:
use <databasename>;
go
set nocount on;
-- define a temp table to store our Principals
CREATE TABLE #DBUs
(
DBUID INT IDENTITY(1, 1) ,
DBUserName VARCHAR(125)
);
-- load the principals into the temp table
INSERT #DBUs ( DBUserName)
SELECT [DatabaseUserName] = princ.[name]
FROM sys.database_principals princ
WHERE princ.[type] IN ('S','U','C')
AND princ.authentication_type <> 0 and princ.name <> 'dbo'; -- ignore the dbo, they can do anything already
DECLARE @intFlag INT = 1 ,
@intMaxFlag INT = ( SELECT MAX(DBUID) FROM #DBUs),
@DBUserName VARCHAR(125),
@count tinyint
-- loop through each principal and check if they have 'excessive' permissions
WHILE (@intFlag <= @intMaxFlag)
BEGIN
SET @DBUserName = ( SELECT DBUserName FROM #DBUs WHERE DBUID = @intFlag);
EXECUTE AS USER = @DBUserName;
SELECT @DBUserName, permission_name
FROM fn_my_permissions(NULL, 'DATABASE')
WHERE permission_name LIKE 'CONTROL%'
OR permission_name LIKE 'ALTER%'
OR permission_name LIKE 'TAKE%'
OR permission_name LIKE 'VIEW%'
OR permission_name LIKE 'DELETE%'
OR permission_name LIKE 'UPDATE%'
OR permission_name LIKE 'INSERT%'
OR permission_name LIKE 'SELECT%'
OR permission_name LIKE 'CREATE%';
REVERT;
SET @intFlag = @intFlag + 1
END
-- Cleanup
DROP TABLE #DBUs
Now, keep in mind that it may be OK for some users to have these particular permissions. A lot of these may be acquired through the group or role membership of the user.
In either case the permissions should be documented for each user and group so that you always know what different people can do in your system.
You can also create new roles to restrict the permissions to more appropriate levels if you find too many people have too much permission.
[divider_top][icon_box icon=”users” title=”Who is Chris Bell?”]
Chris Bell, SQL Server MVP, MCSE & MCITP, is a 20 year veteran of using Microsoft products & SQL Server to create solutions for businesses, organizations and individuals. Chris speaks, blogs, writes articles and makes media of all kinds regarding SQL Server at WaterOxConsulting.com. Chris is also the founder of WaterOx Consulting, Inc. which features SQL Server consulting services, training and the FREE sp_WOxCompliant compliance check script for your SQL Server environment.
Click here to contact Chris or to follow him on twitter. [/icon_box]