GRANT PERMISSION TO ALL OBJECTS ON A DATABASE

How do I write T-SQL command to grant a permission to ALL objects in a
database.
Thanks.
EsmeraldaEsmeralda,
What the permission do you want ?
I send for you one sample for this case:
if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[sp_GrantExec]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_GrantExec]
GO
CREATE PROCEDURE sp_GrantExec (@.Username VARCHAR(256))
/* Funcionalidade: Concede permissão de EXEC para o usuário especificado
Compatível com: SQL Server 7 e 2000
Desenvolvido por: Rodrigo Fernandes
Data: 29/12/2004 */
AS
-- CHECK PERMISSIONS: Because changing owner changes both schema and
-- permissions, the caller must be one of:
-- (1) db_owner
-- (2) db_ddladmin AND db_securityadmin
IF (IS_MEMBER('db_owner') = 0) AND
(IS_MEMBER('db_securityadmin') = 0 OR IS_MEMBER('db_ddladmin') = 0)
BEGIN
RAISERROR(15247,-1,-1)
RETURN(1)
END
IF NOT EXISTS (SELECT name FROM sysusers WHERE name = @.Username)
BEGIN
PRINT 'THE USER DOES NOT EXIST IN DATABASE !'
RETURN(1)
END
ELSE
BEGIN
DECLARE @.Granth VARCHAR(8000)
DECLARE @.Objname SYSNAME
DECLARE Objname_csr CURSOR FOR
SELECT name FROM sysobjects
WHERE xtype IN ('P', 'FN')
AND category = 0
AND name NOT LIKE 'dt_%'
ORDER BY name
OPEN Objname_csr
FETCH NEXT FROM Objname_csr INTO @.Objname
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.Granth = 'GRANT EXEC ON ' + @.Objname + ' TO ' + @.Username
EXEC (@.Granth)
PRINT @.Granth
FETCH NEXT FROM Objname_csr INTO @.Objname
END
CLOSE Objname_csr
DEALLOCATE Objname_csr
RETURN(0)
END
** * Esta msg foi útil pra você ? Então marque-a como tal. ***
Regards,
Rodrigo Fernandes
"LaEsmeralda" wrote:
> How do I write T-SQL command to grant a permission to ALL objects in a
> database.
> Thanks.
> Esmeralda|||Please specify version. If on 2005, you can do:
GRANT SELECT ON DATABASE::dbname TO username.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"LaEsmeralda" <LaEsmeralda@.discussions.microsoft.com> wrote in message
news:A5ED3CAB-00E4-4F6C-9546-BB70B842384A@.microsoft.com...
> How do I write T-SQL command to grant a permission to ALL objects in a
> database.
> Thanks.
> Esmeralda