Find users rights

Product:
Microsoft SQL server 2016

Issue:
Try to find what a users have for access rights inside a database.

Suggested solution:

Enter this code as sysadmin, to find person access rights in one database:

EXECUTE AS LOGIN = 'YourDomain\User.Name' --Change This

USE database  --Change This

GO

SELECT * FROM fn_my_permissions(null, 'database');

REVERT

Enter this to see what databases a person have access to

EXECUTE AS LOGIN = 'YourDomain\User.Name' --Change This
SELECT [name]
FROM MASTER.sys.databases
WHERE HAS_DBACCESS([name]) = 1
REVERT

To find your rights in the current database:

SELECT * FROM fn_my_permissions(null, 'database');

To list dbowners

SELECT user_name(member_principal_id)

FROM   sys.database_role_members

WHERE  user_name(role_principal_id) = 'db_owner'

More Information:

https://stackoverflow.com/questions/7048839/sql-server-query-to-find-all-permissions-access-for-all-users-in-a-database

http://www.sqlnuggets.com/blog/sql-scripts-check-logins-permissions/

http://www.sqlnuggets.com/blog/sql-scripts-list-databases-a-login-can-access/

https://sqlgeekspro.com/users-db_owner-access-databases/

https://www.sqlserver-dba.com/2015/05/find-database-users-mapped-to-db_owner-role.html

https://schottsql.blogspot.com/2016/04/getting-all-permissions-for-server.html

fn_my_permissions() – Sprawdzenie jakie mamy uprawnienia do bazy danych