Re: User permissions

From: Dan Guzman (danguzman@nospam-earthlink.net)
Date: 11/11/02 

From: "Dan Guzman" <danguzman@nospam-earthlink.net>
Date: Mon, 11 Nov 2002 09:17:05 -0600

A script like the one below will do this:

DECLARE @UserName sysname
SET @UserName = 'MyRole'

DECLARE @GrantStatement nvarchar(4000)
DECLARE GrantStatements CURSOR LOCAL FAST_FORWARD
FOR
SELECT
    N'GRANT ALL ON ' +
    QUOTENAME(USER_NAME(uid)) + N'.' +
    QUOTENAME(name) +
    N' TO ' + @UserName
FROM sysobjects
WHERE OBJECTPROPERTY(id, 'IsMSShipped') = 0 AND
    (OBJECTPROPERTY(id, 'IsUserTable') = 1 OR
    OBJECTPROPERTY(id, 'IsProcedure') = 1 OR
    OBJECTPROPERTY(id, 'IsInlineFunction') = 1 OR
    OBJECTPROPERTY(id, 'IsScalarFunction') = 1 OR
    OBJECTPROPERTY(id, 'IsTableFunction') = 1 OR
    OBJECTPROPERTY(id, 'IsView') = 1)
OPEN GrantStatements
WHILE 1 = 1
BEGIN
    FETCH NEXT FROM GrantStatements INTO @GrantStatement
    IF @@FETCH_STATUS <> 0 BREAK
    RAISERROR (@GrantStatement, 0, 1) WITH NOWAIT
    EXEC(@GrantStatement)
END
CLOSE GrantStatements 

-- 
Hope this helps.
Dan Guzman
SQL Server MVP
-----------------------
SQL FAQ links (courtesy  Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
-----------------------
"Chris Themistocli" <cthemistocli@aw.com.cy> wrote in message
news:122d01c2898c$8006c7f0$8cf82ecf@TK2MSFTNGXA07...
> Is there a transact-sql or other way that I can use to
> grant permissions to all objects in a database to a
> specific user? The only way I could find is to go through
> all objects (tables, stored proc and views) by using the
> Enterprise Manager.

Relevant Pages