Revoking permission to view SQL Stored procs

Hi
Is there a way which I can revoke a users ability to view SQL stored procs,
but still have the ability to execute?
I want to do this via T-SQL, and without just considering the 'WITH
ENCRYPTION' option
Thanks
hi Paul,
Paul Aspinall wrote:
> Hi
> Is there a way which I can revoke a users ability to view SQL stored
> procs, but still have the ability to execute?
> I want to do this via T-SQL, and without just considering the 'WITH
> ENCRYPTION' option
> Thanks
every database user is allowed to view the definition of stored procedures,
and you can not change this behaviour... unfortunately, as you already
pointed out, you can only consider the WITH ENCRYPTION option
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||What about granting only EXECUTE permissions and not CREATE, ALTER or
DROP ?
|||hi,
bd wrote:
> What about granting only EXECUTE permissions and not CREATE, ALTER or
> DROP ?
every database user has implicit permission to script objects, so he/she
will be able to access the object definition and DDL
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

returning one single record rather than multiples

Hi

Is it possible to return the results of a query so that instead of
having say 10 rows its concatenated, eg

My query returns 'M' 10 times, can this be returned as 'M M M M M M M
M M M'?

Thanks
LeeLee (lee@.digital-interactive.com) writes:

Quote:

Originally Posted by

Is it possible to return the results of a query so that instead of
having say 10 rows its concatenated, eg
>
My query returns 'M' 10 times, can this be returned as 'M M M M M M M
M M M'?

In SQL 2005:

SELECT subtring(Ms, 1, datalength(Ms) / 2 - 1
FROM (SELECT col + ' ' AS [text()]
FROM tbl
FOR XML PATH('')) AS T(Ms)

This makes use of the XML functionality, and has the drawback that some
characters will be encoded.

In SQL 2000, you are unfortunately best of with a cursor.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||hi

I've just tried that (in SQL 2005)

SELECT subtring(Ms, 1, datalength(Ms) / 2 - 1
FROM (SELECT [company size] + ' ' AS [text()]
FROM results
FOR XML PATH('')) AS T(Ms)

and its returns errors, is that a working example?

Thanks
Lee|||The example from Erland is good, he was just sketching quickly to give you
an idea of how to solve your problem (and something to work on, since there
was no DDL posted). Here is a copy and paste extension to that:

CREATE TABLE tbl (col char(1))

INSERT INTO tbl VALUES ('M')
INSERT INTO tbl VALUES ('M')
INSERT INTO tbl VALUES ('M')
INSERT INTO tbl VALUES ('M')
INSERT INTO tbl VALUES ('M')

SELECT substring(Ms, 1, datalength(Ms) / 2 - 1)
FROM (SELECT col + ' ' AS [text()]
FROM tbl
FOR XML PATH('')) AS T(Ms)

DROP TABLE tbl

HTH,

Plamen Ratchev
http://www.SQLStudio.com