Granting Permissions using SQL 2005 Schema...

All,

I have been asked to grant a Windows group Full access to all tables under our Sandbox Schema. This will allow these users to do anything to the tables under this Schema.

I created the Windows Group (Sandbox Users), created the login in SQL, created the user in the database that is tied to the Windows group, then ran GRANT CONTROL ON SCHEMA::[Sandbox] TO [Sandbox Users].

I have verified that the users are in the Windows group, but they state that they still can not delete tables under the Sandbox Schema.

Anyone have any ideas?

Thanks,

Justin

They would need alter schema to drop tables in the schema.

GRANT ALTER ON SCHEMA::[Sandbox] TO [Sandbox Users]

-Sue

|||

CONTROL should cover ALTER and DELETE. My guess is that the users from that group have been denied some permission that is affecting their DELET statements.

You can make use of fn_my_permissions and has_perm_by_name to find out the actual permissions on the object, for example:

-- Connected as/impersonating a member of Sandbox users

--

SELECT * FROM fn_my_permissions( 'Sandbox', 'schema' )

go

SELECT has_perms_by_name( 'Sandbox.SampleTable', 'object', 'DELETE' )

go

I hope this information helps,

-Raul Garcia

SDE/T

SQL Server Engine

|||

Thanks for catching that Raul...not even sure what I was thinking last night. Or not thinking at that moment.

-Sue

|||

Raul,

Thanks... that will help. I will be working with the user this morning to see if I can figure out why he is having this problem.

It looks like the permissions are fine. I was helping him out this morning and I think he has an issue with the package he was trying to run, bu the fn_my_permissions helped out tremendously.

Thanks!

Justin

Granting Permissions using SQL 2005 Schema...

All,

I have been asked to grant a Windows group Full access to all tables under our Sandbox Schema. This will allow these users to do anything to the tables under this Schema.

I created the Windows Group (Sandbox Users), created the login in SQL, created the user in the database that is tied to the Windows group, then ran GRANT CONTROL ON SCHEMA::[Sandbox] TO [Sandbox Users].

I have verified that the users are in the Windows group, but they state that they still can not delete tables under the Sandbox Schema.

Anyone have any ideas?

Thanks,

Justin

They would need alter schema to drop tables in the schema.

GRANT ALTER ON SCHEMA::[Sandbox] TO [Sandbox Users]

-Sue

|||

CONTROL should cover ALTER and DELETE. My guess is that the users from that group have been denied some permission that is affecting their DELET statements.

You can make use of fn_my_permissions and has_perm_by_name to find out the actual permissions on the object, for example:

-- Connected as/impersonating a member of Sandbox users

--

SELECT * FROM fn_my_permissions( 'Sandbox', 'schema' )

go

SELECT has_perms_by_name( 'Sandbox.SampleTable', 'object', 'DELETE' )

go

I hope this information helps,

-Raul Garcia

SDE/T

SQL Server Engine

|||

Thanks for catching that Raul...not even sure what I was thinking last night. Or not thinking at that moment.

-Sue

|||

Raul,

Thanks... that will help. I will be working with the user this morning to see if I can figure out why he is having this problem.

It looks like the permissions are fine. I was helping him out this morning and I think he has an issue with the package he was trying to run, bu the fn_my_permissions helped out tremendously.

Thanks!

Justin

granting permissions on set of tables

I have 500 tables in my Db.In those some tables name starts with abc (abc_emp,abc_dept) and rest of the tables name start with xyz(xyz_emp,xyz_transactions).I wanted to give select,insert,update and delete permissions for an user on the tables which starts with abc.
How can i do that in a much easier and sophisticated way.

Thanks.What I would probably do is create a role and call it something like abc_tables. Then generate the commands to grant the permissions on those tables with a query like:

select 'grant select, update, insert, delete on ' + name + ' to abc_tables'
from sysobjects
where type = 'U'
and name like 'abc%'

Run the resulting grant statements, then add the user to the role. The beauty of this is, if you have to grant the same permissions to another user, then you have most of the work already done.

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 permissions

I have a production server with a few databases. Each database has about
100-150 tables, and 200 or so Stored Procs.
I have a new user john that gets created on each database. I need to give
him the ability to select, insert, update and delete from any user table, and
to be able to execute any Stored Procedure.
If I grant this new user membership to db_datareader, he gets select from
any user table.
If I grant this new user membership to db_datawriter, he gets insert,
update, delete to any user table.
So far, so good. Now the problem -
What role can I grant that would allow this user to be able to run any
stored procedure? Note that my manager will not let me grant him db_owner, as
this gives him too much rights.
And what every I do to give him the above rights, he can't be allowed to
create tables, create views or create any stored procedures
I could do this manually through the GUI, but with over 200 stored procs per
table, this is too time consuming. I could script, but this is also something
labor intensive, am looking for an easier way.
Thank you in advance.
Sam
Think I found the solution. For each table, use the:
DENY CREATE VIEW to john
DENY CREATE TABLE to john
DENY CREATE SP to john
Still not sure how to give a user rights to execute all Stored Procedures,
though.
Sam
"Sam" wrote:

> I have a production server with a few databases. Each database has about
> 100-150 tables, and 200 or so Stored Procs.
> I have a new user john that gets created on each database. I need to give
> him the ability to select, insert, update and delete from any user table, and
> to be able to execute any Stored Procedure.
> If I grant this new user membership to db_datareader, he gets select from
> any user table.
> If I grant this new user membership to db_datawriter, he gets insert,
> update, delete to any user table.
> So far, so good. Now the problem -
> What role can I grant that would allow this user to be able to run any
> stored procedure? Note that my manager will not let me grant him db_owner, as
> this gives him too much rights.
> And what every I do to give him the above rights, he can't be allowed to
> create tables, create views or create any stored procedures
> I could do this manually through the GUI, but with over 200 stored procs per
> table, this is too time consuming. I could script, but this is also something
> labor intensive, am looking for an easier way.
> Thank you in advance.
> Sam
|||Create a new role in each database.
Grant the appropriate permissions to this role. (I know it's time
consuming, but it's a one time deal).
Then add your new users to this role in each db.
If you check around, there have been several scripts already created that
will grant permissions. You just need to tweak them a bit. My guess is
that in an hour worth of your time, you will have the script completed.
This newsgroup has had several posted to it within the last two weeks.
Rick Sawtell
MCT, MCSD, MCDBA
"Sam" <Sam@.discussions.microsoft.com> wrote in message
news:E7839D71-7EC7-47A0-96DF-4A62DAEED1D0@.microsoft.com...
> I have a production server with a few databases. Each database has about
> 100-150 tables, and 200 or so Stored Procs.
> I have a new user john that gets created on each database. I need to give
> him the ability to select, insert, update and delete from any user table,
and
> to be able to execute any Stored Procedure.
> If I grant this new user membership to db_datareader, he gets select from
> any user table.
> If I grant this new user membership to db_datawriter, he gets insert,
> update, delete to any user table.
> So far, so good. Now the problem -
> What role can I grant that would allow this user to be able to run any
> stored procedure? Note that my manager will not let me grant him db_owner,
as
> this gives him too much rights.
> And what every I do to give him the above rights, he can't be allowed to
> create tables, create views or create any stored procedures
> I could do this manually through the GUI, but with over 200 stored procs
per
> table, this is too time consuming. I could script, but this is also
something
> labor intensive, am looking for an easier way.
> Thank you in advance.
> Sam

Granting permissions

I have a production server with a few databases. Each database has about
100-150 tables, and 200 or so Stored Procs.
I have a new user john that gets created on each database. I need to give
him the ability to select, insert, update and delete from any user table, an
d
to be able to execute any Stored Procedure.
If I grant this new user membership to db_datareader, he gets select from
any user table.
If I grant this new user membership to db_datawriter, he gets insert,
update, delete to any user table.
So far, so good. Now the problem -
What role can I grant that would allow this user to be able to run any
stored procedure? Note that my manager will not let me grant him db_owner, a
s
this gives him too much rights.
And what every I do to give him the above rights, he can't be allowed to
create tables, create views or create any stored procedures
I could do this manually through the GUI, but with over 200 stored procs per
table, this is too time consuming. I could script, but this is also somethin
g
labor intensive, am looking for an easier way.
Thank you in advance.
SamThink I found the solution. For each table, use the:
DENY CREATE VIEW to john
DENY CREATE TABLE to john
DENY CREATE SP to john
Still not sure how to give a user rights to execute all Stored Procedures,
though.
Sam
"Sam" wrote:

> I have a production server with a few databases. Each database has about
> 100-150 tables, and 200 or so Stored Procs.
> I have a new user john that gets created on each database. I need to give
> him the ability to select, insert, update and delete from any user table,
and
> to be able to execute any Stored Procedure.
> If I grant this new user membership to db_datareader, he gets select from
> any user table.
> If I grant this new user membership to db_datawriter, he gets insert,
> update, delete to any user table.
> So far, so good. Now the problem -
> What role can I grant that would allow this user to be able to run any
> stored procedure? Note that my manager will not let me grant him db_owner,
as
> this gives him too much rights.
> And what every I do to give him the above rights, he can't be allowed to
> create tables, create views or create any stored procedures
> I could do this manually through the GUI, but with over 200 stored procs p
er
> table, this is too time consuming. I could script, but this is also someth
ing
> labor intensive, am looking for an easier way.
> Thank you in advance.
> Sam|||Create a new role in each database.
Grant the appropriate permissions to this role. (I know it's time
consuming, but it's a one time deal).
Then add your new users to this role in each db.
If you check around, there have been several scripts already created that
will grant permissions. You just need to tweak them a bit. My guess is
that in an hour worth of your time, you will have the script completed.
This newsgroup has had several posted to it within the last two weeks.
Rick Sawtell
MCT, MCSD, MCDBA
"Sam" <Sam@.discussions.microsoft.com> wrote in message
news:E7839D71-7EC7-47A0-96DF-4A62DAEED1D0@.microsoft.com...
> I have a production server with a few databases. Each database has about
> 100-150 tables, and 200 or so Stored Procs.
> I have a new user john that gets created on each database. I need to give
> him the ability to select, insert, update and delete from any user table,
and
> to be able to execute any Stored Procedure.
> If I grant this new user membership to db_datareader, he gets select from
> any user table.
> If I grant this new user membership to db_datawriter, he gets insert,
> update, delete to any user table.
> So far, so good. Now the problem -
> What role can I grant that would allow this user to be able to run any
> stored procedure? Note that my manager will not let me grant him db_owner,
as
> this gives him too much rights.
> And what every I do to give him the above rights, he can't be allowed to
> create tables, create views or create any stored procedures
> I could do this manually through the GUI, but with over 200 stored procs
per
> table, this is too time consuming. I could script, but this is also
something
> labor intensive, am looking for an easier way.
> Thank you in advance.
> Sam

Granting permissions

I have a production server with a few databases. Each database has about
100-150 tables, and 200 or so Stored Procs.
I have a new user john that gets created on each database. I need to give
him the ability to select, insert, update and delete from any user table, and
to be able to execute any Stored Procedure.
If I grant this new user membership to db_datareader, he gets select from
any user table.
If I grant this new user membership to db_datawriter, he gets insert,
update, delete to any user table.
So far, so good. Now the problem -
What role can I grant that would allow this user to be able to run any
stored procedure? Note that my manager will not let me grant him db_owner, as
this gives him too much rights.
And what every I do to give him the above rights, he can't be allowed to
create tables, create views or create any stored procedures
I could do this manually through the GUI, but with over 200 stored procs per
table, this is too time consuming. I could script, but this is also something
labor intensive, am looking for an easier way.
Thank you in advance.
SamThink I found the solution. For each table, use the:
DENY CREATE VIEW to john
DENY CREATE TABLE to john
DENY CREATE SP to john
Still not sure how to give a user rights to execute all Stored Procedures,
though.
Sam
"Sam" wrote:
> I have a production server with a few databases. Each database has about
> 100-150 tables, and 200 or so Stored Procs.
> I have a new user john that gets created on each database. I need to give
> him the ability to select, insert, update and delete from any user table, and
> to be able to execute any Stored Procedure.
> If I grant this new user membership to db_datareader, he gets select from
> any user table.
> If I grant this new user membership to db_datawriter, he gets insert,
> update, delete to any user table.
> So far, so good. Now the problem -
> What role can I grant that would allow this user to be able to run any
> stored procedure? Note that my manager will not let me grant him db_owner, as
> this gives him too much rights.
> And what every I do to give him the above rights, he can't be allowed to
> create tables, create views or create any stored procedures
> I could do this manually through the GUI, but with over 200 stored procs per
> table, this is too time consuming. I could script, but this is also something
> labor intensive, am looking for an easier way.
> Thank you in advance.
> Sam|||Create a new role in each database.
Grant the appropriate permissions to this role. (I know it's time
consuming, but it's a one time deal).
Then add your new users to this role in each db.
If you check around, there have been several scripts already created that
will grant permissions. You just need to tweak them a bit. My guess is
that in an hour worth of your time, you will have the script completed.
This newsgroup has had several posted to it within the last two weeks.
Rick Sawtell
MCT, MCSD, MCDBA
"Sam" <Sam@.discussions.microsoft.com> wrote in message
news:E7839D71-7EC7-47A0-96DF-4A62DAEED1D0@.microsoft.com...
> I have a production server with a few databases. Each database has about
> 100-150 tables, and 200 or so Stored Procs.
> I have a new user john that gets created on each database. I need to give
> him the ability to select, insert, update and delete from any user table,
and
> to be able to execute any Stored Procedure.
> If I grant this new user membership to db_datareader, he gets select from
> any user table.
> If I grant this new user membership to db_datawriter, he gets insert,
> update, delete to any user table.
> So far, so good. Now the problem -
> What role can I grant that would allow this user to be able to run any
> stored procedure? Note that my manager will not let me grant him db_owner,
as
> this gives him too much rights.
> And what every I do to give him the above rights, he can't be allowed to
> create tables, create views or create any stored procedures
> I could do this manually through the GUI, but with over 200 stored procs
per
> table, this is too time consuming. I could script, but this is also
something
> labor intensive, am looking for an easier way.
> Thank you in advance.
> Sam

granting permission

Want to grant permission for multiple tables to a user or role. is there a w
ay to do this with transact-SQL.
Please help!!!!!
Thanks.
A.SYes, look up GRANT in Books Online.
"J C" <anonymous@.discussions.microsoft.com> wrote in message
news:96B8B085-4A59-45B5-B2F6-1FF3336628FC@.microsoft.com...
> Want to grant permission for multiple tables to a user or role. is there a
way to do this with transact-SQL.
> Please help!!!!!
> Thanks.
> A.Ssql

Grant Views to User

Hi all,

I have a question on the grant issue. I have created a view base on three tables, and I grant 'select' to the user to see the views I have created. But when the user select this views, it say :
"SELECT permission denied on object 'Table1', database 'XX', owner 'XX'.
SELECT permission denied on object 'Table2', database 'XX', owner 'XX'.
SELECT permission denied on object 'Table3', database 'XX', owner 'XX'."

I grant this three table to the user, and it run successfully.

Question is whether is there a way I can let user run the views but without seeing the three tables? or is there a way to hide from user?

Thanks in advance.

Are the tables in another database?

If the tables are in another database than the user, see if this applies (from BOL)

Cross-database permissions are not allowed; permissions can be granted only to users in the current database for objects and statements in the current database. If a user needs permissions to objects in another database, create the user account in the other database, or grant the user account access to the other database, as well as the current database.

/Kenneth

|||

Hi Kenneth,

The views and tables are in the same database. But even in same database it give me the same problem that I have mention in my first post on this topic.

Thanks.

|||Are the views and the table owned by different users ? If they are owned by different users then you should see this error, otherwise it will be a security violation.|||

Hi,

Ya, the tables is created using one user account and the views is created by another user account.

So you mean that I need to create the views using the user account which create the tables? Or is there any other way to solve this?

Thanks.

|||

The 'old way' to avoid this kind of problems (broken ownership chains), is to have all objects in the database
being owned by the same user - eg 'dbo'

The easy way to accomplish that, is to always create objects with two-part names (dbo.myNewtable, dbo.myProc etc..)

/Kenneth

Grant Views to User

Hi all,

I have a question on the grant issue. I have created a view base on three tables, and I grant 'select' to the user to see the views I have created. But when the user select this views, it say :
"SELECT permission denied on object 'Table1', database 'XX', owner 'XX'.
SELECT permission denied on object 'Table2', database 'XX', owner 'XX'.
SELECT permission denied on object 'Table3', database 'XX', owner 'XX'."

I grant this three table to the user, and it run successfully.

Question is whether is there a way I can let user run the views but without seeing the three tables? or is there a way to hide from user?

Thanks in advance.

Are the tables in another database?

If the tables are in another database than the user, see if this applies (from BOL)

Cross-database permissions are not allowed; permissions can be granted only to users in the current database for objects and statements in the current database. If a user needs permissions to objects in another database, create the user account in the other database, or grant the user account access to the other database, as well as the current database.

/Kenneth

|||

Hi Kenneth,

The views and tables are in the same database. But even in same database it give me the same problem that I have mention in my first post on this topic.

Thanks.

|||Are the views and the table owned by different users ? If they are owned by different users then you should see this error, otherwise it will be a security violation.|||

Hi,

Ya, the tables is created using one user account and the views is created by another user account.

So you mean that I need to create the views using the user account which create the tables? Or is there any other way to solve this?

Thanks.

|||

The 'old way' to avoid this kind of problems (broken ownership chains), is to have all objects in the database
being owned by the same user - eg 'dbo'

The easy way to accomplish that, is to always create objects with two-part names (dbo.myNewtable, dbo.myProc etc..)

/Kenneth

grant total for hide duplicate...

for my query in dataset after join few tables, i got following data:
ItemNo Area Barcode Total
1 J 12345 15
1 J 2222 15
in report, i use hide duplicate and displayed like:
ItemNo Area Barcode Total
1 J 12345 15
2222
total: 30
the problem is the total...what i wanted is 15 and no 30.i use
"=Round(sum(Fields!Total.Value,"ItemClass"),3),3" in that textbox expression.
what should i do to get the firgue what i wanted.....
i really appreciate your helps...thank a lot
--
thanksWhat you should do is to have 15 on both and the total is 30. instead if you
show just 15 and total 30, it is a kind of misleading...
so you can hide textbox wise all the values except total.
Amarnath
"kevintts" wrote:
> for my query in dataset after join few tables, i got following data:
> ItemNo Area Barcode Total
> 1 J 12345 15
> 1 J 2222 15
> in report, i use hide duplicate and displayed like:
> ItemNo Area Barcode Total
> 1 J 12345 15
> 2222
> total: 30
> the problem is the total...what i wanted is 15 and no 30.i use
> "=Round(sum(Fields!Total.Value,"ItemClass"),3),3" in that textbox expression.
> what should i do to get the firgue what i wanted.....
> i really appreciate your helps...thank a lot
> --
> thanks|||thanks Amarnath, but what i want is hide or minus the total and the total
become 15.
let say:
ItemNo Area Barcode Total
1 J 12345 15
1 J 2222 15
2 K 54321 20
i got:
ItemNo Area Barcode Total
1 J 12345 15
2222
2 K 54321 20
total: 50
what i wanted:
ItemNo Area Barcode Total
1 J 12345 15
2222
2 K 54321 20
total: 35
The value for '2222' still in the group. any thing wrong with the
exspression"=Round(sum(Fields!Total.Value,"ItemClass"),3),3"?
thanks a lot...
"Amarnath" wrote:
> What you should do is to have 15 on both and the total is 30. instead if you
> show just 15 and total 30, it is a kind of misleading...
> so you can hide textbox wise all the values except total.
> Amarnath
>
> "kevintts" wrote:
> > for my query in dataset after join few tables, i got following data:
> > ItemNo Area Barcode Total
> > 1 J 12345 15
> > 1 J 2222 15
> >
> > in report, i use hide duplicate and displayed like:
> > ItemNo Area Barcode Total
> > 1 J 12345 15
> > 2222
> > total: 30
> >
> > the problem is the total...what i wanted is 15 and no 30.i use
> > "=Round(sum(Fields!Total.Value,"ItemClass"),3),3" in that textbox expression.
> >
> > what should i do to get the firgue what i wanted.....
> > i really appreciate your helps...thank a lot
> >
> > --
> > thanks|||Expression is ok but since the text box is hidden, but the value exists so it
sums up.
If possible can you get this through query. e.g the values not present
should be null. in your case '2222' should have Total as Null. so when you
sum it sums up exactly.
Amarnath
"RE: grant total for hide duplicate..." wrote:
> thanks Amarnath, but what i want is hide or minus the total and the total
> become 15.
> let say:
> ItemNo Area Barcode Total
> 1 J 12345 15
> 1 J 2222 15
> 2 K 54321 20
> i got:
> ItemNo Area Barcode Total
> 1 J 12345 15
> 2222
> 2 K 54321 20
> total: 50
> what i wanted:
> ItemNo Area Barcode Total
> 1 J 12345 15
> 2222
> 2 K 54321 20
> total: 35
> The value for '2222' still in the group. any thing wrong with the
> exspression"=Round(sum(Fields!Total.Value,"ItemClass"),3),3"?
> thanks a lot...
> "Amarnath" wrote:
> > What you should do is to have 15 on both and the total is 30. instead if you
> > show just 15 and total 30, it is a kind of misleading...
> >
> > so you can hide textbox wise all the values except total.
> >
> > Amarnath
> >
> >
> > "kevintts" wrote:
> >
> > > for my query in dataset after join few tables, i got following data:
> > > ItemNo Area Barcode Total
> > > 1 J 12345 15
> > > 1 J 2222 15
> > >
> > > in report, i use hide duplicate and displayed like:
> > > ItemNo Area Barcode Total
> > > 1 J 12345 15
> > > 2222
> > > total: 30
> > >
> > > the problem is the total...what i wanted is 15 and no 30.i use
> > > "=Round(sum(Fields!Total.Value,"ItemClass"),3),3" in that textbox expression.
> > >
> > > what should i do to get the firgue what i wanted.....
> > > i really appreciate your helps...thank a lot
> > >
> > > --
> > > thanks

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 for all tables

Is there a way to grant select on all tables with in a database using TSQL?
I don't like the idea of having to do it for every table...I'm looking for a
short cut..
Thanks in advanceIn SQL 2000, you can add an user to db_datareader fixed role. This role has
Select permission on all tables & views.
Dejan Sarka, SQL Server MVP
Mentor
www.SolidQualityLearning.com
"owenmj" <owenmj@.discussions.microsoft.com> wrote in message
news:8CE5A325-08E3-400C-9DFB-DBF3187F02F1@.microsoft.com...
> Is there a way to grant select on all tables with in a database using
> TSQL?
> I don't like the idea of having to do it for every table...I'm looking for
> a
> short cut..
> Thanks in advance
>|||In SQL Server 2005, you can grant SELECT on the database to achieve this.
Dejan's reply provides the answer for SQL Server 2000.
Laurentiu Cristofor [MSFT]
Software Design Engineer
SQL Server Engine
http://blogs.msdn.com/lcris/
This posting is provided "AS IS" with no warranties, and confers no rights.
"owenmj" wrote:

> Is there a way to grant select on all tables with in a database using TSQL
?
> I don't like the idea of having to do it for every table...I'm looking for
a
> short cut..
> Thanks in advance
>|||Thank you!
"Dejan Sarka" wrote:

> In SQL 2000, you can add an user to db_datareader fixed role. This role ha
s
> Select permission on all tables & views.
> --
> Dejan Sarka, SQL Server MVP
> Mentor
> www.SolidQualityLearning.com
>
> "owenmj" <owenmj@.discussions.microsoft.com> wrote in message
> news:8CE5A325-08E3-400C-9DFB-DBF3187F02F1@.microsoft.com...
>
>sql

Grant rights to Truncate table to specific user 2000/2005

Hi
For certain "work" tables only, I would like to grant truncate table
ability.
In SQL2005 I can do
GRANT ALTER ON dbo.tbl#### TO myuser
Is there one syntax will work in both versions.
I don't mind if myuser is an owner of that table as long as dbo and
myuser can truncate it and other users can access it as dbo.tbl####.
I do not want to create myuser.tbl####
ThanksHi
In SQL Server if I remember well you need GRANT ALTER TABLE ON dbo.tbl####
TO myuser
<terryshamir@.gmail.com> wrote in message
news:1194962955.797517.209350@.19g2000hsx.googlegroups.com...
> Hi
> For certain "work" tables only, I would like to grant truncate table
> ability.
> In SQL2005 I can do
> GRANT ALTER ON dbo.tbl#### TO myuser
> Is there one syntax will work in both versions.
> I don't mind if myuser is an owner of that table as long as dbo and
> myuser can truncate it and other users can access it as dbo.tbl####.
> I do not want to create myuser.tbl####
> Thanks
>|||On 13 Nov, 14:14, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Hi
> In SQL Server if I remember well you need GRANT ALTER TABLE ON dbo.tbl####
> TO myuser
>
I tried that it returned "privilege alter table may not be granted or
revoked"
"incorrect syntax near 'on'".
Its a pain cos I want to clear these tables down quickly and don't
wanna fill log up. But do not wanna make this user dbo..|||I haven't used it yet, but I beleive you can write a proc that runs as dbo
and grant your user the rights to run the proc.
Should be easy enough to test.
<terryshamir@.gmail.com> wrote in message
news:1194962955.797517.209350@.19g2000hsx.googlegroups.com...
> Hi
> For certain "work" tables only, I would like to grant truncate table
> ability.
> In SQL2005 I can do
> GRANT ALTER ON dbo.tbl#### TO myuser
> Is there one syntax will work in both versions.
> I don't mind if myuser is an owner of that table as long as dbo and
> myuser can truncate it and other users can access it as dbo.tbl####.
> I do not want to create myuser.tbl####
> Thanks
>

GRANT permission to lots of tables and sp to db user

Is it possible to grant permissions (select, insert, delete, update, exec)
to a db user to all tables and all stored procedures in a specific db in an
easy (lazy!) way?
I mean, except for clicking in all permission checkboxes in Enterprise
Manager or writing a huge sql script like
grant select, insert, delete, update
on mytable1
to myuser
grant select, insert, delete, update
on mytable2
to myuser
...
grant exec
on mySP1
to myuser
grant exec
on mySP2
to myuser
...
?
Is there another way, like
GRANT select, insert, delete, update
on AllMyTables
to myuser
GRANT exec
on AllmySP
to myuser
?You can use a script like to example below to grant mass permissions
according to your requirements.
SET NOCOUNT ON
DECLARE @.GrantStatement nvarchar(500)
DECLARE @.LastError int
DECLARE GrantStatements CURSOR LOCAL FAST_FORWARD FOR
SELECT
N'GRANT ' +
CASE
WHEN OBJECTPROPERTY([ob].[id], 'IsUserTable') = 1 OR
OBJECTPROPERTY([ob].[id], 'IsView') = 1 THEN
N'SELECT, INSERT, UPDATE, DELETE'
WHEN OBJECTPROPERTY([ob].[id], 'IsTableFunction') = 1 OR
OBJECTPROPERTY([ob].[id], 'IsInlineFunction') = 1 THEN
N'SELECT'
WHEN OBJECTPROPERTY([ob].[id], 'IsScalarFunction') = 1 OR
OBJECTPROPERTY([ob].[id], 'IsProcedure') = 1 THEN
N'EXECUTE'
END +
N' ON ' +
QUOTENAME(USER_NAME([ob].[uid])) + '.' + QUOTENAME([ob].[nam
e]) +
N' TO MyRole'
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
"Siri" <Siri@.discussions.microsoft.com> wrote in message
news:51FFF23E-3543-4A4C-B6FE-8FCE4026CCA3@.microsoft.com...
> Is it possible to grant permissions (select, insert, delete, update, exec)
> to a db user to all tables and all stored procedures in a specific db in
> an
> easy (lazy!) way?
> I mean, except for clicking in all permission checkboxes in Enterprise
> Manager or writing a huge sql script like
> grant select, insert, delete, update
> on mytable1
> to myuser
> grant select, insert, delete, update
> on mytable2
> to myuser
> ...
> grant exec
> on mySP1
> to myuser
> grant exec
> on mySP2
> to myuser
> ...
> ?
> Is there another way, like
> GRANT select, insert, delete, update
> on AllMyTables
> to myuser
> GRANT exec
> on AllmySP
> to myuser
> ?
>|||Thank you very much! This really helped!
Siri
"Dan Guzman" wrote:

> You can use a script like to example below to grant mass permissions
> according to your requirements.
> SET NOCOUNT ON
> DECLARE @.GrantStatement nvarchar(500)
> DECLARE @.LastError int
> DECLARE GrantStatements CURSOR LOCAL FAST_FORWARD FOR
> SELECT
> N'GRANT ' +
> CASE
> WHEN OBJECTPROPERTY([ob].[id], 'IsUserTable') = 1 OR
> OBJECTPROPERTY([ob].[id], 'IsView') = 1 THEN
> N'SELECT, INSERT, UPDATE, DELETE'
> WHEN OBJECTPROPERTY([ob].[id], 'IsTableFunction') = 1 OR
> OBJECTPROPERTY([ob].[id], 'IsInlineFunction') = 1 THEN
> N'SELECT'
> WHEN OBJECTPROPERTY([ob].[id], 'IsScalarFunction') = 1 OR
> OBJECTPROPERTY([ob].[id], 'IsProcedure') = 1 THEN
> N'EXECUTE'
> END +
> N' ON ' +
> QUOTENAME(USER_NAME([ob].[uid])) + '.' + QUOTENAME([ob].&#
91;name]) +
> N' TO MyRole'
> 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
> "Siri" <Siri@.discussions.microsoft.com> wrote in message
> news:51FFF23E-3543-4A4C-B6FE-8FCE4026CCA3@.microsoft.com...
>
>

Grant Permission Statement

Can I use the Grant Statement to grant permissions to all tables in a
database.
I can only get this to work on individual tables?Simply add the user to the db_datareader role:
sp_addrolemember 'db_datareader', 'MyUser'
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Sarah Kingswell" <skingswell@.xonitek.co.uk> wrote in message
news:ugLANg1nGHA.2264@.TK2MSFTNGP04.phx.gbl...
Can I use the Grant Statement to grant permissions to all tables in a
database.
I can only get this to work on individual tables?|||Hi
SELECT 'GRANT SELECT ON [' + USER_NAME(uid) + '].[' + name + '] TO '
+
'[MyUser]'
FROM sysobjects
WHERE
type = 'U'
AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(USER_
NAME(uid)) + '.' +
QUOTENAME(name)), 'IsMSShipped') = 0
Copy-Paste the output into the QA and run it against a database
"Sarah Kingswell" <skingswell@.xonitek.co.uk> wrote in message
news:ugLANg1nGHA.2264@.TK2MSFTNGP04.phx.gbl...
> Can I use the Grant Statement to grant permissions to all tables in a
> database.
> I can only get this to work on individual tables?
>
>|||In 2005 you can do:
USE dbname
GRANT SELECT ON DATABASE::dbname TO UsrName
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sarah Kingswell" <skingswell@.xonitek.co.uk> wrote in message
news:ugLANg1nGHA.2264@.TK2MSFTNGP04.phx.gbl...
> Can I use the Grant Statement to grant permissions to all tables in a data
base.
> I can only get this to work on individual tables?
>
>