Giving and removing permissions in SQL Server

By: Greg Robidoux | Comments (12) | Related: > Security

Problem

Often times when auditing SQL Servers I notice that most environments use the default database roles to grant access to users. These default roles such as db_owner, db_datareader and db_datawriter are great, but often times they give the users a lot more access then they really need. Giving permissions and removing permissions is not that hard to do within SQL Server, it just takes some time to determine what permissions should be applied.

Solution

SQL Server offers three pretty simple commands to give and remove access, these commands are:

GRANT - gives a user permission to perform certain tasks on database objects
DENY - denies any access to a user to perform certain tasks on database objects
REVOKE - removes a grant or deny permission from a user on certain database objects

Here are some examples of these commands.

Allow users Joe and Mary to SELECT, INSERT and UPDATE data in table Customers

GRANT INSERT, UPDATE, SELECT ON Customers TO Joe, Mary

Revoke UPDATE access to table Customers for user Joe

REVOKE UPDATE ON Customers to Joe

DENY DELETE access to table Customers for user Joe and Mary

DENY DELETE ON Customers to Joe, Mary

As you can see from the above examples it is pretty easy to grant, deny and revoke access. In addition to grant SELECT, INSERT, DELETE and UPDATE rights you can also grant EXECUTE rights to run a stored procedure as follows:

GRANT EXEC ON uspInsertCustomers TO Joe

To determine what rights have been granted in a database use the sp_helprotect stored procedure.

In addition to granting rights to objects that you create you can also grant users permissions to do other tasks such as create tables, views, stored procedures, etc... To grant a user permissions to create a table you would run this command.

GRANT CREATE TABLE TO Joe

As you can see granting rights and permissions to certain features is not all that difficult to do. Take the time to understand what permissions are really needed by the database users and grant, deny and revoke accordingly instead of just using the default database roles.

Next Steps

Take a look at this tip on how to determine what permissions have been granted Auditing your SQL Server database and server permissions
Take a look at these commands for other examples
- GRANT
- DENY
- REVOKE
- sp_helprotect

About the author

Greg Robidoux is the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server. He is also one of the co-founders of MSSQLTips.com. Greg has been working with SQL Server since 1999, has authored numerous database-related articles, and delivered several presentations related to SQL Server. Before SQL Server, he worked on many data platforms such as DB2, Oracle, Sybase, and Informix.

View all my tips

Comments For This Article

Tuesday, July 16, 2019 - 9:34:32 AM - Greg Robidoux

Hi Shakib, it looks like you can use either TO or FROM to revoke permissions.

-Greg

Thursday, July 11, 2019 - 3:40:01 PM - shakib

REVOKE UPDATE ON Customers to Joe

here "to" is incorrect ."from" is correct. it would be :

REVOKE UPDATE ON Customers from Joe

thanks :)

Thursday, December 10, 2015 - 6:56:40 AM - Anand Kumar Singh

I have a query related to sql server security point of view. I just give brief of my situation, I am running SQL server 2014 r2 in that around 8 database was created, it was running with no problem since 2 years, but recently i m getting issue like suddenly my entire database say any one complete database got deleted that means all tables of that database became blank. When i started "SQL server Profiler" to record session, i saw one query is running like Select name from sys.tables and then one by one all table name of that database with the statement like-- delete from employee and so on.. i could not able to get the information why and from where this query is running.

DO you have any idea, how this cause is keep on coming from past 2 weeks.

please reply ASAP.

Please Note:- I am hosting one internal website in IIS server with windows server 2008 and sql server 2014(last 1 year or so) currently and 2008 earlier

Sunday, May 31, 2015 - 11:39:41 PM - VIJAY PATEL

By mistake I execute scipt in wrong DB and I was able to delete all table but not able to delete two tables which has Foreign key constraint and I have delete constraint and try to delete the table unsuccessfully can you hale.

Thank you very much.

VIjay

Monday, May 6, 2013 - 2:12:55 PM - mohan

when i run insert,update or delete a query in a trigger in which i used linked server in sql server 2005, i m getting an error which is mentioned below

OLE DB provider "SQLNCLI" for linked server "218" returned message "No transaction is active.".

Msg 7391, Level 16, State 2, Procedure a1_Insert, Line 7

The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "218" was unable to begin a distributed transaction.

i have checked with the MSDTC options, hope every options where given correctly.

Please guide me to rectify this error.

Thursday, December 6, 2012 - 4:23:08 AM - Dineshbabu

I have created procedure as below

CREATE PROC pmsuser.Test124 --both schema name and user name is PMSUSER
AS
BEGIN
    UPDATE dbo.table1
    SET    CTC = 99955
    WHERE ID = 3
END

Then i have denied update permission for pmsuser --user

DENY

UPDATEONOBJECT::DBO.Table1 TO PMSUSEReventhough i'm getting the below error

Msg 229, Level 14, State 5, Procedure test124, Line 4
The UPDATE permission was denied on the object 'Table1', database 'DevDB', schema 'dbo'.

Friday, September 28, 2012 - 4:28:14 PM - Greg Robidoux

Hi Susan,

the issue you are having is related to having the stored procedure in one database and having it update tables in another database. The simple solution would be to create the stored procedure in the same database and GRANT EXEC permissions on that stored procedure for the users that need to run the stored procedure.

If you are unable to do that the other option is to enable database chaining for both of the databases. This can be done with this command that needs to be run in both databases. You will need to change DBName for the actual names of the databases.

EXEC sp_dboption 'DBName', 'db_chaining', 'true';

Thursday, September 27, 2012 - 4:35:21 PM - Susan Pace

USE [RobotSQL]
GO
/****** Object: StoredProcedure [TechSupp].[usp_UpdatePhysician] Script Date: 09/27/2012 16:38:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author: Susan Pace
-- Create date: 08/21/2012
-- Description: Allow Tech Supoort to update the Physician Table
-- =============================================

ALTER PROCEDURE [TechSupp].[usp_UpdatePhysician]
--params
    (@p_id    VARCHAR(10),
     @login_name  VARCHAR(10),
     @p_username  VARCHAR(50),
     @P_Name   VARCHAR(30)= '')
AS
BEGIN
SET NOCOUNT ON;
--Begin Work
BEGIN TRY

  BEGIN TRANSACTION

  IF @P_Name = '' OR @p_name IS NULL
   BEGIN
    UPDATE Labdata.dbo.Physician
     SET login_Name = @login_name,
      p_username = @p_username
    FROM Labdata.dbo.Physician
    WHERE p_id =@p_id
   END
  ELSE
   BEGIN
    UPDATE Labdata.dbo.Physician
     SET login_Name = @login_name,
      p_username = @p_username,
      p_name = @P_Name
    FROM Labdata.dbo.Physician
    WHERE p_id =@p_id
   END

END TRY
--Catch errors and return to user
BEGIN CATCH
  IF @@TRANCOUNT > 0
  BEGIN
   ROLLBACK TRANSACTION
  END
   DECLARE @ErrMsg nvarchar ( 4000 ), @ErrSeverity int
   SELECT @ErrMsg =   ERROR_MESSAGE (), @ErrSeverity =   ERROR_SEVERITY ()
   RAISERROR ( @ErrMsg , @ErrSeverity , 16 )
   RETURN
END CATCH
--Return Scope ID
IF @@TRANCOUNT > 0
BEGIN
  --Commit Tran if everthing is okay
COMMIT TRANSACTION
END
END

Thursday, September 27, 2012 - 3:59:43 PM - Greg Robidoux

Susan - is the above code the code that gives the user this error: Update permissions was denied on the object XXX,database XXX, Schema 'dbo'

or is there another set of code that gives them the error?

Thursday, September 27, 2012 - 1:18:59 PM - Susan Pace

SET STATISTICS IO OFF
SET STATISTICS TIME OFF
SET NOCOUNT ON

DECLARE @UserString   VARCHAR(8000)
       ,@DatabaseString  VARCHAR(8000)
       ,@DefaultDatabase VARCHAR(255)
       ,@password   VARCHAR(20)
       ,@RolesString  VARCHAR(8000)
       ,@delimiter   CHAR(1)
       ,@Environment  VARCHAR(20)
       ,@Instance           TINYINT
       ,@DatabaseRole  VARCHAR(20)
       ,@intFlag   INT
       ,@recordcount  INT

SET @intFlag = 1
SET @recordcount = 1

WHILE (@intFlag <=1)--@recordcount)
BEGIN

-- Change this section per user
SET @UserString = (SELECT [User] FROM tmpdata.dbo.Security WHERE @intFlag = s_ID)
SET @Environment = (SELECT @@SERVERNAME)
SET @Instance = 1
SET @DatabaseRole = (SELECT DatabaseRole FROM tmpdata.dbo.Security WHERE @intFlag = s_ID)

IF @Environment = 'DEVELOPMENT' AND @Instance = 1
BEGIN
SET @DatabaseString = 'ArchiveData,DevTrack,ESRD,HealthSQL,MedLex,MedScribeSQL,PatientData,POCForms,RobotSQL,TmpData,Treatment,TrigLogs,zCol,zHealthSQL'
END

IF @Environment = 'DEVELOPMENT' AND @Instance = 2
BEGIN
SET @DatabaseString = 'CKDWarehouse,CustAcct,DZMGmnt,DevTrack,EDIData_ghn7,edidata_old,edidata_sql3,GHNDictionary,GHNKBase,GHNLogs,GHNSchedule,ICD9,interfaces_sql3,LabData,LabDataMU,MGCPGSQL,RealmDB,RobotBM,RobotSQL,Rx,Tmpdata,TrigLogs'
END

SET @DefaultDatabase = 'master'

/*Grant View Definition to user*/
  DECLARE @SQL2 varchar(max)
  SET @SQL2 = 'USE ' + @DefaultDatabase + '; GRANT VIEW ANY DEFINITION TO ' + @UserString
  PRINT (@SQL2)
  EXECUTE (@SQL2)

IF @DatabaseRole IN ('QA','BA')
BEGIN
SET @RolesString = 'db_datareader'
END

IF @DatabaseRole = 'Superuser'
BEGIN
   SET @RolesString = 'db_datareader,db_datawriter,db_superuser'
END

IF @DatabaseRole = 'WebDev'
BEGIN
   SET @RolesString = 'db_datareader,db_developer'
END

IF @DatabaseRole = 'TechSupport'
BEGIN
   SET @RolesString = 'db_datareader,db_techsupport'
END

SET @delimiter = ','
BEGIN TRY
    DROP TABLE #Users
    DROP TABLE #Databases
    DROP TABLE #Roles
END TRY
BEGIN CATCH

END CATCH

    ;WITH Substr(num, firstchar, lastchar) AS (
      SELECT 1, 1, CHARINDEX(@delimiter, @UserString)
      UNION ALL
      SELECT num + 1, lastchar + 1, CHARINDEX(@delimiter, @UserString, lastchar + 1)
      FROM Substr
      WHERE lastchar > 0
    )
    SELECT
        num,
        UserName = SUBSTRING(@UserString, firstchar, CASE WHEN lastchar > 0 THEN lastchar-firstchar ELSE 8000 END)
    INTO #Users
    FROM Substr

    ;WITH Substr(num, firstchar, lastchar) AS (
      SELECT 1, 1, CHARINDEX(@delimiter, @DatabaseString)
      UNION ALL
      SELECT num + 1, lastchar + 1, CHARINDEX(@delimiter, @DatabaseString, lastchar + 1)
      FROM Substr
      WHERE lastchar > 0
    )
    SELECT
        num,
        DatabaseName = SUBSTRING(@DatabaseString, firstchar, CASE WHEN lastchar > 0 THEN lastchar-firstchar ELSE 8000 END)
    INTO #Databases
    FROM Substr

    ;WITH Substr(num, firstchar, lastchar) AS (
      SELECT 1, 1, CHARINDEX(@delimiter, @RolesString)
      UNION ALL
      SELECT num + 1, lastchar + 1, CHARINDEX(@delimiter, @RolesString, lastchar + 1)
      FROM Substr
      WHERE lastchar > 0
    )
    SELECT
        num,
        RoleName = SUBSTRING(@RolesString, firstchar, CASE WHEN lastchar > 0 THEN lastchar-firstchar ELSE 8000 END)
    INTO #Roles
    FROM Substr

DECLARE @NumUsers int
DECLARE @NumDBs int
DECLARE @NumRoles int
DECLARE @UserIter int
DECLARE @DBIter int
DECLARE @RoleIter int
DECLARE @UserName varchar(255)
DECLARE @RoleUserName varchar(255)
DECLARE @DBName varchar(255)
DECLARE @RoleName varchar(255)
DECLARE @SQL varchar(max)

SET @NumUsers   = (SELECT MAX(num) FROM #Users)
SET @NumDBs     = (SELECT MAX(num) FROM #Databases)
SET @NumRoles   = (SELECT MAX(num) FROM #Roles)
SET @UserIter   = 1
SET @SQL        = ''

WHILE @UserIter <= @NumUsers
BEGIN
    SET @DBIter     = 1
    SET @RoleIter   = 1
    SET @UserName = (SELECT UserName FROM #Users WHERE num = @UserIter)

    SET @UserIter = @UserIter + 1

    -- Add user to the databases
    WHILE @DBIter <= @NumDBs
    BEGIN
        SET @DBName = (SELECT DatabaseName FROM #Databases WHERE num = @DBIter)

        SET @SQL = 'USE ' + @DBName + '; DROP USER ' + @UserName + ' ;'
        PRINT (@SQL)
        EXECUTE (@SQL)

        SET @SQL = 'USE ' + @DBName + '; CREATE USER ' + @UserName + ' FOR LOGIN ' + @UserName

        PRINT (@SQL)
        EXECUTE (@SQL)

        /* Grant Show Plan to WebDev, TechSupport and SuperUser*/
        IF @DatabaseRole IN ('Superuser','WebDev','TechSupport')
   BEGIN
    SET @SQL = 'USE ' + @DBName + '; GRANT SHOWPLAN TO ' + @UserName + ' ;'
    PRINT (@SQL)
    EXECUTE (@SQL)
   END

        SET @DBIter = @DBIter + 1
        SET @RoleIter = 1

WHILE @RoleIter <= @NumRoles
BEGIN

SET @RoleName = (SELECT RoleName FROM #Roles WHERE num = @RoleIter)

-- Must remove brackets for addrolemember procedure

            SET @RoleUserName = REPLACE(REPLACE(@UserName, '[', ''), ']', '')
            SET @SQL = 'USE ' + @DBName + '; EXEC sp_addrolemember ''' + @RoleName + ''', ''' + @RoleUserName + ''''
            EXECUTE (@SQL)

            IF @DBName = 'RobotSQL' AND
    @UserName IN ('CFAdmin','DrFirst','HL7Link')
    AND @RoleIter = 1
    BEGIN
     SET @SQL = 'USE ' + @DBName + '; REVOKE EXECUTE ON SCHEMA ::TechSupp TO ' + @UserName
     EXECUTE (@SQL)
   END


            IF @DBName ='MGCPGSQL' AND @RoleName = 'db_developer'
              BEGIN
                PRINT '--No role for db_developer in MGCPGSQL.'
              END
            ELSE
              BEGIN
                PRINT (@SQL)
              END

            SET @RoleIter = @RoleIter + 1
        END
    END
END

SET @intFlag = @intFlag + 1

END

Thursday, September 27, 2012 - 12:35:16 PM - Greg Robidoux

This should work by creating the stored procedure and granting them EXEC on the stored procedure. You shouldn't need to give them direct access to the tables.

Is it possible to share the code you are using?

Thursday, September 27, 2012 - 12:21:47 PM - Susan Pace

Greg,

I have read alot of article and searched but I am having a hard time find exactly what I am looking for. Instead of using Windows accounts we are using SQL accounts. Each user has an account and our application has an account. I have techsupport who know how to write SQL statements and sometime just goes and open tables up to correct data or insert data and I want to stop this kind of practice. We have created proc for them to do these kind of update or insert instatements using parameters. They have been granted execute to the schema dbo but when they try to execute the proc they get an error Update permissions was denied on the object XXX,database XXX, Schema 'dbo' . I do not want to give the db_datawriter because that defeats the purpose. I am at a loss. Any suggestions?

Sincerely,

Susan Pace