security

SQL Server articles related to security.

Find all permissions for all users in a database

You need to run the following query to list permissions for all users in a database.

WITH cteUserPermissions
AS
(
-- Permissions provisioned to a sql user
-- or windows user/group directly
SELECT  
        [UserName] = CASE DP.[type]
                                        WHEN 'S' THEN DP.[name]
                                        WHEN 'U' THEN L.[name]
                                 END,
        [UserType] = CASE DP.[type]
                                        WHEN 'S' THEN 'SQL User'
                                        WHEN 'U' THEN 'Windows User'
                                 END,  
        [DatabaseUserName] = DP.[name],      
        [Role] = null,      
        [PermissionState] = P.[state_desc],
        [PermissionName] = P.permission_name,
        [ObjectName] = OBJECT_NAME(P.major_id),
        [ObjectType] = O.type_desc    
FROM        
        sys.database_principals DP
        LEFT JOIN sys.login_token L ON DP.[sid] = L.[sid]
        LEFT JOIN sys.database_permissions P ON P.[grantee_principal_id] = DP.[principal_id]
        LEFT JOIN sys.objects O ON P.[major_id] = O.[object_id]
WHERE DP.[type] in ('S','U')  

UNION

-- Permissions provisioned to a sql user or windows user/group
-- through a database or application role
SELECT  
        [UserName] = CASE M.[type]
                                        WHEN 'S' THEN M.[name]
                                        WHEN 'U' THEN L.[name]
                                 END,
        [UserType] = CASE M.[type]
                                        WHEN 'S' THEN 'SQL User'
                                        WHEN 'U' THEN 'Windows User'
                                 END,
        [DatabaseUserName] = M.[name],  
        [Role] = R.[name],      
        [PermissionState] = P.[state_desc],
        [PermissionName] = P.permission_name,
        [ObjectName] = OBJECT_NAME(P.major_id),
        [ObjectType] = O.type_desc
   
FROM        
        sys.database_role_members DRM
        JOIN sys.database_principals R ON R.[principal_id] = DRM.[role_principal_id]
        JOIN sys.database_principals M ON M.[principal_id] = DRM.[member_principal_id]
        LEFT JOIN sys.login_token L ON M.[sid] = L.[sid]
        LEFT JOIN sys.database_permissions P ON P.[grantee_principal_id] = R.[principal_id]
        LEFT JOIN sys.objects O ON P.[major_id] = O.[object_id]

UNION

-- Permissions provisioned to the public role,
-- which everyone gets by default
SELECT  
        [UserName] = '{All Users}',
        [UserType] = '{All Users}',
        [DatabaseUserName] = '{All Users}',      
        [Role] = DP.[name],      
        [PermissionState] = P.[state_desc],
        [PermissionName] = P.permission_name,
        [ObjectName] = OBJECT_NAME(P.major_id),
        [ObjectType] = O.type_desc
FROM
        sys.database_principals DP
        LEFT JOIN sys.database_permissions P ON P.[grantee_principal_id] = DP.[principal_id]
        JOIN sys.objects O ON O.[object_id] = P.[major_id]
WHERE
        DP.[type] = 'R'                         -- only roles
        AND DP.[name] = 'public'        -- only public role
        AND O.is_ms_shipped = 0
)
SELECT *
FROM cteUserPermissions
WHERE UserName in ('{All Users}', 'myusername')
ORDER BY UserName, ObjectName, PermissionState, PermissionName

Creating readonly views in Sql Server

According to MSDN, views composed of simple selects automatically allow you to use insert/update/delete statements on the table.

There are several different options for avoiding this and in this article I will present various ways to make a view read only in a SQL Server database:

1. Permissions
You have the option to remove UPDATE/DELETE/INSERT permissions on the view.

2. UsingINSTEAD OFtrigger

CREATE TRIGGER dbo.MySampleView_Trigger_OnInsertOrUpdateOrDelete]
ON dbo.MySampleView
INSTEAD OF INSERT, UPDATE, DELETE
AS
BEGIN
        RAISERROR ('You are not allow to update this view!', 16, 1)    
END

3. UsingUNIONoperator
You could specify an UNION operator in order to make SQL Server fail during the INSERT/UPDATE/DELETE operation. See the below example:

ALTER VIEW dbo.MySampleView
as
SELECT col1, col2 FROM dbo.MySampleTable
UNION
SELECT NULL, NULL WHERE 1 =0

Subscribe to RSS - security