0

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?

  • 1
    Did you check sys.database_permissions? It seems you've only checked for role membership but not explicit grant permissions. – Aaron Bertrand Apr 24 '14 at 23:43
1

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;
  • the first query only came back with Connect Database. The second query came back with nothing. I tried running the revoke anyway but that didn't help. The user is still able to drop and create a table on the database. – Lumpy Apr 25 '14 at 12:20
  • Can they do this in any database or only this one? Is it a Windows login? If so, what groups is it a member of in AD? – Aaron Bertrand Apr 25 '14 at 12:36
  • It is not a windows login. The user appears to be able to do this on all dtabases although I have been using a single database as my test case. – Lumpy Apr 25 '14 at 12:41
  • if it's helpful the login does not have any roles assigned to it either. – Lumpy Apr 25 '14 at 12:49
0

I would check the following:

  1. Check assumptions

    USE [ApplicationDB];
    
    SELECT 
        @@SERVERNAME AS [ServerName],
        @@SERVICENAME AS [Sql Instance],
        SUSER_SNAME() AS [Login - Connection],
        SUSER_NAME() AS [Login - Running as],
        USER_NAME() AS [User - Running as];
    
    EXECUTE AS LOGIN = N'foo';
    SELECT 
        @@SERVERNAME AS [ServerName],
        @@SERVICENAME AS [Sql Instance],
        SUSER_SNAME() AS [Login - Connection],
        SUSER_NAME() AS [Login - Running as],
        USER_NAME() AS [User - Running as];
    REVERT;
    GO
    
  2. Double check db owner

    SELECT
      name
     ,SUSER_SNAME(D.owner_sid)
    FROM sys.databases AS D
    WHERE
      D.database_id = DB_ID()
    
  3. Check effective permissions

    EXECUTE AS LOGIN = N'foo';
    SELECT 
        N'Database' AS [Scope Class],
        DB_NAME() AS [Scope Name],
        permission_name AS [Effective Permission in Scope],
        fbp.type,
        fbp.covering_permission_name
    FROM fn_builtin_permissions(N'DATABASE') AS fbp
    WHERE
        HAS_PERMS_BY_NAME(N'[PermissionsTest]', N'DATABASE',fbp.permission_name)<>0
    UNION ALL
    SELECT
        N'SCHEMA',
        s.name,
        permission_name AS [Effective Permission in Scope],
        fbp.type,
        fbp.covering_permission_name
    FROM sys.fn_builtin_permissions(N'SCHEMA') AS fbp
    CROSS JOIN sys.schemas AS S
    WHERE
        HAS_PERMS_BY_NAME(s.name, N'SCHEMA', fbp.permission_name) <> 0
        -- remove system schemas
        AND s.name NOT IN ('db_owner','db_accessadmin','db_securityadmin',
                            'db_ddladmin','db_backupoperator','db_datareader',
                            'db_datawriter','db_denydatareader', 'db_denydatawriter',
                            'INFORMATION_SCHEMA','sys', 'guest' /*,'dbo' */ ) 
    REVERT;
    
  4. list assigned permissions

    SELECT
        DP.class_desc AS [Scope Class],
        CASE 
            WHEN class = 0 THEN 
                DB_NAME()
            WHEN class = 3 THEN
                SCHEMA_NAME(DP.major_id)
            WHEN minor_id = 0 THEN 
                OBJECT_NAME(major_id,DB_ID())
            ELSE 
                '<ignored>'
        END  AS [Scope Name],
        USER_NAME(COALESCE(DRM.role_principal_id,DP.grantee_principal_id)) AS grantee ,
        DP.permission_name AS [permission affected],
        DP.state_desc
    FROM sys.database_permissions AS DP
    LEFT JOIN sys.database_role_members AS DRM ON DP.grantee_principal_id = DRM.role_principal_id
    WHERE
        DP.grantee_principal_id <> 0 AND
        (
            DP.grantee_principal_id = USER_ID('foo') OR
            DRM.member_principal_id = USER_ID('foo') 
        )
    

    where foo is the user of interest

  • I've logged in as the sqlserver user that the apposes and yes username is returning the correct name. Query number 2 came back as a blank set and query number 3 gives me the name of one of our developers as the owner. Not the user that I'm troubleshooting. – Lumpy Apr 25 '14 at 12:22
  • Curious, did you run query 2 and 3 as the applications user? If so, please try the queries again as an admin. And You are not using "application roles" or impersonations? – Matt Apr 25 '14 at 13:26
  • Not using impersonation, No application roles defined. I ran both queries as sysadmin account and got the same results. – Lumpy Apr 25 '14 at 13:30
  • Let me check my assumptions the application account can create both tables create table dbo.myTable(id int ) and temp tables create table #myTable(id int ). Did you inspect public server role and public database role for any unwanted permissions? – Matt Apr 25 '14 at 13:56
  • the user can create tables and temp tables yes. I'm not sure about the rights associated with the public role. I'll need to look them up. – Lumpy Apr 25 '14 at 14:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.