Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips


























































   Got a SQL tip?
            We want to know!

Giving and removing permissions in SQL Server

MSSQLTips author Greg Robidoux By:   |   Read Comments (8)   |   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: 12/19/2006


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


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Comments and Feedback:
Thursday, September 27, 2012 - 12:21:47 PM - Susan Pace Read The Tip

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


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

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 - 1:18:59 PM - Susan Pace Read The Tip

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 - 3:59:43 PM - Greg Robidoux Read The Tip

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 - 4:35:21 PM - Susan Pace Read The Tip

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


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

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, December 06, 2012 - 4:23:08 AM - Dineshbabu Read The Tip

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


Monday, May 06, 2013 - 2:12:55 PM - mohan Read The Tip

 

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.



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.