Learn more about SQL Server tools

 
 

Tutorials          DBA          Dev          BI          Career          Categories          Events          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Events

DBA    Dev    BI    Categories

 

Giving and removing permissions in SQL Server


By:   |   Read Comments (10)   |   Related Tips: More > 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



Last Update:





About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips
Related Resources





More SQL Server Solutions




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



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

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 Back To Top

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 06, 2013 - 2:12:55 PM - mohan Back To Top

 

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 06, 2012 - 4:23:08 AM - Dineshbabu Back To Top

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 Back To Top

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';

You can read more about this in this tip: http://www.mssqltips.com/sqlservertip/1782/understanding-cross-database-ownership-chaining-in-sql-server/


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

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 Back To Top

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 Back To Top

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 Back To Top

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 Back To Top

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


Learn more about SQL Server tools