granting access to multiple tables.

[joeythelips]

Hi,

I am an oracle dba and i've recently had to start learning sql server.

I have setup a user called BI on my sql server and i want to give access to certain tables owned by another user to BI.

These table names can be seen by using the following sql:
SELECT name FROM dbo.sysobjects
where xtype = 'U'

I get 867 rows returned. Is there a quick way of giving access to all these tables as you could do in oracle by using:
SELECT 'grant select on' ||' '|| name ||' '|| 'to BI' FROM dbo.sysobjects
where xtype = 'U'

jOE

 

[gradley]

Here is something I use to generate permissions using sysobjects that I received previously from a users group.

Hope it works for you!

PRINT 'Updating Permissions for database '
PRINT ' '
DECLARE @objectname varchar(255)
DECLARE @type char(1)
DECLARE @objectname_header varchar(255)
DECLARE @user varchar(50)

SET @user = xxx' -- specify user name here

DECLARE curTables CURSOR FOR
SELECT [name], type FROM sysobjects
WHERE type IN ('U', 'V','P', 'FN') -- Select Tables, Views, Stored Procedures and User Functions
and [name] not like ('DT%')
and [name] not like ('sys%')
ORDER BY type, [name]

OPEN curTables

FETCH NEXT FROM curTables INTO @objectname, @type

WHILE (@@fetch_status = 0)
BEGIN

SELECT @objectname_header = 'Granting permissions to ' + @objectname
PRINT @objectname_header

if @type = 'U' or @type = 'V'
EXEC ('Grant SELECT, INSERT, UPDATE, DELETE on ' + @objectname +' to ' + @user)

if @type = 'P' or @type = 'FN'
EXEC ('Grant EXECUTE on ' + @objectname +' to ' + @user)

FETCH NEXT FROM curTables INTO @objectname, @type

END
DEALLOCATE curTables