April 15, 2010 at 2:14 pm
When I run this stored procedure:
USE [EliteBuild]
GO
/****** Object: StoredProcedure [dbo].[sp_GrantProcurementPermissions] Script Date: 04/15/2010 13:15:09 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_GrantProcurementPermissions]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_GrantProcurementPermissions]
GO
USE [EliteBuild]
GO
/****** Object: StoredProcedure [dbo].[sp_GrantProcurementPermissions] Script Date: 04/15/2010 13:15:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE procedure [dbo].[sp_GrantProcurementPermissions]
As
/*
Purpose: Grant access to the Procurement db to all db logins in osCompanyWorker (plus procurementuser)
Created: 11-24-2008
*/
Declare @EliteDB varchar(255)
Set @EliteDB = db_name()
declare @dbname nvarchar(255),
@ProcurementDB nvarchar(255),
@sql nvarchar(4000),
@params nvarchar(4000)
Declare dbCursor Cursor For
select name from master.dbo.sysdatabases
open dbCursor
fetch next from dbCursor into @dbname
while (@@Fetch_Status) = 0
begin
set @ProcurementDB = ''
set @sql = ' use ' + @dbname +
' select @pdbname = ''' + @dbname + ''' from information_schema.columns ' +
' where table_name = ''vapievents'' ' +
' and column_name = ''callRef'' '
set @params=N'@pdbname varchar(255) output'
exec sp_executesql @sql, @params, @pdbname = @ProcurementDB output
if @ProcurementDB <> ''
begin
Set @sql = 'use ' + @ProcurementDB + ' ' +
'declare @User varchar(30) Declare CursorSQL Cursor for Select RTRIM(MLSLogin) + ''_'' +
RTRIM(CAST(PK AS char(9))) AS Name from ' + rtrim(@EliteDB) + '.dbo.osCompanyWorker ' +
' union select ''procurementuser'' as name ' +
' Open CursorSQL ' +
' Fetch Next From CursorSQL Into @User '+
' While (@@Fetch_Status) = 0 ' +
' Begin ' +
' If @@Version like ''Microsoft SQL Server 200[58]%'' Begin ' +
' If exists (select name from master.sys.sql_logins where Name=@User) Begin ' +
' If exists (Select name from SysUsers where Name=@User) begin Exec sp_revokedbaccess @User end ' +
' Exec sp_grantdbaccess @User ' +
' Exec sp_addrolemember ''db_datareader'', @User' +
' Exec sp_addrolemember ''db_datawriter'', @User' +
' End ' +
' End ' +
' Else Begin ' +
' If exists (select name from master.dbo.sysxlogins where Name=@User) Begin ' +
' If exists (Select name from SysUsers where Name=@User) begin Exec sp_revokedbaccess @User end ' +
' Exec sp_grantdbaccess @User ' +
' Exec sp_addrolemember ''db_datareader'', @User' +
' Exec sp_addrolemember ''db_datawriter'', @User' +
' End ' +
' End ' +
' Fetch Next From CursorSQL Into @User ' +
' End ' +
' Deallocate CursorSQL '
Exec(@sql)
end
fetch next from dbCursor into @dbname
end
deallocate dbCursor
return
GO
*******************************************************
I receive the following error:
Msg 208, Level 16, State 1, Line 1
Invalid object name 'information_schema.columns'.
If I look in Views System Views I can see INFORMATION_SCHEMA.COLUMNS. I am
able to query againt the view without issue.
I did not write this stored procedure and it is being called from a larger procedure, which fixes logins for the applilcations databases. I am attempting to create a test environment and as far as I know this procedure runs correctly in the production environment. The bolded section of the sp is where I believe it is failing.
Has anyone run into this sort of thing before? any help you can give will be greatly appreciated.
Ken
April 15, 2010 at 3:18 pm
What is the Server/Database Collation? It could be a case sensitive one?
April 15, 2010 at 4:02 pm
thanks for the reply,
The collation is: SQL_Latin1_General_CP1_CI_AS
I will have to look for a case issue...
April 15, 2010 at 4:45 pm
kwitzell (4/15/2010)
thanks for the reply,The collation is: SQL_Latin1_General_CP1_CI_AS
I will have to look for a case issue...
The CI stands for CaseInsensitive. Otherwise you'd have a CS collation. No need for further investigation on this subject... 😉
I just tried to run the related section of the proc on a new db on my sandbox (set to SQL_Latin1_General_CP1_CI_AS collation). It did run just fine.
What permissions are used to run this proc? Permission issues is left of what I can think of as possible reasons for failure... Can you run the first part of the proc (down to "if @ProcurementDB <> '' ") as a separate query in SSMS using the same account that can query INFORMATION_SCHEMA.COLUMNS?
April 16, 2010 at 3:51 pm
We were running this as SA, in an effort to eliminate permissions issues. I did find something new, in the code the bolded statement makes a reference to a table "vapievents". I serached the database and master for that table and could not find it, so I am thinking this might be the problem. We have sent the script back to the vendor asking for and explanation. Thanks for all your help!
Ken
April 16, 2010 at 5:52 pm
Thank you for the update.
It's always nice to have someone posting an update to his question even though the issue is not completely resolved (yet).
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply