Re: Logging in irrespective of the database access settings

From: Aaron [SQL Server MVP] (ten.xoc_at_dnartreb.noraa)
Date: 07/16/04 

Date: Fri, 16 Jul 2004 09:33:24 -0400

Here's one for tables.

DECLARE @u SYSNAME
SET @u = 'someuser'

DECLARE @o SYSNAME, @t SYSNAME
DECLARE tables CURSOR LOCAL STATIC READ_ONLY
FOR
    SELECT TABLE_SCHEMA, TABLE_NAME
    FROM INFORMATION_SCHEMA.TABLES
OPEN tables
FETCH NEXT FROM tables INTO @o, @t
WHILE @@FETCH_STATUS=0
BEGIN
    EXEC('GRANT EXEC ON ['+@o+'].['+@t+'] TO ['+@u+']')
    FETCH NEXT FROM tables INTO @o, @t
END
CLOSE tables
DEALLOCATE tables 

-- 
http://www.aspfaq.com/
(Reverse address to reply.)
"Astra" <info@NoEmail.com> wrote in message
news:#$Zr3dzaEHA.2932@TK2MSFTNGP10.phx.gbl...
> Dear Aaron
>
> Many thanks re the below, but how would I put it into a cursor to execute
as
> well as generate your example scripts.
>
> Sorry for being a bit thick, but this is all new territory for me.

Relevant Pages

  • Re: number of rows in cursor
    ... DECLARE authors_cursor insensitive CURSOR FOR( ... declare @l sysname ... FETCH NEXT FROM authors_cursor into @l ...
    (microsoft.public.sqlserver.programming)
  • Re: Logging in irrespective of the database access settings
    ... SET @u = 'someuser' ... DECLARE @o SYSNAME, @t SYSNAME ... FETCH NEXT FROM tables INTO @o, ...
    (microsoft.public.sqlserver.setup)
  • Re: Database, table, index and columns
    ... DECLARE @t SYSNAME ... DECLARE tables CURSOR ... FETCH NEXT FROM tables INTO @t ...
    (microsoft.public.sqlserver.programming)
  • Re: Print?
    ... DECLARE @database sysname ... DECLARE @backupfile varchar ... FETCH NEXT FROM CursorDatabases INTO @database,@dbowner ... Open CursorDataFile ...
    (microsoft.public.sqlserver.programming)
  • RE: SQL 2000 instance showing up as version6.5 after running fixre
    ... exec sp_configure N'allow updates', 1 ... DECLARE @username varchar ... FETCH NEXT FROM list_triggers INTO @name, ... create table syssubscriptions (artid int, srvid smallint, dest_db sysname, ...
    (microsoft.public.sqlserver.clustering)