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:
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