Sql-server – Hidden user rights

permissionsSecuritysql-server-2008-r2

I'm running SQL Server 2008 R2 SP2. I have a SQL Server user, that the application uses to connect to my database. I've removed the user from all roles except public and the login from all database roles.

When I log into my SQL Server, I'm able to connect to the database and although I can't read from any tables, I can still create and drop tables.

In querying

sys.database_role_members
sys.database_principals
sys.server_role_members
sys.server_principals

… I'm not able to find any roles associated with the SQL user.

Is there someplace else I can look to find out how this user has permission to create and drop tables?

What right needs to be revoked to remove this users ability to create and drop data objects?

Best Answer

I bet CREATE TABLE is one of the things that shows up here (replace foo with the login name you're concerned about):

SELECT p.[permission_name], p.class_desc
FROM sys.server_principals AS sp
INNER JOIN sys.database_principals AS dp
ON sp.sid = dp.sid
INNER JOIN sys.database_permissions AS p
ON dp.principal_id = p.grantee_principal_id
WHERE sp.name = N'foo'
AND p.state_desc = N'GRANT'
AND p.class_desc IN (N'DATABASE', N'SCHEMA');

For each database permission found that you don't want them to have, run a revoke (for example, for CREATE TABLE):

REVOKE CREATE TABLE TO foo;

And for each schema where they have explicit privileges (like ALTER) - just replace schema with the actual schema:

REVOKE ALTER ON SCHEMA::schema TO foo;

They may also own a schema (not dbo, but maybe their default schema or another schema), so check that:

SELECT s.name
FROM sys.server_principals AS sp
INNER JOIN sys.database_principals AS dp
ON sp.sid = dp.sid
INNER JOIN sys.schemas AS s
ON dp.principal_id = s.principal_id
WHERE sp.name = N'foo';

If this comes up with any (again, replace schema with the actual schema):

ALTER AUTHORIZATION ON SCHEMA::schema TO dbo;

Related Solutions

Sql-server – Allow user to do anything within his own schema but not create or drop the schema itself

There is no need to grant CONTROL on the schema.
The permission required to DROP SCHEMA is either CONTROL on the schema or ALTER ANY SCHEMA at the database level, and that is why your user was able to drop the schema. Removing these two permissions will prevent the role-associated users from creating and droping the schema (unless they have higher level permissions of course).

The required permission to CREATE ALTER and DROP other objects is the CREATE permission for the object type (table\procedure\function\view) combined with ALTER permission on the schema.
You already have these permissions in your script, so all that you have to do is remove the CONTROL permission. For reference, here is a BOL list of DDL statements where You can find the required permission for all object types.

For the lazy, here is your code after removing the unnecessary permission:

CREATE ROLE myrole AUTHORIZATION dbo;
EXEC sp_addrolemember 'myrole', 'myuser';

CREATE SCHEMA myschema AUTHORIZATION dbo;

GRANT ALTER, DELETE, EXECUTE, INSERT, REFERENCES, SELECT,
          UPDATE, VIEW DEFINITION ON SCHEMA::myschema TO myrole;

GRANT CREATE TABLE, CREATE PROCEDURE, CREATE FUNCTION, CREATE VIEW TO myrole;

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 – Allow user to do anything within his own schema but not create or drop the schema itself

Sql-server – User can alter procedures without permission

Related Question