May 4, 2015 at 6:05 am
Is there anyway to find the permissions assigned to user. What level of access the user has in each db and the tables & is he able to see jobs
Each object and permissions
May 4, 2015 at 6:19 am
Try the below query. hope it will suffice your requirement
SELECT SCHEMA_NAME(schema_id) + '.' + name TableName
, type_desc
, HAS_PERMS_BY_NAME(SCHEMA_NAME(schema_id) + '.' + name,
'OBJECT', 'SELECT') AS have_select
, HAS_PERMS_BY_NAME(SCHEMA_NAME(schema_id) + '.' + name,
'OBJECT', 'UPDATE') AS have_update
, HAS_PERMS_BY_NAME(SCHEMA_NAME(schema_id) + '.' + name,
'OBJECT', 'INSERT') AS have_insert
, HAS_PERMS_BY_NAME(SCHEMA_NAME(schema_id) + '.' + name,
'OBJECT', 'DELETE') AS have_delete
, HAS_PERMS_BY_NAME(SCHEMA_NAME(schema_id) + '.' + name,
'OBJECT', 'EXECUTE') AS have_execute
FROM sys.all_objects
WHERE type_desc IN ('USER_TABLE', 'SQL_STORED_PROCEDURE', 'VIEW')
AND SCHEMA_NAME(schema_id) NOT IN ('sys', 'INFORMATION_SCHEMA')
ORDER BY type_desc, tablename
May 4, 2015 at 6:34 am
I started role as DBA. I want to find the permissions to the remaining users like below but when I execute this it is throwing error
Can't execute as the database prinicpal 'Bob' doesn't exist, this type principal can't be impersonated or you don't have permissions
EXECUTE AS USER = 'Bob';
-- Server rights
SELECT * FROM fn_my_permissions(NULL, 'SERVER');
-- Database rights
SELECT * FROM fn_my_permissions(NULL, 'DATABASE');
-- Specific per object rigths
SELECT T.TABLE_TYPE AS OBJECT_TYPE, T.TABLE_SCHEMA AS [SCHEMA_NAME], T.TABLE_NAME AS [OBJECT_NAME], P.PERMISSION_NAME FROM INFORMATION_SCHEMA.TABLES T
CROSS APPLY fn_my_permissions(T.TABLE_SCHEMA + '.' + T.TABLE_NAME, 'OBJECT') P
WHERE P.subentity_name = ''
UNION
SELECT R.ROUTINE_TYPE AS OBJECT_TYPE, R.ROUTINE_SCHEMA AS [SCHEMA_NAME], R.ROUTINE_NAME AS [OBJECT_NAME], P.PERMISSION_NAME
FROM INFORMATION_SCHEMA.ROUTINES R
CROSS APPLY fn_my_permissions(R.ROUTINE_SCHEMA + '.' + R.ROUTINE_NAME, 'OBJECT') P
ORDER BY OBJECT_TYPE, [SCHEMA_NAME], [OBJECT_NAME], P.PERMISSION_NAME
REVERT;
GO
May 4, 2015 at 7:01 am
do you have the "bob" in your db users list ?
May 4, 2015 at 7:21 am
Is the user "bob" a network account? If so, you'll need to include the domain like this:
EXECUTE AS login = 'domain_name\user_name';
--do your queries
REVERT;
May 4, 2015 at 12:51 pm
Thank you. Now it is working
May 4, 2015 at 1:29 pm
Glad to hear it. Which one was it? Did the user exist or what it a network account?
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply