Three Database Permission related questions

Question

Three Database Permission related questions

HookSQLDBA7

SSChampion

Points: 10709
More actions
July 10, 2017 at 9:24 am

#331789

We are running SQL Server 2012 EE SP3 on Windows Server 2008 R2 Enterprise. I have the following three permission related questions:
1. How to hide database user roles and permissions from users/developers on a dev and test server?
2. If the database has one schema and if Select permission is giving on that schema, is db_datareader required?
3. In the Database Properties dialog, on the Permissions page, select User or Role, why are there double of some permissions listed under 'Permissions for User/Role'?
Permission Grantor Grant With Grant Deny
Create function
Create function
Create procedure
Create procedure
Create table
Create table
Create view
Create view
View definition
View definition
Thanks in advance!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Sue_H SSC Guru Points: 90860 More actions · Answer 1

1. You could try denying select on the views database_prinicipals and database_role_members. That will prevent the users from seeing those but I'm not sure if it introduces other issues - I would imagine it could but I haven't tested it.
2. No it's not needed as long as all they need to select from is in that schema.
3. You can have multiples when some permissions have already been granted, denied, etc as more than one user could grant or deny. Usually you'll see one of the rows with nothing selected and the other rows have at least one permission checked. They may be exactly the same object, permissions but different grantors.

Sue

HookSQLDBA7 SSChampion Points: 10709 More actions · Answer 2

Thanks Sue.

Another permissions related question, I removed permissions from the individual developers' db users, created a db role, assigned permissions to the db role, and then made the developers members of the db role. Now, in SSMS, under Security folder, I double click on the individual login, go to Login Properties and the User Mapping page, it does NOT show a user mapped to the login for the database in question. (This does not appear to cause a problem as the users are still able to access everything within the db.) Just curious, is this (no db user mapped to login) happening because I have moved the individual developers logins into a db role? Or, is it happening because I have added the GRANT CONNECT permission to the db role and removed it from the individual developer db user?

The db role has the following db level and schema permissions:

USE DBName
GO
GRANT CONNECT TO [Developers]
GO
GRANT CREATE FUNCTION TO [Developers];
GO
GRANT CREATE PROCEDURE TO [Developers];
GO
GRANT CREATE TABLE TO [Developers];
GO
GRANT CREATE VIEW TO [Developers];
GO
GRANT VIEW DEFINITION TO [Developers];
GO

GRANT ALTER ON SCHEMA::[SchemaName] TO [Developers]
GO
GRANT SELECT ON SCHEMA::[SchemaName] TO [Developers]
GO
GRANT INSERT ON SCHEMA::[SchemaName] TO [Developers]
GO
GRANT UPDATE ON SCHEMA::[SchemaName] TO [Developers]
GO
GRANT DELETE ON SCHEMA::[SchemaName] TO [Developers]
GO
GRANT EXECUTE ON SCHEMA::[SchemaName] TO [Developers]
GO
GRANT CREATE SEQUENCE ON SCHEMA::[SchemaName] TO [Developers]
GO
GRANT REFERENCES ON SCHEMA::[SchemaName] TO [Developers]
GO

Sue_H SSC Guru Points: 90860 More actions · Answer 3

Because of the last one:

I have added the GRANT CONNECT permission to the db role and removed it from the individual developer db user

The login itself does not have connect permissions to the database. When you check the logins like that in SSMS, it will query the database_permissions and database_principals views to check the state column to see if that login itself has database access (connect permissions). You can run profiler or an extended events session when you check the logins - it will show you what is being executed to determine access.

Sue

HookSQLDBA7 SSChampion Points: 10709 More actions · Answer 4

Thanks again Sue. Now, hopefully my last question, now that I have added the GRANT CONNECT permission to the db role and removed it from the individual developer db user, does this present any disadvantages with auditing the login or tracing the login?

Sue_H SSC Guru Points: 90860 More actions · Answer 5

HookSqlDba7 - Wednesday, July 12, 2017 2:50 PM
Thanks again Sue. Now, hopefully my last question, now that I have added the GRANT CONNECT permission to the db role and removed it from the individual developer db user, does this present any disadvantages with auditing the login or tracing the login?

It will still pick up the logins, activities, queries the user does, etc. And it will be associated with the user, not the role, if you are capturing Login Name.

Sue