Granting Select to an NT Login Group

I have this procdure to grant select permission to a developers group on an
SQL Databse. The query executes but never returnes and the permissions are
set. Can anyone tell me why the little world keeps spinning and never
returns. thanks
DECLARE @.cmd as varchar(255)
DECLARE table_names CURSOR FOR
SELECT [name] FROM sysobjects WHERE type = 'u' ORDER BY [name]
DECLARE @.name as varchar(255)
OPEN table_names
FETCH NEXT FROM table_names INTO @.name
WHILE (@.@.FETCH_STATUS <> 1)
SELECT @.cmd = 'GRANT SELECT ON ' + @.name + ' TO [VS Developers]'
--PRINT @.cmd
EXEC @.cmd
FETCH NEXT FROM table_names INTO @.name
DEALLOCATE table_names
CLOSE table_names
"brymer28303" <brymer28303@.discussions.microsoft.com> wrote in message
news:D3BA7C42-13A0-49EF-A476-3A32121E2D41@.microsoft.com...
>I have this procdure to grant select permission to a developers group on an
> SQL Databse. The query executes but never returnes and the permissions
> are
> set. Can anyone tell me why the little world keeps spinning and never
> returns. thanks
> DECLARE @.cmd as varchar(255)
> DECLARE table_names CURSOR FOR
> SELECT [name] FROM sysobjects WHERE type = 'u' ORDER BY [name]
> DECLARE @.name as varchar(255)
> OPEN table_names
> FETCH NEXT FROM table_names INTO @.name
> WHILE (@.@.FETCH_STATUS <> 1)
> SELECT @.cmd = 'GRANT SELECT ON ' + @.name + ' TO [VS Developers]'
> --PRINT @.cmd
> EXEC @.cmd
> FETCH NEXT FROM table_names INTO @.name
> DEALLOCATE table_names
> CLOSE table_names
Infinite loop. Your loop doesn't include the FETCH NEXT. Also you should
test for @.@.FETCH_STATUS = 0, since values other that 1 are possible.
Should beL
DECLARE @.cmd as varchar(255)
DECLARE table_names CURSOR FOR
SELECT [name] FROM sysobjects WHERE type = 'u' ORDER BY [name]
DECLARE @.name as varchar(255)
OPEN table_names
FETCH NEXT FROM table_names INTO @.name
WHILE @.@.FETCH_STATUS = 0
BEGIN
SELECT @.cmd = 'GRANT SELECT ON ' + @.name + ' TO [VS Developers]'
--PRINT @.cmd
EXEC @.cmd
FETCH NEXT FROM table_names INTO @.name
END
CLOSE table_names
DEALLOCATE table_names
David
|||Thanks David
"David Browne" wrote:

>
> "brymer28303" <brymer28303@.discussions.microsoft.com> wrote in message
> news:D3BA7C42-13A0-49EF-A476-3A32121E2D41@.microsoft.com...
> Infinite loop. Your loop doesn't include the FETCH NEXT. Also you should
> test for @.@.FETCH_STATUS = 0, since values other that 1 are possible.
> Should beL
> DECLARE @.cmd as varchar(255)
> DECLARE table_names CURSOR FOR
> SELECT [name] FROM sysobjects WHERE type = 'u' ORDER BY [name]
> DECLARE @.name as varchar(255)
> OPEN table_names
> FETCH NEXT FROM table_names INTO @.name
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> SELECT @.cmd = 'GRANT SELECT ON ' + @.name + ' TO [VS Developers]'
> --PRINT @.cmd
> EXEC @.cmd
> FETCH NEXT FROM table_names INTO @.name
> END
> CLOSE table_names
> DEALLOCATE table_names
>
> David
>

Granting Select to an NT Login Group

I have this procdure to grant select permission to a developers group on an
SQL Databse. The query executes but never returnes and the permissions are
set. Can anyone tell me why the little world keeps spinning and never
returns. thanks
DECLARE @.cmd as varchar(255)
DECLARE table_names CURSOR FOR
SELECT [name] FROM sysobjects WHERE type = 'u' ORDER BY [name]
DECLARE @.name as varchar(255)
OPEN table_names
FETCH NEXT FROM table_names INTO @.name
WHILE (@.@.FETCH_STATUS <> 1)
SELECT @.cmd = 'GRANT SELECT ON ' + @.name + ' TO [VS Developers]'
--PRINT @.cmd
EXEC @.cmd
FETCH NEXT FROM table_names INTO @.name
DEALLOCATE table_names
CLOSE table_names"brymer28303" <brymer28303@.discussions.microsoft.com> wrote in message
news:D3BA7C42-13A0-49EF-A476-3A32121E2D41@.microsoft.com...
>I have this procdure to grant select permission to a developers group on an
> SQL Databse. The query executes but never returnes and the permissions
> are
> set. Can anyone tell me why the little world keeps spinning and never
> returns. thanks
> DECLARE @.cmd as varchar(255)
> DECLARE table_names CURSOR FOR
> SELECT [name] FROM sysobjects WHERE type = 'u' ORDER BY [name]
> DECLARE @.name as varchar(255)
> OPEN table_names
> FETCH NEXT FROM table_names INTO @.name
> WHILE (@.@.FETCH_STATUS <> 1)
> SELECT @.cmd = 'GRANT SELECT ON ' + @.name + ' TO [VS Developers]'
> --PRINT @.cmd
> EXEC @.cmd
> FETCH NEXT FROM table_names INTO @.name
> DEALLOCATE table_names
> CLOSE table_names
Infinite loop. Your loop doesn't include the FETCH NEXT. Also you should
test for @.@.FETCH_STATUS = 0, since values other that 1 are possible.
Should beL
DECLARE @.cmd as varchar(255)
DECLARE table_names CURSOR FOR
SELECT [name] FROM sysobjects WHERE type = 'u' ORDER BY [name]
DECLARE @.name as varchar(255)
OPEN table_names
FETCH NEXT FROM table_names INTO @.name
WHILE @.@.FETCH_STATUS = 0
BEGIN
SELECT @.cmd = 'GRANT SELECT ON ' + @.name + ' TO [VS Developers]'
--PRINT @.cmd
EXEC @.cmd
FETCH NEXT FROM table_names INTO @.name
END
CLOSE table_names
DEALLOCATE table_names
David|||Thanks David
"David Browne" wrote:

>
> "brymer28303" <brymer28303@.discussions.microsoft.com> wrote in message
> news:D3BA7C42-13A0-49EF-A476-3A32121E2D41@.microsoft.com...
> Infinite loop. Your loop doesn't include the FETCH NEXT. Also you should
> test for @.@.FETCH_STATUS = 0, since values other that 1 are possible.
> Should beL
> DECLARE @.cmd as varchar(255)
> DECLARE table_names CURSOR FOR
> SELECT [name] FROM sysobjects WHERE type = 'u' ORDER BY [name]
> DECLARE @.name as varchar(255)
> OPEN table_names
> FETCH NEXT FROM table_names INTO @.name
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> SELECT @.cmd = 'GRANT SELECT ON ' + @.name + ' TO [VS Developers]'
> --PRINT @.cmd
> EXEC @.cmd
> FETCH NEXT FROM table_names INTO @.name
> END
> CLOSE table_names
> DEALLOCATE table_names
>
> David
>

Granting Select to an NT Login Group

I have this procdure to grant select permission to a developers group on an
SQL Databse. The query executes but never returnes and the permissions are
set. Can anyone tell me why the little world keeps spinning and never
returns. thanks
DECLARE @.cmd as varchar(255)
DECLARE table_names CURSOR FOR
SELECT [name] FROM sysobjects WHERE type = 'u' ORDER BY [name]
DECLARE @.name as varchar(255)
OPEN table_names
FETCH NEXT FROM table_names INTO @.name
WHILE (@.@.FETCH_STATUS <> 1)
SELECT @.cmd = 'GRANT SELECT ON ' + @.name + ' TO [VS Developers]'
--PRINT @.cmd
EXEC @.cmd
FETCH NEXT FROM table_names INTO @.name
DEALLOCATE table_names
CLOSE table_names"brymer28303" <brymer28303@.discussions.microsoft.com> wrote in message
news:D3BA7C42-13A0-49EF-A476-3A32121E2D41@.microsoft.com...
>I have this procdure to grant select permission to a developers group on an
> SQL Databse. The query executes but never returnes and the permissions
> are
> set. Can anyone tell me why the little world keeps spinning and never
> returns. thanks
> DECLARE @.cmd as varchar(255)
> DECLARE table_names CURSOR FOR
> SELECT [name] FROM sysobjects WHERE type = 'u' ORDER BY [name]
> DECLARE @.name as varchar(255)
> OPEN table_names
> FETCH NEXT FROM table_names INTO @.name
> WHILE (@.@.FETCH_STATUS <> 1)
> SELECT @.cmd = 'GRANT SELECT ON ' + @.name + ' TO [VS Developers]'
> --PRINT @.cmd
> EXEC @.cmd
> FETCH NEXT FROM table_names INTO @.name
> DEALLOCATE table_names
> CLOSE table_names
Infinite loop. Your loop doesn't include the FETCH NEXT. Also you should
test for @.@.FETCH_STATUS = 0, since values other that 1 are possible.
Should beL
DECLARE @.cmd as varchar(255)
DECLARE table_names CURSOR FOR
SELECT [name] FROM sysobjects WHERE type = 'u' ORDER BY [name]
DECLARE @.name as varchar(255)
OPEN table_names
FETCH NEXT FROM table_names INTO @.name
WHILE @.@.FETCH_STATUS = 0
BEGIN
SELECT @.cmd = 'GRANT SELECT ON ' + @.name + ' TO [VS Developers]'
--PRINT @.cmd
EXEC @.cmd
FETCH NEXT FROM table_names INTO @.name
END
CLOSE table_names
DEALLOCATE table_names
David|||Thanks David
"David Browne" wrote:
>
> "brymer28303" <brymer28303@.discussions.microsoft.com> wrote in message
> news:D3BA7C42-13A0-49EF-A476-3A32121E2D41@.microsoft.com...
> >I have this procdure to grant select permission to a developers group on an
> > SQL Databse. The query executes but never returnes and the permissions
> > are
> > set. Can anyone tell me why the little world keeps spinning and never
> > returns. thanks
> >
> > DECLARE @.cmd as varchar(255)
> >
> > DECLARE table_names CURSOR FOR
> > SELECT [name] FROM sysobjects WHERE type = 'u' ORDER BY [name]
> > DECLARE @.name as varchar(255)
> >
> > OPEN table_names
> > FETCH NEXT FROM table_names INTO @.name
> > WHILE (@.@.FETCH_STATUS <> 1)
> > SELECT @.cmd = 'GRANT SELECT ON ' + @.name + ' TO [VS Developers]'
> > --PRINT @.cmd
> > EXEC @.cmd
> > FETCH NEXT FROM table_names INTO @.name
> > DEALLOCATE table_names
> > CLOSE table_names
> Infinite loop. Your loop doesn't include the FETCH NEXT. Also you should
> test for @.@.FETCH_STATUS = 0, since values other that 1 are possible.
> Should beL
> DECLARE @.cmd as varchar(255)
> DECLARE table_names CURSOR FOR
> SELECT [name] FROM sysobjects WHERE type = 'u' ORDER BY [name]
> DECLARE @.name as varchar(255)
> OPEN table_names
> FETCH NEXT FROM table_names INTO @.name
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> SELECT @.cmd = 'GRANT SELECT ON ' + @.name + ' TO [VS Developers]'
> --PRINT @.cmd
> EXEC @.cmd
> FETCH NEXT FROM table_names INTO @.name
> END
> CLOSE table_names
> DEALLOCATE table_names
>
> David
>sql

granting select permission

Hi,

How to grant select permission on table of another database from current database.

Ex: I am in database asddb

I want to grant select permission on table "test" which is in database bsddb.

Can anyone please help me in resolving this problem.Please take a look at the GRANT statement in Books Online. You need to switch to the database using USE and run the GRANT statement.

granting select permission

Hi,

How to grant select permission on table of another database from current database.

Ex: I am in database asddb

I want to grant select permission on table "test" which is in database bsddb.

Can anyone please help me in resolving this problem.Please take a look at the GRANT statement in Books Online. You need to switch to the database using USE and run the GRANT statement.

Granting Permissions on Multiple Tables

I created a new Role called GRPSELECT. I want to give this group the abilit
y to only run the SELECT statement on all the tables in my database. Right
now I can run "GRANT SELECT ON table TO GRPSELECT" in Query Analyzer, but it
only allows me to do this
to one table at a time.
How can I accomplish this to all 600 tables in the database without manually
typing in every single table?
Thanks in advance!Instead of creating a new role you can add these users/group to
db_datareader fixed db role.
Members of db_datareader fixed db role have select permissions on any
objects in the db.
Thanks,
Lyudmila Fokina
Please do not send e-mail directly to this alias. This alias is for
newsgroup purposes only
Disclaimer: This posting is provided "AS IS" with no warranties, and confers
no rights.
"Jon Jones" <Jon Jones@.discussions.microsoft.com> wrote in message
news:96F7208A-EA64-467A-8AD2-5477782ED32E@.microsoft.com...
> I created a new Role called GRPSELECT. I want to give this group the
ability to only run the SELECT statement on all the tables in my database.
Right now I can run "GRANT SELECT ON table TO GRPSELECT" in Query Analyzer,
but it only allows me to do this to one table at a time.
> How can I accomplish this to all 600 tables in the database without
manually typing in every single table?
> Thanks in advance!|||Jon,
You can use Transact-SQL to generate the script for you:
Ex:
SELECT 'GRANT SELECT ON ' + so.name + ' TO GRPSELECT'
FROM dbo.sysobjects so
WHERE so.type = 'u'
This output can then be copied.
Randy Dyess
"Jon Jones" wrote:

> I created a new Role called GRPSELECT. I want to give this group the ability to o
nly run the SELECT statement on all the tables in my database. Right now I can run
"GRANT SELECT ON table TO GRPSELECT" in Query Analyzer, but it only allows me to do
thi
s to one table at a time.
> How can I accomplish this to all 600 tables in the database without manual
ly typing in every single table?
> Thanks in advance!sql

Granting permission on multiple stored procs

Is there a better way of granting permission to a stored proc for a selected user other than (enterprise manager) select sp then accessing propertys then permissions, then user?GRANT EXEC ON <proc> TO <MyUser>

just about anything you can do in the EM can be scripted saved as script and used again. which makes you more efficient.|||but the rest of the story is this...
the project is a work in progress thing. we are added new stored procs as needed (up to 83 as of this morning) and now we wanted a selected user to test the new project. I was looking for a way to not have to type out all 83 stored proc names in order to grant access to them for this user. I was hoping for a solution/feature that i was not aware of in EM. so it looks like i will have to do this process manually either way (EM or Script)...|||select 'grant execute on ' + name + ' to user'
from sysobjects
where type = 'P'
and crdate > getdate() -1|||You are using Database Roles, I hope.

GRANTING a variable

When exec.
GRANT SELECT ON @.variable_name to user_name
The above does not work due to the variable.
Is there no way of dynamically assigning the table-name?
Thks again.You have to create the complete statement as a string ad then execute it
with EXEC or sp_xecutesql. Read an excellent article at
http://www.algonet.se/~sommar/dynamic_sql.html.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Wayne" <anonymous@.discussions.microsoft.com> wrote in message
news:11f301c3fb91$454511d0$a401280a@.phx.gbl...
> When exec.
> GRANT SELECT ON @.variable_name to user_name
> The above does not work due to the variable.
> Is there no way of dynamically assigning the table-name?
> Thks again.|||Hi,
Yes you can do it, Look in to the below code
declare @.sql nvarchar(200)
declare @.tbl_name varchar(30)
set @.tbl_name='sysobjects'
set @.sql = 'grant select on '+ @.tbl_name + ' to public'
exec sp_executesql @.sql
Thanks
Hari
MCDBA
"Wayne" <anonymous@.discussions.microsoft.com> wrote in message
news:11f301c3fb91$454511d0$a401280a@.phx.gbl...
> When exec.
> GRANT SELECT ON @.variable_name to user_name
> The above does not work due to the variable.
> Is there no way of dynamically assigning the table-name?
> Thks again.sql

Grant using Query

Hello,
I am using the following to create the text for granting permissions to
stored procedures:
USE Train
SELECT 'GRANT EXECUTE ON '+ name + ' TO Web_Publish'
FROM sysobjects WHERE (type = 'P') AND (category = 0) AND (name LIKE
'%web_%')
Is it possible to actually grant permissions by using an SQL statement like
the following?
GRANT EXECUTE ON
SELECT name
FROM sysobjects WHERE (type = 'P') AND (category = 0) AND (name LIKE
'%web_%')
TO Web_Publish
--
Thanks in advance,
StevenHi
DECLARE @.proc_name SYSNAME
DECLARE @.sql VARCHAR(4000)
SET @.proc_name = ''
WHILE 1=1
BEGIN
SET @.proc_name = (SELECT TOP 1 ROUTINE_NAME FROM
INFORMATION_SCHEMA.ROUTINES
WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME), 'IsMSShipped') = 0
-- Only user stored procedures
AND ROUTINE_TYPE = 'Procedure'
AND ROUTINE_NAME > @.proc_name
ORDER BY ROUTINE_NAME
)
IF @.proc_name IS NULL BREAK
SET @.sql = 'GRANT EXECUTE ON ' + QUOTENAME(@.proc_name) + ' TO MyUser'
EXEC (@.sql)
END
"Steven K0" <stroy@.api.com> wrote in message
news:%23KrhfbRNGHA.3944@.tk2msftngp13.phx.gbl...
> Hello,
> I am using the following to create the text for granting permissions to
> stored procedures:
> USE Train
> SELECT 'GRANT EXECUTE ON '+ name + ' TO Web_Publish'
> FROM sysobjects WHERE (type = 'P') AND (category = 0) AND (name LIKE
> '%web_%')
> Is it possible to actually grant permissions by using an SQL statement
> like the following?
> GRANT EXECUTE ON
> SELECT name
> FROM sysobjects WHERE (type = 'P') AND (category = 0) AND (name LIKE
> '%web_%')
> TO Web_Publish
> --
> Thanks in advance,
> Steven
>
>

grant to all objects

Is there an easy way to assign permissions to all objects for a user/group?
For instance, in the following BOL example, SELECT permissions are granted t
o
the public role:
GRANT SELECT
ON authors
TO public
GO
In the above example, the table object 'authors' is explicitly listed; is
there a way to use the same statement to grant the same select permission to
the public role for all objects? Or would I have to explicitly indicate each
object? In the latter case, I could use a cursor to dynamically create the
grant statements for each of my objects, however, I was hoping to find a
simpler way to have this done.
Thanks for all your responses in advance.Firstly, I think the BOL example is dumb - I wouldn't go assigning ANY
permissions to the public role in ANY database.
That said, you'd have to assign individual object permissions
individually, preferably to a role that you create but to a specific
user would work too (just much uglier and more admin overhead when users
come & go and when restoring DB backups to other servers). You can do
this in a small cursor loop which is very easy to do. Here's one I
whipped up in about 5 minutes when I read your post that will assign all
possible permissions for all user tables, views, procs & UDFs in the
current database to a specified user or role (untested):
declare @.cmd nvarchar(1000)
declare @.objname sysname
declare @.owner sysname
declare @.objtype char(2)
declare objs cursor for
select [name], user_name(uid) as owner, type from dbo.sysobjects
where type in ('U', 'P', 'V', 'FN')
order by type, [name]
for read only
open objs
fetch next from objs into @.objname, @.owner, @.objtype
while (@.@.FETCH_STATUS != -1)
begin
if (@.@.FETCH_STATUS != -2)
begin
select @.cmd = 'grant ' +
case (@.objtype)
when ('U') then ('select, insert, update, delete,
references')
when ('V') then ('select, insert, update, delete,
references')
when ('P') then ('execute')
when ('FN') then ('execute')
end + ' on [' + @.owner + '].[' + @.objname + '] to <my
user/role>'
exec (@.cmd)
end
fetch next from objs into @.objname, @.owner, @.objtype
end
close objs
deallocate objs
How easy is that?
HTH
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Rob wrote:

>Is there an easy way to assign permissions to all objects for a user/group?
>For instance, in the following BOL example, SELECT permissions are granted
to
>the public role:
>GRANT SELECT
>ON authors
>TO public
>GO
>In the above example, the table object 'authors' is explicitly listed; is
>there a way to use the same statement to grant the same select permission t
o
>the public role for all objects? Or would I have to explicitly indicate eac
h
>object? In the latter case, I could use a cursor to dynamically create the
>grant statements for each of my objects, however, I was hoping to find a
>simpler way to have this done.
>Thanks for all your responses in advance.
>sql

grant select to public

I'm NOT the dbo
but I am allowed to create table/views/UDF
But I can't not do this:
grant select on vw_I_created to public
got this error:
SQL Server Database Error: Grantor does not have GRANT permission.
Is this right ?Your user was not given GRANT permission, which is the ability to grant a
specified permission to other principals.
"klabu" wrote:
> I'm NOT the dbo
> but I am allowed to create table/views/UDF
> But I can't not do this:
> grant select on vw_I_created to public
> got this error:
> SQL Server Database Error: Grantor does not have GRANT permission.
> Is this right ?
>
>|||Hi,
On specific objects if you have to give grant again to other securiy
accounts you can use or ask the admin for the grant with below option
WITH GRANT OPTION
Thanks
Ajay
"Edgardo Valdez, MCTS / MCITP"
<EdgardoValdezMCTSMCITP@.discussions.microsoft.com> wrote in message
news:B7485BF1-0937-4FE6-97A0-A4486985F37B@.microsoft.com...
> Your user was not given GRANT permission, which is the ability to grant a
> specified permission to other principals.
> "klabu" wrote:
>> I'm NOT the dbo
>> but I am allowed to create table/views/UDF
>> But I can't not do this:
>> grant select on vw_I_created to public
>> got this error:
>> SQL Server Database Error: Grantor does not have GRANT permission.
>> Is this right ?
>>

grant select to public

I'm NOT the dbo
but I am allowed to create table/views/UDF
But I can't not do this:
grant select on vw_I_created to public
got this error:
SQL Server Database Error: Grantor does not have GRANT permission.
Is this right ?
Your user was not given GRANT permission, which is the ability to grant a
specified permission to other principals.
"klabu" wrote:

> I'm NOT the dbo
> but I am allowed to create table/views/UDF
> But I can't not do this:
> grant select on vw_I_created to public
> got this error:
> SQL Server Database Error: Grantor does not have GRANT permission.
> Is this right ?
>
>
|||Hi,
On specific objects if you have to give grant again to other securiy
accounts you can use or ask the admin for the grant with below option
WITH GRANT OPTION
Thanks
Ajay
"Edgardo Valdez, MCTS / MCITP"
<EdgardoValdezMCTSMCITP@.discussions.microsoft.com> wrote in message
news:B7485BF1-0937-4FE6-97A0-A4486985F37B@.microsoft.com...[vbcol=seagreen]
> Your user was not given GRANT permission, which is the ability to grant a
> specified permission to other principals.
> "klabu" wrote:

grant select to public

I'm NOT the dbo
but I am allowed to create table/views/UDF
But I can't not do this:
grant select on vw_I_created to public
got this error:
SQL Server Database Error: Grantor does not have GRANT permission.
Is this right ?Your user was not given GRANT permission, which is the ability to grant a
specified permission to other principals.
"klabu" wrote:

> I'm NOT the dbo
> but I am allowed to create table/views/UDF
> But I can't not do this:
> grant select on vw_I_created to public
> got this error:
> SQL Server Database Error: Grantor does not have GRANT permission.
> Is this right ?
>
>|||Hi,
On specific objects if you have to give grant again to other securiy
accounts you can use or ask the admin for the grant with below option
WITH GRANT OPTION
Thanks
Ajay
"Edgardo Valdez, MCTS / MCITP"
<EdgardoValdezMCTSMCITP@.discussions.microsoft.com> wrote in message
news:B7485BF1-0937-4FE6-97A0-A4486985F37B@.microsoft.com...[vbcol=seagreen]
> Your user was not given GRANT permission, which is the ability to grant a
> specified permission to other principals.
> "klabu" wrote:
>sql

GRANT Select to all tables on a DB

I have three main database files on a SQL 2000 server. Each database has
about 200 tables. I need the ability to easily give a user SELECT for all
tables in each database. I can use the GUI, but it takes way too long. Please
help me figure out an easy way to enumerate all tables in the database, so I
can construct a GRANT Select statement.
Thanks.
S
Add the user to the db_datareader role.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Sam" <Sam@.discussions.microsoft.com> wrote in message
news:0DC63C37-8882-4575-A0FC-7193428EA19F@.microsoft.com...
> I have three main database files on a SQL 2000 server. Each database has
> about 200 tables. I need the ability to easily give a user SELECT for all
> tables in each database. I can use the GUI, but it takes way too long.
Please
> help me figure out an easy way to enumerate all tables in the database, so
I
> can construct a GRANT Select statement.
> Thanks.
> S
|||Geoff,
Thank you for the information. I appreciate it. But I mainly need to figure
out how to quickly enumerate all the tables in a database, so that I can do a
grant or a deny on specific permissions. Please help me with that, if you
can. Thank you in advance.
S
"Geoff N. Hiten" wrote:

> Add the user to the db_datareader role.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Sam" <Sam@.discussions.microsoft.com> wrote in message
> news:0DC63C37-8882-4575-A0FC-7193428EA19F@.microsoft.com...
> Please
> I
>
>
|||I don't understand why you still want to cursor through the tables. The
solution that Geoff provided is a quick and easy way to provide select
rights on all tables to a specific database user. This is easier than
granting direct table rights and it automatically adds the appropriate
rights if new tables are added to the database.
Anyway, if you want to see a list of tables in your database:
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE OBJECTPROPERTY(object_id(TABLE_NAME), 'IsUserTable') = 1
In your first post you mention that you want to grant select rights on all
tables to a specific user.
Now you say that you want to grant or deny. I am confused as to what your
real intentions are.
Keith
"Sam" <Sam@.discussions.microsoft.com> wrote in message
news:E4BB8ACD-1124-422D-8D0A-C42CB894BBE1@.microsoft.com...
> Geoff,
> Thank you for the information. I appreciate it. But I mainly need to
figure
> out how to quickly enumerate all the tables in a database, so that I can
do a[vbcol=seagreen]
> grant or a deny on specific permissions. Please help me with that, if you
> can. Thank you in advance.
> S
> "Geoff N. Hiten" wrote:
has[vbcol=seagreen]
all[vbcol=seagreen]
database, so[vbcol=seagreen]
|||There are corresponding roles for denying read and/or write access to a
database. I suggest looking at the various system roles and read about
user-defined roles. You are probably much better off with role-based
security than trying to explicitly grant or deny access to a large number of
tables for each user.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Sam" <Sam@.discussions.microsoft.com> wrote in message
news:E4BB8ACD-1124-422D-8D0A-C42CB894BBE1@.microsoft.com...
> Geoff,
> Thank you for the information. I appreciate it. But I mainly need to
figure
> out how to quickly enumerate all the tables in a database, so that I can
do a[vbcol=seagreen]
> grant or a deny on specific permissions. Please help me with that, if you
> can. Thank you in advance.
> S
> "Geoff N. Hiten" wrote:
has[vbcol=seagreen]
all[vbcol=seagreen]
database, so[vbcol=seagreen]
|||Sam,
For all tables in one DB, for example,pub db
use pub
go
sp_msforeachtable 'grant select on ? to RO'
For all DBs, sp_msforeachdb will do.
Cheers,
SangHunJung
"Sam" wrote:

> I have three main database files on a SQL 2000 server. Each database has
> about 200 tables. I need the ability to easily give a user SELECT for all
> tables in each database. I can use the GUI, but it takes way too long. Please
> help me figure out an easy way to enumerate all tables in the database, so I
> can construct a GRANT Select statement.
> Thanks.
> S
|||Thank you, thank you, thank you.
That is exactly what I was looking for.
sam
"SangHunJung" wrote:
[vbcol=seagreen]
> Sam,
> For all tables in one DB, for example,pub db
> use pub
> go
> sp_msforeachtable 'grant select on ? to RO'
> For all DBs, sp_msforeachdb will do.
> Cheers,
> SangHunJung
> "Sam" wrote:
|||Are there similar commands to iterate through all the Stored Procs on a
database, as well as all the views? Thank you again.
Sam
"SangHunJung" wrote:
[vbcol=seagreen]
> Sam,
> For all tables in one DB, for example,pub db
> use pub
> go
> sp_msforeachtable 'grant select on ? to RO'
> For all DBs, sp_msforeachdb will do.
> Cheers,
> SangHunJung
> "Sam" wrote:
|||If you need flexibility, generate and/or execute the script yourself rather
than relying on undocumented procedures. For example:
SET NOCOUNT ON
DECLARE @.GrantStatement nvarchar(500)
DECLARE @.LastError int
DECLARE GrantStatements CURSOR LOCAL FAST_FORWARD FOR
SELECT
CASE
WHEN OBJECTPROPERTY([ob].[id], 'IsUserTable') = 1 OR
OBJECTPROPERTY([ob].[id], 'IsView') = 1 OR
OBJECTPROPERTY([ob].[id], 'IsTableFunction') = 1 OR
OBJECTPROPERTY([ob].[id], 'IsInlineFunction') = 1 THEN
N'GRANT SELECT ON ' +
QUOTENAME(USER_NAME([ob].[uid])) + '.' + QUOTENAME([ob].[name]) +
' TO MyRole'
WHEN OBJECTPROPERTY([ob].[id], 'IsScalarFunction') = 1 THEN
N'GRANT EXECUTE ON ' +
QUOTENAME(USER_NAME([ob].[uid])) + '.' + QUOTENAME([ob].[name]) +
' TO MyRole'
ELSE
N''
END
FROM
sysobjects ob
WHERE
OBJECTPROPERTY([ob].[id], 'IsMSShipped') = 0 AND
(OBJECTPROPERTY([ob].[id], 'IsProcedure') = 1 OR
OBJECTPROPERTY([ob].[id], 'IsUserTable') = 1 OR
OBJECTPROPERTY([ob].[id], 'IsView') = 1 OR
OBJECTPROPERTY([ob].[id], 'IsInlineFunction') = 1 OR
OBJECTPROPERTY([ob].[id], 'IsTableFunction') = 1)
OPEN GrantStatements
WHILE 1 = 1
BEGIN
FETCH NEXT FROM GrantStatements INTO @.GrantStatement
IF @.@.FETCH_STATUS = -1 BREAK
RAISERROR (@.GrantStatement, 0, 1) WITH NOWAIT
EXECUTE sp_ExecuteSQL @.GrantStatement
END
CLOSE GrantStatements
DEALLOCATE GrantStatements
Hope this helps.
Dan Guzman
SQL Server MVP
"Sam" <Sam@.discussions.microsoft.com> wrote in message
news:A411570B-8F1B-46F7-8C88-9A00702C125A@.microsoft.com...[vbcol=seagreen]
> Are there similar commands to iterate through all the Stored Procs on a
> database, as well as all the views? Thank you again.
> Sam
> "SangHunJung" wrote:

GRANT Select to all tables on a DB

I have three main database files on a SQL 2000 server. Each database has
about 200 tables. I need the ability to easily give a user SELECT for all
tables in each database. I can use the GUI, but it takes way too long. Pleas
e
help me figure out an easy way to enumerate all tables in the database, so I
can construct a GRANT Select statement.
Thanks.
SAdd the user to the db_datareader role.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Sam" <Sam@.discussions.microsoft.com> wrote in message
news:0DC63C37-8882-4575-A0FC-7193428EA19F@.microsoft.com...
> I have three main database files on a SQL 2000 server. Each database has
> about 200 tables. I need the ability to easily give a user SELECT for all
> tables in each database. I can use the GUI, but it takes way too long.
Please
> help me figure out an easy way to enumerate all tables in the database, so
I
> can construct a GRANT Select statement.
> Thanks.
> S|||Geoff,
Thank you for the information. I appreciate it. But I mainly need to figure
out how to quickly enumerate all the tables in a database, so that I can do
a
grant or a deny on specific permissions. Please help me with that, if you
can. Thank you in advance.
S
"Geoff N. Hiten" wrote:

> Add the user to the db_datareader role.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Sam" <Sam@.discussions.microsoft.com> wrote in message
> news:0DC63C37-8882-4575-A0FC-7193428EA19F@.microsoft.com...
> Please
> I
>
>|||I don't understand why you still want to cursor through the tables. The
solution that Geoff provided is a quick and easy way to provide select
rights on all tables to a specific database user. This is easier than
granting direct table rights and it automatically adds the appropriate
rights if new tables are added to the database.
Anyway, if you want to see a list of tables in your database:
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE OBJECTPROPERTY(object_id(TABLE_NAME), 'IsUserTable') = 1
In your first post you mention that you want to grant select rights on all
tables to a specific user.
Now you say that you want to grant or deny. I am confused as to what your
real intentions are.
Keith
"Sam" <Sam@.discussions.microsoft.com> wrote in message
news:E4BB8ACD-1124-422D-8D0A-C42CB894BBE1@.microsoft.com...
> Geoff,
> Thank you for the information. I appreciate it. But I mainly need to
figure
> out how to quickly enumerate all the tables in a database, so that I can
do a[vbcol=seagreen]
> grant or a deny on specific permissions. Please help me with that, if you
> can. Thank you in advance.
> S
> "Geoff N. Hiten" wrote:
>
has[vbcol=seagreen]
all[vbcol=seagreen]
database, so[vbcol=seagreen]|||There are corresponding roles for denying read and/or write access to a
database. I suggest looking at the various system roles and read about
user-defined roles. You are probably much better off with role-based
security than trying to explicitly grant or deny access to a large number of
tables for each user.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Sam" <Sam@.discussions.microsoft.com> wrote in message
news:E4BB8ACD-1124-422D-8D0A-C42CB894BBE1@.microsoft.com...
> Geoff,
> Thank you for the information. I appreciate it. But I mainly need to
figure
> out how to quickly enumerate all the tables in a database, so that I can
do a[vbcol=seagreen]
> grant or a deny on specific permissions. Please help me with that, if you
> can. Thank you in advance.
> S
> "Geoff N. Hiten" wrote:
>
has[vbcol=seagreen]
all[vbcol=seagreen]
database, so[vbcol=seagreen]|||Sam,
For all tables in one DB, for example,pub db
use pub
go
sp_msforeachtable 'grant select on ? to RO'
For all DBs, sp_msforeachdb will do.
Cheers,
SangHunJung
"Sam" wrote:

> I have three main database files on a SQL 2000 server. Each database has
> about 200 tables. I need the ability to easily give a user SELECT for all
> tables in each database. I can use the GUI, but it takes way too long. Ple
ase
> help me figure out an easy way to enumerate all tables in the database, so
I
> can construct a GRANT Select statement.
> Thanks.
> S|||Thank you, thank you, thank you.
That is exactly what I was looking for.
sam
"SangHunJung" wrote:
[vbcol=seagreen]
> Sam,
> For all tables in one DB, for example,pub db
> use pub
> go
> sp_msforeachtable 'grant select on ? to RO'
> For all DBs, sp_msforeachdb will do.
> Cheers,
> SangHunJung
> "Sam" wrote:
>|||Are there similar commands to iterate through all the Stored Procs on a
database, as well as all the views? Thank you again.
Sam
"SangHunJung" wrote:
[vbcol=seagreen]
> Sam,
> For all tables in one DB, for example,pub db
> use pub
> go
> sp_msforeachtable 'grant select on ? to RO'
> For all DBs, sp_msforeachdb will do.
> Cheers,
> SangHunJung
> "Sam" wrote:
>|||If you need flexibility, generate and/or execute the script yourself rather
than relying on undocumented procedures. For example:
SET NOCOUNT ON
DECLARE @.GrantStatement nvarchar(500)
DECLARE @.LastError int
DECLARE GrantStatements CURSOR LOCAL FAST_FORWARD FOR
SELECT
CASE
WHEN OBJECTPROPERTY([ob].[id], 'IsUserTable') = 1 OR
OBJECTPROPERTY([ob].[id], 'IsView') = 1 OR
OBJECTPROPERTY([ob].[id], 'IsTableFunction') = 1 OR
OBJECTPROPERTY([ob].[id], 'IsInlineFunction') = 1 THEN
N'GRANT SELECT ON ' +
QUOTENAME(USER_NAME([ob].[uid])) + '.' + QUOTENAME([ob].[nam
e]) +
' TO MyRole'
WHEN OBJECTPROPERTY([ob].[id], 'IsScalarFunction') = 1 THEN
N'GRANT EXECUTE ON ' +
QUOTENAME(USER_NAME([ob].[uid])) + '.' + QUOTENAME([ob].[nam
e]) +
' TO MyRole'
ELSE
N''
END
FROM
sysobjects ob
WHERE
OBJECTPROPERTY([ob].[id], 'IsMSShipped') = 0 AND
(OBJECTPROPERTY([ob].[id], 'IsProcedure') = 1 OR
OBJECTPROPERTY([ob].[id], 'IsUserTable') = 1 OR
OBJECTPROPERTY([ob].[id], 'IsView') = 1 OR
OBJECTPROPERTY([ob].[id], 'IsInlineFunction') = 1 OR
OBJECTPROPERTY([ob].[id], 'IsTableFunction') = 1)
OPEN GrantStatements
WHILE 1 = 1
BEGIN
FETCH NEXT FROM GrantStatements INTO @.GrantStatement
IF @.@.FETCH_STATUS = -1 BREAK
RAISERROR (@.GrantStatement, 0, 1) WITH NOWAIT
EXECUTE sp_ExecuteSQL @.GrantStatement
END
CLOSE GrantStatements
DEALLOCATE GrantStatements
Hope this helps.
Dan Guzman
SQL Server MVP
"Sam" <Sam@.discussions.microsoft.com> wrote in message
news:A411570B-8F1B-46F7-8C88-9A00702C125A@.microsoft.com...[vbcol=seagreen]
> Are there similar commands to iterate through all the Stored Procs on a
> database, as well as all the views? Thank you again.
> Sam
> "SangHunJung" wrote:
>

GRANT Select to all tables on a DB

I have three main database files on a SQL 2000 server. Each database has
about 200 tables. I need the ability to easily give a user SELECT for all
tables in each database. I can use the GUI, but it takes way too long. Please
help me figure out an easy way to enumerate all tables in the database, so I
can construct a GRANT Select statement.
Thanks.
SAdd the user to the db_datareader role.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Sam" <Sam@.discussions.microsoft.com> wrote in message
news:0DC63C37-8882-4575-A0FC-7193428EA19F@.microsoft.com...
> I have three main database files on a SQL 2000 server. Each database has
> about 200 tables. I need the ability to easily give a user SELECT for all
> tables in each database. I can use the GUI, but it takes way too long.
Please
> help me figure out an easy way to enumerate all tables in the database, so
I
> can construct a GRANT Select statement.
> Thanks.
> S|||Geoff,
Thank you for the information. I appreciate it. But I mainly need to figure
out how to quickly enumerate all the tables in a database, so that I can do a
grant or a deny on specific permissions. Please help me with that, if you
can. Thank you in advance.
S
"Geoff N. Hiten" wrote:
> Add the user to the db_datareader role.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Sam" <Sam@.discussions.microsoft.com> wrote in message
> news:0DC63C37-8882-4575-A0FC-7193428EA19F@.microsoft.com...
> > I have three main database files on a SQL 2000 server. Each database has
> > about 200 tables. I need the ability to easily give a user SELECT for all
> > tables in each database. I can use the GUI, but it takes way too long.
> Please
> > help me figure out an easy way to enumerate all tables in the database, so
> I
> > can construct a GRANT Select statement.
> > Thanks.
> > S
>
>|||I don't understand why you still want to cursor through the tables. The
solution that Geoff provided is a quick and easy way to provide select
rights on all tables to a specific database user. This is easier than
granting direct table rights and it automatically adds the appropriate
rights if new tables are added to the database.
Anyway, if you want to see a list of tables in your database:
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE OBJECTPROPERTY(object_id(TABLE_NAME), 'IsUserTable') = 1
In your first post you mention that you want to grant select rights on all
tables to a specific user.
Now you say that you want to grant or deny. I am confused as to what your
real intentions are.
--
Keith
"Sam" <Sam@.discussions.microsoft.com> wrote in message
news:E4BB8ACD-1124-422D-8D0A-C42CB894BBE1@.microsoft.com...
> Geoff,
> Thank you for the information. I appreciate it. But I mainly need to
figure
> out how to quickly enumerate all the tables in a database, so that I can
do a
> grant or a deny on specific permissions. Please help me with that, if you
> can. Thank you in advance.
> S
> "Geoff N. Hiten" wrote:
> > Add the user to the db_datareader role.
> >
> > --
> > Geoff N. Hiten
> > Microsoft SQL Server MVP
> > Senior Database Administrator
> > Careerbuilder.com
> >
> > I support the Professional Association for SQL Server
> > www.sqlpass.org
> >
> > "Sam" <Sam@.discussions.microsoft.com> wrote in message
> > news:0DC63C37-8882-4575-A0FC-7193428EA19F@.microsoft.com...
> > > I have three main database files on a SQL 2000 server. Each database
has
> > > about 200 tables. I need the ability to easily give a user SELECT for
all
> > > tables in each database. I can use the GUI, but it takes way too long.
> > Please
> > > help me figure out an easy way to enumerate all tables in the
database, so
> > I
> > > can construct a GRANT Select statement.
> > > Thanks.
> > > S
> >
> >
> >|||There are corresponding roles for denying read and/or write access to a
database. I suggest looking at the various system roles and read about
user-defined roles. You are probably much better off with role-based
security than trying to explicitly grant or deny access to a large number of
tables for each user.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Sam" <Sam@.discussions.microsoft.com> wrote in message
news:E4BB8ACD-1124-422D-8D0A-C42CB894BBE1@.microsoft.com...
> Geoff,
> Thank you for the information. I appreciate it. But I mainly need to
figure
> out how to quickly enumerate all the tables in a database, so that I can
do a
> grant or a deny on specific permissions. Please help me with that, if you
> can. Thank you in advance.
> S
> "Geoff N. Hiten" wrote:
> > Add the user to the db_datareader role.
> >
> > --
> > Geoff N. Hiten
> > Microsoft SQL Server MVP
> > Senior Database Administrator
> > Careerbuilder.com
> >
> > I support the Professional Association for SQL Server
> > www.sqlpass.org
> >
> > "Sam" <Sam@.discussions.microsoft.com> wrote in message
> > news:0DC63C37-8882-4575-A0FC-7193428EA19F@.microsoft.com...
> > > I have three main database files on a SQL 2000 server. Each database
has
> > > about 200 tables. I need the ability to easily give a user SELECT for
all
> > > tables in each database. I can use the GUI, but it takes way too long.
> > Please
> > > help me figure out an easy way to enumerate all tables in the
database, so
> > I
> > > can construct a GRANT Select statement.
> > > Thanks.
> > > S
> >
> >
> >|||Sam,
For all tables in one DB, for example,pub db
use pub
go
sp_msforeachtable 'grant select on ? to RO'
For all DBs, sp_msforeachdb will do.
Cheers,
SangHunJung
"Sam" wrote:
> I have three main database files on a SQL 2000 server. Each database has
> about 200 tables. I need the ability to easily give a user SELECT for all
> tables in each database. I can use the GUI, but it takes way too long. Please
> help me figure out an easy way to enumerate all tables in the database, so I
> can construct a GRANT Select statement.
> Thanks.
> S|||Thank you, thank you, thank you.
That is exactly what I was looking for.
sam
"SangHunJung" wrote:
> Sam,
> For all tables in one DB, for example,pub db
> use pub
> go
> sp_msforeachtable 'grant select on ? to RO'
> For all DBs, sp_msforeachdb will do.
> Cheers,
> SangHunJung
> "Sam" wrote:
> > I have three main database files on a SQL 2000 server. Each database has
> > about 200 tables. I need the ability to easily give a user SELECT for all
> > tables in each database. I can use the GUI, but it takes way too long. Please
> > help me figure out an easy way to enumerate all tables in the database, so I
> > can construct a GRANT Select statement.
> > Thanks.
> > S|||Are there similar commands to iterate through all the Stored Procs on a
database, as well as all the views? Thank you again.
Sam
"SangHunJung" wrote:
> Sam,
> For all tables in one DB, for example,pub db
> use pub
> go
> sp_msforeachtable 'grant select on ? to RO'
> For all DBs, sp_msforeachdb will do.
> Cheers,
> SangHunJung
> "Sam" wrote:
> > I have three main database files on a SQL 2000 server. Each database has
> > about 200 tables. I need the ability to easily give a user SELECT for all
> > tables in each database. I can use the GUI, but it takes way too long. Please
> > help me figure out an easy way to enumerate all tables in the database, so I
> > can construct a GRANT Select statement.
> > Thanks.
> > S|||If you need flexibility, generate and/or execute the script yourself rather
than relying on undocumented procedures. For example:
SET NOCOUNT ON
DECLARE @.GrantStatement nvarchar(500)
DECLARE @.LastError int
DECLARE GrantStatements CURSOR LOCAL FAST_FORWARD FOR
SELECT
CASE
WHEN OBJECTPROPERTY([ob].[id], 'IsUserTable') = 1 OR
OBJECTPROPERTY([ob].[id], 'IsView') = 1 OR
OBJECTPROPERTY([ob].[id], 'IsTableFunction') = 1 OR
OBJECTPROPERTY([ob].[id], 'IsInlineFunction') = 1 THEN
N'GRANT SELECT ON ' +
QUOTENAME(USER_NAME([ob].[uid])) + '.' + QUOTENAME([ob].[name]) +
' TO MyRole'
WHEN OBJECTPROPERTY([ob].[id], 'IsScalarFunction') = 1 THEN
N'GRANT EXECUTE ON ' +
QUOTENAME(USER_NAME([ob].[uid])) + '.' + QUOTENAME([ob].[name]) +
' TO MyRole'
ELSE
N''
END
FROM
sysobjects ob
WHERE
OBJECTPROPERTY([ob].[id], 'IsMSShipped') = 0 AND
(OBJECTPROPERTY([ob].[id], 'IsProcedure') = 1 OR
OBJECTPROPERTY([ob].[id], 'IsUserTable') = 1 OR
OBJECTPROPERTY([ob].[id], 'IsView') = 1 OR
OBJECTPROPERTY([ob].[id], 'IsInlineFunction') = 1 OR
OBJECTPROPERTY([ob].[id], 'IsTableFunction') = 1)
OPEN GrantStatements
WHILE 1 = 1
BEGIN
FETCH NEXT FROM GrantStatements INTO @.GrantStatement
IF @.@.FETCH_STATUS = -1 BREAK
RAISERROR (@.GrantStatement, 0, 1) WITH NOWAIT
EXECUTE sp_ExecuteSQL @.GrantStatement
END
CLOSE GrantStatements
DEALLOCATE GrantStatements
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Sam" <Sam@.discussions.microsoft.com> wrote in message
news:A411570B-8F1B-46F7-8C88-9A00702C125A@.microsoft.com...
> Are there similar commands to iterate through all the Stored Procs on a
> database, as well as all the views? Thank you again.
> Sam
> "SangHunJung" wrote:
>> Sam,
>> For all tables in one DB, for example,pub db
>> use pub
>> go
>> sp_msforeachtable 'grant select on ? to RO'
>> For all DBs, sp_msforeachdb will do.
>> Cheers,
>> SangHunJung
>> "Sam" wrote:
>> > I have three main database files on a SQL 2000 server. Each database
>> > has
>> > about 200 tables. I need the ability to easily give a user SELECT for
>> > all
>> > tables in each database. I can use the GUI, but it takes way too long.
>> > Please
>> > help me figure out an easy way to enumerate all tables in the database,
>> > so I
>> > can construct a GRANT Select statement.
>> > Thanks.
>> > S

Grant select permission on all Tables to a DB role

Hello,
I got Win2k Advanced Server with SQL 2K running.
I am trying to figure out one single query will give a permission to all
tables under a database. A database has 20 - 30 tables with several custom
roles and it is pain to run a query that give a permission on one table at a
time.
Grant select on my_table to my_role
Grant update on my_table1 to my_role1
Can any one turn a light for me on this'
Thanks in advance.
SangHunThere might be another way, but you could use the sp_msforeachtable sproc.
Please note that it's undocumented/unsupported, so insert usual warnings
here (don't rely on it in production code or expect it to be there in the
next release, etc, etc.)
But I digress...
sp_msforeachtable 'grant select on ? to my_role'
Will grant select on every table in the current database to my_role.
Here is an article with more info on that sproc and another related sproc,
sp_msforeachdb:
http://www.dbazine.com/larsen5.shtml
"SangHunJung" <SangHunJung@.discussions.microsoft.com> wrote in message
news:CF69101B-4AE0-4C38-9466-FDCFE5818569@.microsoft.com...
> Hello,
> I got Win2k Advanced Server with SQL 2K running.
> I am trying to figure out one single query will give a permission to all
> tables under a database. A database has 20 - 30 tables with several
custom
> roles and it is pain to run a query that give a permission on one table at
a
> time.
> Grant select on my_table to my_role
> Grant update on my_table1 to my_role1
> Can any one turn a light for me on this'
> Thanks in advance.
> SangHun|||Why can't you just add the role to db_datareader and db_datawriter?
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:eZvZS68eEHA.3100@.TK2MSFTNGP10.phx.gbl...
There might be another way, but you could use the sp_msforeachtable sproc.
Please note that it's undocumented/unsupported, so insert usual warnings
here (don't rely on it in production code or expect it to be there in the
next release, etc, etc.)
But I digress...
sp_msforeachtable 'grant select on ? to my_role'
Will grant select on every table in the current database to my_role.
Here is an article with more info on that sproc and another related sproc,
sp_msforeachdb:
http://www.dbazine.com/larsen5.shtml
"SangHunJung" <SangHunJung@.discussions.microsoft.com> wrote in message
news:CF69101B-4AE0-4C38-9466-FDCFE5818569@.microsoft.com...
> Hello,
> I got Win2k Advanced Server with SQL 2K running.
> I am trying to figure out one single query will give a permission to all
> tables under a database. A database has 20 - 30 tables with several
custom
> roles and it is pain to run a query that give a permission on one table at
a
> time.
> Grant select on my_table to my_role
> Grant update on my_table1 to my_role1
> Can any one turn a light for me on this'
> Thanks in advance.
> SangHun|||Does db_reader role has select permission on all existing tables?
I have seem that even I added a user to the db_reader role, the user didn't
have select permission to any table until I grant permission to specific
table for the user.
I thought I might create a read-only role and manage from there.
Thanks,
"Tom Moreau" wrote:

> Why can't you just add the role to db_datareader and db_datawriter?
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com/sql
>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:eZvZS68eEHA.3100@.TK2MSFTNGP10.phx.gbl...
> There might be another way, but you could use the sp_msforeachtable sproc.
> Please note that it's undocumented/unsupported, so insert usual warnings
> here (don't rely on it in production code or expect it to be there in the
> next release, etc, etc.)
> But I digress...
> sp_msforeachtable 'grant select on ? to my_role'
> Will grant select on every table in the current database to my_role.
> Here is an article with more info on that sproc and another related sproc,
> sp_msforeachdb:
> http://www.dbazine.com/larsen5.shtml
>
> "SangHunJung" <SangHunJung@.discussions.microsoft.com> wrote in message
> news:CF69101B-4AE0-4C38-9466-FDCFE5818569@.microsoft.com...
> custom
> a
>
>|||Thanks Addm,
I will try that out on testing env.
SangHun
"Adam Machanic" wrote:

> There might be another way, but you could use the sp_msforeachtable sproc.
> Please note that it's undocumented/unsupported, so insert usual warnings
> here (don't rely on it in production code or expect it to be there in the
> next release, etc, etc.)
> But I digress...
> sp_msforeachtable 'grant select on ? to my_role'
> Will grant select on every table in the current database to my_role.
> Here is an article with more info on that sproc and another related sproc,
> sp_msforeachdb:
> http://www.dbazine.com/larsen5.shtml
>
> "SangHunJung" <SangHunJung@.discussions.microsoft.com> wrote in message
> news:CF69101B-4AE0-4C38-9466-FDCFE5818569@.microsoft.com...
> custom
> a
>
>|||The db_reader role has SELECT permission on all tables and views. I've not
seen the problem you describe. Perhaps you can give us a repro script?
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"SangHunJung" <SangHunJung@.discussions.microsoft.com> wrote in message
news:3BF1E1D9-412B-4282-B04F-DCF952713E36@.microsoft.com...
Does db_reader role has select permission on all existing tables?
I have seem that even I added a user to the db_reader role, the user didn't
have select permission to any table until I grant permission to specific
table for the user.
I thought I might create a read-only role and manage from there.
Thanks,
"Tom Moreau" wrote:

> Why can't you just add the role to db_datareader and db_datawriter?
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com/sql
>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:eZvZS68eEHA.3100@.TK2MSFTNGP10.phx.gbl...
> There might be another way, but you could use the sp_msforeachtable sproc.
> Please note that it's undocumented/unsupported, so insert usual warnings
> here (don't rely on it in production code or expect it to be there in the
> next release, etc, etc.)
> But I digress...
> sp_msforeachtable 'grant select on ? to my_role'
> Will grant select on every table in the current database to my_role.
> Here is an article with more info on that sproc and another related sproc,
> sp_msforeachdb:
> http://www.dbazine.com/larsen5.shtml
>
> "SangHunJung" <SangHunJung@.discussions.microsoft.com> wrote in message
> news:CF69101B-4AE0-4C38-9466-FDCFE5818569@.microsoft.com...
> custom
at[vbcol=seagreen]
> a
>
>

Grant select permission on all tables

Hi Gurus, Can any one tell me how to grant select permisision all tables in a DB
like Grant all on [all tables] to myuser.
Thanks in advance.
Srinivas varanasiUse the Information Schema views...like

SELECT 'GRANT SELECT ON ' + TABLE_NAME + ' TO USER'
FROM INFORMATION_SCHEMA.Tables

Now this is from the public library 9:30 sat morning folks, so check the syntax...

No testing here|||another idea is

sp_addrolemember 'db_datareader' ,
'[domain\username] or [username]'

thats not exactly a 'grant' but it will give the user read access to all tables in the database.sql

GRANT SELECT on View

I have granted SELECT permissions on a view to a user, now when I run the
view it tells me that the user is denied select permissions on a table. The
user does not have any other permissions set on any tables or views other
than the SELECT on this particular view. How can I allow the user to view
this data?
Thank you in advance,
RuiThis sounds like a broken ownership problem. Does the same user (e.g. dbo)
own the view as owns the table? To avoid this problem, both objects will
need to be owned by the same user.
"Rui Peres" <peresr@.southcoast.org> wrote in message
news:u2quzdN0FHA.2960@.tk2msftngp13.phx.gbl...
>I have granted SELECT permissions on a view to a user, now when I run the
> view it tells me that the user is denied select permissions on a table.
> The
> user does not have any other permissions set on any tables or views other
> than the SELECT on this particular view. How can I allow the user to view
> this data?
> Thank you in advance,
> Rui
>|||Thanks, that did it.
Rui
"Lori Clark" <lclark@.dbadvisor.com> wrote in message
news:ebAkZvN0FHA.460@.TK2MSFTNGP15.phx.gbl...
> This sounds like a broken ownership problem. Does the same user (e.g.
dbo)
> own the view as owns the table? To avoid this problem, both objects will
> need to be owned by the same user.
>
> "Rui Peres" <peresr@.southcoast.org> wrote in message
> news:u2quzdN0FHA.2960@.tk2msftngp13.phx.gbl...
other[vbcol=seagreen]
view[vbcol=seagreen]
>

'GRANT SELECT ON sysprocesses TO PUBLIC' does not work in SQL 2005

'GRANT SELECT ON sysprocesses TO PUBLIC'
This statement used to work in SQL 7.0 and 2000.
But due to changes in security in SQL 2005 I receive
Server: Msg 4610, Level 16, State 1, Line 1
You can only grant or revoke permissions on objects in the current database.
I am currently on master DB. So I do not understand the error message.
'sysproceses' is a sytem view, in the sys schema.
Objects created in SQL 2005 belong to schemas.
In SQL 2000, sysprocesses is a system table ans objects belong to owner.
Any idea?
Best regards
AlexCT
You should post this in the 2005 beta newsgroups
Bert