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

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?

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

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?

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

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/

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

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.