Re: GRANT all on all to User ????

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

From: "Dan Guzman" <danguzman@nospam-earthlink.net>
Date: Wed, 19 Jun 2002 07:27:30 -0500

The GRANT statement syntax doesn't accept wildcard characters. However,
you can execute mass permission changes using a script like the example
below.

DECLARE @UserName sysname
SET @UserName = 'public'

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
DEALLOCATE 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
-----------------------
"Tim P" <tp@FejlLessorFejl.dk> wrote in message
news:Xns923271BD272D0tpFejlLessorFejldk@207.46.230.185...
> I am trying to Grant access to a user, I want to
> "GRANT All On *.* to user"
> but I get a syntax error when I use *.* and db_name.* isn't working
either.
>
> can you help ?
>
> Thanks Tim P.
>

Relevant Pages

  • Re: GRANT all on all to User ????
    ... > DECLARE @UserName sysname ... > DECLARE @GrantStatement nvarchar ... > DECLARE GrantStatements CURSOR LOCAL FAST_FORWARD ...
    (microsoft.public.sqlserver.security)
  • Re: GRANT permission to lots of tables and sp to db user
    ... You can use a script like to example below to grant mass permissions ... OPEN GrantStatements ... > to myuser ...
    (microsoft.public.sqlserver.security)
  • Re: User permissions
    ... DECLARE @UserName sysname ... DECLARE GrantStatements CURSOR LOCAL FAST_FORWARD ... SQL Server MVP ...
    (microsoft.public.sqlserver.security)