List user rights in mssql

List_Rights_MS_SQL.sql

-- https://www.mssqltips.com/sqlservertip/6828/sql-server-login-user-permissions-fn-my-permissions/

-- List all permissions
-- SELECT * FROM sys.fn_builtin_permissions(DEFAULT);

-- SELECT DISTINCT permission_name FROM sys.fn_builtin_permissions(DEFAULT) ORDER BY permission_name;
-- show the different classes 
SELECT DISTINCT parent_class_desc FROM sys.fn_builtin_permissions(DEFAULT);
-- SELECT * FROM sys.fn_builtin_permissions(DEFAULT) WHERE NULLIF(parent_class_desc, '') IS NULL; 



SELECT * FROM fn_my_permissions(NULL, 'SERVER');  
SELECT * FROM fn_my_permissions(NULL, 'DATABASE');  
SELECT * FROM fn_my_permissions(NULL, 'SCHEMA');  



SELECT * FROM fn_my_permissions(NULL, 'SERVER') WHERE permission_name LIKE '%trace%'
SELECT * FROM fn_my_permissions(NULL, 'DATABASE') WHERE permission_name LIKE '%trace%'
SELECT * FROM fn_my_permissions(NULL, 'SCHEMA') WHERE permission_name LIKE '%trace%'


SELECT * FROM fn_my_permissions('dbo.T_Benutzer', 'OBJECT')
SELECT * FROM fn_my_permissions('INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE', 'OBJECT')



USE DB_Name;
GRANT ALTER ANY ASSEMBLY TO [SomeUser]
-- REVOKE ALTER ANY ASSEMBLY FROM [SomeUser]

-- sp_add_trusted_assembly : 
-- Requires membership in the sysadmin fixed server role or CONTROL SERVER permission.
USE master;
GRANT CONTROL SERVER TO [SomeUser]
-- REVOKE CONTROL SERVER TO [SomeUser]


USE master;
-- GRANT UNSAFE ASSEMBLY TO [SomeUser];
REVOKE UNSAFE ASSEMBLY TO [SomeUser];


USE master;
-- SELECT * FROM sys.trusted_assemblies 
-- GRANT VIEW SERVER STATE TO [SomeUser]
REVOKE VIEW SERVER STATE TO [SomeUser]


USE master; -- Switch to the master database, as server-level permissions are managed there
GRANT ALTER TRACE TO [SomeUser]
REVOKE ALTER TRACE TO [SomeUser]




SELECT * FROM fn_my_permissions(NULL, 'SERVER')
WHERE permission_name LIKE '%control%'



SELECT * FROM fn_my_permissions(NULL, 'SERVER');  
SELECT * FROM fn_my_permissions(NULL, 'DATABASE');  
SELECT * FROM fn_my_permissions(NULL, 'SCHEMA');  

SELECT * FROM fn_my_permissions(NULL, 'SERVER') WHERE permission_name LIKE '%trace%'
SELECT * FROM fn_my_permissions(NULL, 'DATABASE') WHERE permission_name LIKE '%trace%'
SELECT * FROM fn_my_permissions(NULL, 'SCHEMA') WHERE permission_name LIKE '%trace%'