Sql-server – Granting permission to the service account

permissionsSecurityservice-accountssql server

I am currently granting permissions to a service account on a database. I want to ensure if the granting db_datareader and db_datawriter database roles is the same as my grant command.

Adding service account to db_datareader and db_datawriter database roles.

EXEC sp_addrolemember N'DB_DATAREADER', N'INT\svc-w-corerefdata-de';
EXEC sp_addrolemember N'DB_DATAWRITER', N'INT\svc-w-corerefdata-de';

Creating a role, granting permissions to it and adding the service account to the role.

IF DATABASE_PRINCIPAL_ID('AlterPermCoreRefData') IS NULL
    BEGIN 
        CREATE ROLE [AlterPermCoreRefData] AUTHORIZATION [dbo]
    END

GRANT EXECUTE, ALTER, SELECT, DELETE, INSERT, UPDATE ON database::[CoreReferenceStaging] TO [AlterPermCoreRefData]

EXEC sp_addrolemember 'AlterPermCoreRefData', 'INT\svc-w-corerefdata-de'

Best Answer

Have a look at the fixed db roles in SQL server:

In your first example (db_datareader and db_datawriter), the service account can only read and modify data in the tables. With these 2 roles, the service account cannot modify the tables or perform and DDL. Nor does it have EXECUTE permissions.

In your second example, you've given all the permissions above but you've added EXECUTE and ALTER. This allows that account to execute SPs and also modify objects (structurally, or otherwise) within the DB.

So, to answer your question, no, they are not the same. The second example has more permissions and can do more.

Related Solutions

Sql-server – The SELECT permission was denied on the (View) object after explicit grant select on view to user

Your response pointed me in the right direction.

After looking at the object and verifying the permissions many times, I shifted my focus to the Login and User.

Apparently the login is mapped to a different user. So when I checked the server_principal against the database_principal there was a mismatch.

The login is called appuser and i am also trying to grant permissions inside the database to appuser. Unfortunately someone created a different database user which is actually called appwebuser.

So once i discovered that we were granting permissions to the wrong user and update the code to the correct database user the problem was resolved.

I used the following query to track down the login. I add in the servername and dbname so i can check against my different environments and report to the customer.

SELECT 
@@servername  as [server_name]
,db_name()  as [database_name]
,sp.name as [server_principal_name]
,sp.type as [server_principal_type]
,sp.type_desc as [server_principal_type_desc]
,sp.create_date as [server_principal_create_date]
,sp.default_database_name as [server_principal_default_database_name]
,dp.name as [database_principal_name]
,dp.type as [database_principal_type]
,dp.type_desc as [database_principal_type_desc] 
,dp.default_schema_name as [database_principal_default_schema_name]
FROM 
    sys.database_principals  dp INNER JOIN
    sys.server_principals sp ON (dp.sid = sp.sid)
WHERE 
    sp.name like 'appuser'
    or dp.name like  'appuser'
    or sp.name like 'appwebuser'
    or dp.name like  'appwebuser'

Sql-server – User can alter procedures without permission

Do you see anything that has been granted or role membership that shouldn't exist?

EXECUTE AS USER = N'your user';
GO
SELECT * FROM sys.fn_my_permissions(NULL, NULL);
GO
REVERT;
GO

SELECT * FROM sys.database_permissions
WHERE grantee_principal_id = USER_ID(N'your user');
GO

SELECT r.name
FROM sys.database_role_members AS m
INNER JOIN sys.database_principals AS p
ON m.member_principal_id = p.principal_id
INNER JOIN sys.database_principals AS r
ON m.role_principal_id = r.principal_id
WHERE p.name = N'your user';

SELECT r.name
FROM sys.server_role_members AS m
INNER JOIN sys.server_principals AS p
ON m.member_principal_id = p.principal_id
INNER JOIN sys.server_principals AS r
ON m.role_principal_id = r.principal_id
WHERE p.name = N'your login';

It is possible these developers are in the db_owner role without your knowledge or have been granted ALL on the dbo or another schema.

Make sure you execute these statements in the right database, and also validate that your users are logging in as who they say they are logging in as. Perhaps they have the sa password and you don't know it. Finally, you should make sure that the database principal maps to the correct server-level login.

SELECT dp.name, sp.name
FROM sys.database_principals AS dp
FULL OUTER JOIN sys.server_principals AS sp
ON dp.sid = sp.sid
WHERE 
(
  dp.type <> 'R' 
  OR sp.type NOT IN ('C', 'K')
)
ORDER BY 
  dp.name COLLATE DATABASE_DEFAULT + sp.name COLLATE DATABASE_DEFAULT DESC;

Best Answer

Related Solutions

Sql-server – The SELECT permission was denied on the (View) object after explicit grant select on view to user

Sql-server – User can alter procedures without permission

Related Question