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

Good Query for Paging?

Hi,

I am wondering what is the suggested query to use when I want to page through a bunch of data. I figure this is a pretty common situation so there should be some good ways to do this.

Basically I have a fixed page size and everytime a new one is requested, I just want to get those records. So if my page size is 10, for the first one I want to get records 1-10 of an ordered set, for page 2 I want to get records 11-20, etc.

The problem is I can only see "TOP" as an option, but that gives me the *first* n records. What if I want a set of records in the middle of the set?

I am also thinking some kind of subquery with an identity column, and then selecting based on the identity column may do the trick.

I would appreciate any any good solutions for this!

Thanks.select top 10 *
from (select top 20 * from tbl order by id) as a
order by id desc

For a variable page do it in dynamic sql.

or temp table solution - could use table variable)
create table #a (id int identity(1,1), ...)
set rowcount = @.maxrecs
insert #a select ...
set rowcount 0
select top 10 * from #a order by id desc
drop table #a

Good Old Event ID: 18456 - Can't figure this one out!

Hi everyone,

I've got a clean SQL Server 2005 Enterprise Edition installation, with a domain account configured as the service account. My application log is now flooded with the following error message:

Event Type: Failure Audit
Event Source: MSSQLSERVER
Event Category: (4)
Event ID: 18456
Date: 5/25/2007
Time: 1:57:00 AM
User: TNG\TNG-SQL_Service
Computer: TNG-MOSSDB01
Description:
Login failed for user 'TNG\TNG-SQL_Service'. [CLIENT: <local machine>]

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
Data:
0000: 18 48 00 00 0e 00 00 00 .H......
0008: 0d 00 00 00 54 00 4e 00 ....T.N.
0010: 47 00 2d 00 4d 00 4f 00 G.-.M.O.
0018: 53 00 53 00 44 00 42 00 S.S.D.B.
0020: 30 00 31 00 00 00 07 00 0.1.....
0028: 00 00 6d 00 61 00 73 00 ..m.a.s.
0030: 74 00 65 00 72 00 00 00 t.e.r...

And when I open up the SQL logs, I'm getting two errors - one is essentially the same as the above, and the other is:

Date 5/25/2007 1:51:00 AM
Log SQL Server (Current - 5/25/2007 1:51:00 AM)

Source Logon

Message
Error: 18456, Severity: 14, State: 16.

Now I've seen all kinds of posts about granting rights to master and such - and I have, but these errors are not going away. I've gone so far as to create new service accounts and even switch over to the local system account - and the error still occurs. Does anyone have any insight into other routes / approaches I can take with this?

Thank you,

Chris

From BOL topic Troubleshooting: Login failed for user 'x'

Additional Error Information

To increase security, the error message that is returned to the client deliberately hides the nature of the authentication error. However, in the SQL Server error log, a corresponding error contains an error state that maps to an authentication failure condition. Compare the error state to the following list to determine the reason for the login failure.

State Description

2

User ID is not valid.

5

User ID is not valid.

6

An attempt was made to use a Windows login name with SQL Server Authentication.

7

Login is disabled, and the password is incorrect.

8

The password is incorrect.

9

Password is not valid.

11

Login is valid, but server access failed.

12

Login is valid login, but server access failed.

18

Password must be changed.

Other error states exist and signify an unexpected internal processing error.

You may need to contact Support for this.

HTH,

-Steven Gott

SDE/T

SQL Server

|||

Hi Steve,

Do you by chance have any information on State = 16?

Thanks,
Chris

|||

It is related to not being able to establish a database session.

Check the default_database for your account and make sure it is master.

How are you changing the service account? Please make sure you use the tools provided in sql server because usinging windows scm will not do the whole job.

HTH,

-Steven Gott

SDE/T

SQL Server

|||

Steve,

The default_database for the account is master, and the service accounts were set through the SQL Server Configuration Manager. I went back into it and reset all of them just in case - but still getting the error. Just doesn't make any sense. In fact, I'm also seeing the same error for another account - the service account I use with SharePoint (residing on another server). Same exact errors in the Event Log for the Server & the SQL Logs.

Are there any statements I can run against the master to see if maybe an entry is missing? I'm drawing at straws with this.

Thanks,
Chris

|||

You can try the following statements to see if there is anything unexpected:

SELECT name, principal_id, type_desc, is_disabled, default_database_name, default_language_name

FROM sys.server_principals

WHERE name = 'login_name'

go

SELECT name, database_id, user_access_desc, state_desc, is_in_standby

FROM sys.databases

WHERE name = 'default_db_name'

go

SELECT suser_name(role_principal_id), suser_name(member_principal_id)

FROM sys.server_role_members

WHERE member_principal_id = suser_id( 'login_name' )

go

SELECT class_desc, suser_name(grantee_principal_id), permission_name, state_desc

FROM sys.server_permissions

WHERE grantee_principal_id = suser_id( 'login_name' )

Go

Pay attention to the default database, if the login is disabled and if it has at least CONNECT SERVER permission.

I hope this helps,

-Raul Garcia

SDE/T

SQL Server Engine