Re: User permissions

From: Chris Themistocli (cthemistocli@aw.com.cy)
Date: 11/12/02 

From: "Chris Themistocli" <cthemistocli@aw.com.cy>
Date: Mon, 11 Nov 2002 23:54:27 -0800

Thank you! I will try your script and hopefully it will
solve my problem.

Clarification: I need this user (who is a superuser used
by a client/server application) to have rights to
read/write to all tables, views and run all sps.

>-----Original Message-----
>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