Thanks Yelena, you're a star!

As you rightly say, you can't assign permissions on an object which belongs to a schema owned by the same role/user.

Permissions in SQL Server 7 and 2000...

Thanks Yelena, at least I'm not going mad!

It seemed so easy in SQL Server 2000.  We're obviously missing something simple...

Regards,

Barry

I'm working in SQL Server 2005.  I don't know if you are using an earlier version.  If you are the system tables/available functions may be different.

Sorry I can't be of...

Will the following do the job:

SELECT SCHEMA_NAME(o.schema_id) As SchemaName

  , o.Name As TableName

  , c.Name As ColumnName

FROM sys.objects o INNER JOIN sys.columns c ON o.object_id = c.object_id

  INNER JOIN...

That's what I thought.  Just seems a bit clunky.

Thanks.

Thank you Kenneth, those links were spot on, particularly the first one!

Barry

Thanks Yukas, thought that was probably the case, just wanted to be sure I wasn't missing a trick!

Regards,

Barry

Thank you all for those suggestions, it sounds very promising.

Barry

I use the following code for finding strings in sps:

SELECT DISTINCT a.name AS SPName

FROM syscomments b, sysobjects a

WHERE b.text LIKE '%authors%'

AND a.id=b.id

AND a.type='P'

Would the following...

Already doing that.

Slight variation using a query and tab as field separator:

DECLARE @cmd varchar(1000)

, @sql varchar(1000)

SET @sql = 'SELECT Field1, Field2'

SET @sql = @sql...

See my thread 'Exporting to a text file from a stored procedure', in the T-SQL forum, 19-9-03.

Thank you David for both your expertise and the speed of your responses.

Thanks David, that was just the job!

However, after I get the sp working, I tried to be clever (always dangerous) and wanted to put my rather long query into a...

Thanks Gary, that's spot on!!