SQL Server Security – Determine Effective Permissions for Accounts

Securitysql server

With SQL Server – if you have a local or domain user (including accounts like LOCAL SYSTEM) how can you determine

What effective permissions they have on a database
How those permissions are granted (e.g. which roles, any direct etc)

Best Answer

In the Server you can look for

exec sp_helpsrvrolemember sysadmin exec sp_helpsrvrolemember dbcreator

In the databases that you want

exec sp_helprolemember db_datareader
exec sp_helprolemember db_datawriter
exec sp_helprolemember db_ddladmin
exec sp_helprolemember db_owner

and all direct permissions

exec sp_helprotect null,null

Related Solutions

Sql-server – Running an SSIS package owned by a domain user from SQL Server running on a local service account

My personal opinion is that option #1 is the way to go. But I see no need for you to have to grant the domain account local administrator access. It seems to me that it requires access to certain folders and files and so you could grant the domain user access to only the resources that it needs to run the package successfully. This can be done through the file/folder properties dialog box and select the security tab - there should be no need to set it for every file and folder as you could set the permissions of the parent directory and set them to override child properties.

I hope this helps you.

SQL Server – How to Find Out Why a User Has Certain Effective Permissions

You can find some good information regarding security from the article below.

Reviewing SQL Server Permissions | TechRepublic http://tek.io/KfzEyp

Except:

The following query uses the sys.database_permissions system view to indicate which users had specific permissions inside the current database.

SELECT
        dp.class_desc
       ,dp.permission_name
       ,dp.state_desc
       ,ObjectName = OBJECT_NAME(major_id)
       ,GranteeName = grantee.name
       ,GrantorName = grantor.name
    FROM
        sys.database_permissions dp
        JOIN sys.database_principals grantee
        ON dp.grantee_principal_id = grantee.principal_id
        JOIN sys.database_principals grantor
        ON dp.grantor_principal_id = grantor.principal_id

Best Answer

Related Solutions

Sql-server – Running an SSIS package owned by a domain user from SQL Server running on a local service account

SQL Server – How to Find Out Why a User Has Certain Effective Permissions

Related Question