撤消授予用户访问数据库的权限

Question

我正在构建一个web应用程序，其中可以管理用户对多个数据库的访问。我使用以下代码为用户提供对数据库的读取访问权限：

    public void giveUserReadRightsForDatabase(int serverID, string dbName, string dbUsername, string myPassword)
    {
        ISFramework.Database myDB = new ISFramework.Database();
        string myQuery = "IF NOT EXISTS (SELECT name FROM sys.sql_logins WHERE name ='" + dbUsername + "')" + Environment.NewLine +
                            "BEGIN" + Environment.NewLine +
                            "CREATE LOGIN " + dbUsername + " WITH PASSWORD = '" + myPassword +
 "', CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF" + Environment.NewLine +
                            "END" + Environment.NewLine +
                            "BEGIN TRY" + Environment.NewLine +
                            "USE [" + dbName + "]" + Environment.NewLine +
                            "CREATE USER " + dbUsername + " FOR LOGIN " + dbUsername + Environment.NewLine +
                            "USE [" + dbName + "]" + Environment.NewLine +
                            "ALTER ROLE db_datareader ADD MEMBER " + dbUsername + Environment.NewLine +
                            "END TRY" + Environment.NewLine +
                            "BEGIN CATCH" + Environment.NewLine +
                            "END CATCH";
        myDB.executeNonQuery("DBServer" + serverID, myQuery);
    }

我使用以下代码来删除权限：

    public void removePermissionsForThisDatabase(int serverID, string dbUsername, string dbName)
    {
        ISFramework.Database myDB = new ISFramework.Database();
        string myQuery = "BEGIN TRY" + Environment.NewLine +
                            "USE [" + dbName + "]" + Environment.NewLine +
                            "CREATE USER " + dbUsername + " FOR LOGIN " + dbUsername + Environment.NewLine +
                            "USE [" + dbName + "]" + Environment.NewLine +
                            "ALTER ROLE db_datareader DROP MEMBER " + dbUsername + Environment.NewLine +
                            "USE [" + dbName + "]" + Environment.NewLine +
                            "ALTER ROLE db_datawriter DROP MEMBER " + dbUsername + Environment.NewLine +
                            "USE [" + dbName + "]" + Environment.NewLine +
                            "IF  EXISTS (SELECT * FROM sys.sql_logins WHERE name = N'" + dbUsername + "')" + Environment.NewLine +
                            "DROP USER " + dbUsername + Environment.NewLine +
                            "IF  EXISTS (SELECT * FROM sys.database_principals WHERE name = N'" + dbUsername + "')" + Environment.NewLine +
                            "DROP LOGIN " + dbUsername + Environment.NewLine +
                            "END TRY" + Environment.NewLine +
                            "BEGIN CATCH" + Environment.NewLine +
                            "END CATCH";
        myDB.executeNonQuery("DBServer" + serverID, myQuery);
    }

在第一种方法结束时，用户可以访问和读取数据库，这很好。尽管当我使用第二种方法时，用户仍然可以访问数据库，这是不好的。我已经调试并确认程序正在调用方法并执行查询，所以查询本身肯定有错误。

我试过在SQL Server Management Studio中做以下几行，它确实完成了它的工作，所以我真的不知道，说实话。从来没有错误消息，但代码并不是应该的。

 USE [Dev_ISFramework]
 IF  EXISTS (SELECT * FROM sys.sql_logins WHERE name = N'Johnny456')
 DROP USER Johnny456

Answer 1

我不是这里的专家，但如果您的第二个方法在SQL开始时使用BEGIN TRY，那么尝试在用户存在时创建用户会引发一个异常，该异常会被静默捕获。所以你的角色转换没有执行。

您必须重新访问removePermissionsForThisDatabase()中使用的整个SQL代码才能执行您想要的操作。