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

Got an expensive server, SQL is very slow on writes

Hi,

i am experiencing SQl write performance problems on a very shiny server. Got data files on a Raid 1+0, log files on a separate drive, all SCSI, Win2003 server, 6G RAM, 2 Xeon processors. I've created a small benchmarking program and run it on my desktop pc and this 'big' server. Here are the results:

Desktop: SQL server inserts: 78 Seconds, Direct writes to the harddisk(Just write a string to the file 10000 times): 13 seconds

SQLServer: SQL server inserts: 422 Seconds, Direct writes to the harddisk: 16 seconds

So, for some reason, my 'shiny' machine is 6 times slower on writes than my desktop. When i tried comparing the select performance, my shiny server is 10 times faster than my desktop.

Initially i had Raid5 on my server and it had poorer direct write performance but now, direct writes seem to be ok, so, i recon this is a problem related to SQL server.

What can i do to improve the insert performance?

Thanks in advanceYour performance should double by placing the database file and transaction log file on different drives.

HTH|||As i mentioned previously, i already have my data and log files in different physical drives but it it does not make a difference.
Playing around with Raid configurations i managed to significally improve the speed of direct writes to hardisk (not within SQL) but sql write speed still stayed the same. I have a feeling that this is either Win2003 server or sql server matter. Cannot find any info about it.

Had someone come accross the same problem?|||Maybe check if your RAID-controller has its writecache disabled. How did you test? Within Queryanalyzer or custom Tool.?|||I wrote a small c# utility to do the test. Tool does 2 types of test:

1. It runs 10,000 insert quieries against a simple table

2. It writes a string into the flat file using direct file access 10,000 times.

I thought that writecache might be an issue but my direct writes (test 2) perform very well (equal to my desktop pc, whereas test 1 is 6 times slower than on my desktop) so i think that bottleneck is not in disk/raid configuration but somewhere else.

Not sure where though:( Same thing happens with SQL7.

Currently trying to install Win2k server op.|||For anyone having the same kind of a problem, issue was resolved by by installing a battery backed write cache on the SCSI controller. Achieved 24 times speed boost at once.|||... though enabling write cache is not a good idea anyways since it can lead to data corruption, even during normal operation.|||I've paid £250 for this write cache chip, it's backup onboard battery will last for 72 hours. I am pretty confident that my cached data will be save. Besides that, apparently write caching with battery backup is microsoft's recommendation. (Q230785)|||It's not a matter of how long your battery lasts. I read that the datapages can get out of sync with write caching enabled which leads to corrupt data files. But not 100% sure could be an issue of SQL 7 only.

... maybe one of our friendly MVP-SQL-Gurus here can confirm or disproof that hardware write caching can be a problem? :)

- Moon

Good Tape software for SQL backups

We would like to backup some databases natively and move the bak files to a
tape server that has a tape drive DLT1 Dell 120T.What are some of the 3rd
party tape software options thats reasonably priced that i can use for the
tape autoloader and that can send me some reports daily about the success or
failure of the SQL backup files copy onto tape.Veritas.
Yovan
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OsBU8LOfDHA.616@.TK2MSFTNGP11.phx.gbl...
> We would like to backup some databases natively and move the bak files to
a
> tape server that has a tape drive DLT1 Dell 120T.What are some of the 3rd
> party tape software options thats reasonably priced that i can use for the
> tape autoloader and that can send me some reports daily about the success
or
> failure of the SQL backup files copy onto tape.
>
>

Good Question

I have some Lotus Notes files that I saved as Excel spreadsheets. I migrated
the files to SQL using DTS. One of the files has a table with 2 columns as
follows:
Room ID Number of Beds
201 3
202 2
I need to add a column to that table that will look into the combination of
each RoomID and Number Of Beds and will look like this:
Room ID
201A
201B
201C
202A
202B
So, because Room 201 has a capacity of 3 beds, it showed up as 201A, 201B
and 201C and so forth for the other rooms. What code I can use to accomplish
this?
Thanks a million
--
TSUse an auxiliary numbers table.
Example:
use northwind
go
create table t1 (
room_id int not null unique,
number_of_beds int not null check (number_of_beds between 1 and 5)
)
go
insert into t1 values(201, 3)
insert into t1 values(202, 2)
go
select
identity(int, 1, 1) as number
into
number
from
sysobjects as a
cross join
sysobjects as b
go
declare @.s varchar(255)
set @.s = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
select
ltrim(room_id) + substring(@.s, n.number, 1)
from
t1
inner join
number as n
on n.number <= t1.number_of_beds
order by
room_id,
n.number
go
drop table t1
go
drop table number
go
AMB
"TS" wrote:

> I have some Lotus Notes files that I saved as Excel spreadsheets. I migrat
ed
> the files to SQL using DTS. One of the files has a table with 2 columns as
> follows:
> Room ID Number of Beds
> 201 3
> 202 2
> I need to add a column to that table that will look into the combination o
f
> each RoomID and Number Of Beds and will look like this:
> Room ID
> 201A
> 201B
> 201C
> 202A
> 202B
> So, because Room 201 has a capacity of 3 beds, it showed up as 201A, 201B
> and 201C and so forth for the other rooms. What code I can use to accompli
sh
> this?
> Thanks a million
> --
> TS|||Hello TS,
CREATE TABLE [dbo].[Rooms] (
[RoomID] [int] NULL ,
[NumberofBeds] [int] NULL
) ON [PRIMARY]
GO
select * from Rooms
GO
RoomID NumberofBeds
-- --
200 3
201 4
202 2
select A.RoomID, B.Division
from Rooms A
Inner Join ( Select 'A' 'Division',1 'Sequence'
UNION ALL
select 'B',2
UNION ALL
select 'C',3
UNION ALL
select 'D',4
UNION ALL
select 'E',5
UNION ALL
select 'F',6 ) B
ON B.Sequence <= A.NumberofBeds
Thanks,
Gopi
"TS" <TS@.discussions.microsoft.com> wrote in message
news:07AF5AB5-BFD0-4E5D-90CE-316F267DAF4B@.microsoft.com...
>I have some Lotus Notes files that I saved as Excel spreadsheets. I
>migrated
> the files to SQL using DTS. One of the files has a table with 2 columns as
> follows:
> Room ID Number of Beds
> 201 3
> 202 2
> I need to add a column to that table that will look into the combination
> of
> each RoomID and Number Of Beds and will look like this:
> Room ID
> 201A
> 201B
> 201C
> 202A
> 202B
> So, because Room 201 has a capacity of 3 beds, it showed up as 201A, 201B
> and 201C and so forth for the other rooms. What code I can use to
> accomplish
> this?
> Thanks a million
> --
> TS|||Here is an example of creating a table with the two fields and populating th
e
standard values. Then modifying the table to add new column and updating th
e
value of that column depending on the number of rooms.
Create table #Table1
(
RoomID nvarchar (3),
NumOfBeds nvarchar (1)
)
Insert #Table1
values ('201','3')
Insert #Table1
values ('202','2')
Insert #Table1
values ('203','3')
Insert #Table1
values ('204','2')
select * from #table1
--Alter table to have GUID ID
Alter TABLE [#table1] ADD [RoomType] nvarchar (4)
GO
-- Update values for colum with conditions for number of beds
Update #Table1
Set Roomtype = RoomID+'A'
WHERE Numofbeds ='3'
Update #Table1
Set Roomtype = RoomID+'B'
WHERE Numofbeds ='2'
Select * from #Table1
Drop table #table1
Hope this helps guide you in the right direction.
"TS" wrote:

> I have some Lotus Notes files that I saved as Excel spreadsheets. I migrat
ed
> the files to SQL using DTS. One of the files has a table with 2 columns as
> follows:
> Room ID Number of Beds
> 201 3
> 202 2
> I need to add a column to that table that will look into the combination o
f
> each RoomID and Number Of Beds and will look like this:
> Room ID
> 201A
> 201B
> 201C
> 202A
> 202B
> So, because Room 201 has a capacity of 3 beds, it showed up as 201A, 201B
> and 201C and so forth for the other rooms. What code I can use to accompli
sh
> this?
> Thanks a million
> --
> TS|||Thanks a lot. Your code did exactly what I was looking for. Now the only
thing I need in order to finish the conversion is to include the description
next to the room id as follows:-
This is how the table looked like before applying your code:
Room Capacity Description
201 2 Small Single
202 1 Large Double
This is how the table looks like now after applying your code
RoomID
201A
201B
202A
What I need is to add another column to what I have now so the table will
look like this
RoomID Description
201A Small Single
201B Small Single
202A Large Double
What is the code for that.
Thank you for all your help.
TS
"Alejandro Mesa" wrote:
> Use an auxiliary numbers table.
> Example:
> use northwind
> go
> create table t1 (
> room_id int not null unique,
> number_of_beds int not null check (number_of_beds between 1 and 5)
> )
> go
> insert into t1 values(201, 3)
> insert into t1 values(202, 2)
> go
> select
> identity(int, 1, 1) as number
> into
> number
> from
> sysobjects as a
> cross join
> sysobjects as b
> go
> declare @.s varchar(255)
> set @.s = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
> select
> ltrim(room_id) + substring(@.s, n.number, 1)
> from
> t1
> inner join
> number as n
> on n.number <= t1.number_of_beds
> order by
> room_id,
> n.number
> go
> drop table t1
> go
> drop table number
> go
>
> AMB
>
> "TS" wrote:
>

Good Book on SQL Server Reporting Services Script

Hello, I recently discovered the rs.exe utility and .rss files. I have
looked at and enjoyed the sample scripts, but was wondering if there were any
books on this subject that anyone has found useful?
Thanks in advance!
TimTim,
That is a hard one, as all of the books out there do not talk much to the
rs.exe utility, but there is one site www.sqldbatips.com has a tool that can
help out a ton: Reporting Services Scripter.
I also have a method to debug rss scripts.
In Visual Studio
1. Create a new Visual Basic Console Application Project: name it RSDebug
2. In the Soultion Explorer window - right click on the RSDebug Project and
select the Add Web Reference option.
3. In the URL text box add:
http://servername/reportserver/reportservice2005.asmx
4. Click Go Command Button (This step takes a while)
5. In the Web Reference Name text box add SSRSWebService
6. Click Add Reference Command Button
7. In the Code Window above the Module Module1 add:
Imports RSDebug.SSRSWebService
Imports System.Web.Services.Protocols
8. Below the Module Module1 add: Public rs As New ReportingService2005
9. Paste your script code in the Sub Main() procedure
10. Set your breakpoint and go.
Example: (In this example System.IO is needed due to the use of the
MemoryStream object)
Imports RSDebug.SSRSWebService
Imports System.Web.Services.Protocols
Imports System.IO ' This was added because MemoryStream was used
Module Module1
Public rs As New ReportingService2005
Sub Main()
Dim strObjectName As String = "Report Name"
Dim strObjectPath As String = "/Application"
Dim strObjectFullPath As String = strObjectPath & "/" & strObjectName
Dim strLocalFile As String = "Report Name.rdl"
Dim strLocalPath As String = "C:\"
Dim strLocalFullPath As String = strLocalPath & strLocalFile
Dim objReportDefinition As Byte()
Dim objMemoryStream As MemoryStream
Dim objDocument As New System.Xml.XmlDocument()
Try
rs.Credentials = System.Net.CredentialCache.DefaultCredentials
objReportDefinition = rs.GetReportDefinition(strObjectFullPath)
objMemoryStream = New MemoryStream(objReportDefinition)
objDocument.Load(objMemoryStream)
objDocument.Save(strLocalFullPath)
Console.WriteLine("Standard: Report downloaded successfully.")
Catch e As SoapException
Console.WriteLine("Error: " +
e.Detail.Item("ErrorCode").InnerText + " (" +
e.Detail.Item("Message").InnerText + ")")
End Try
End Sub
End Module|||Thanks very much Reeves, this is great information.
Tim
"Reeves Smith" wrote:
> Tim,
> That is a hard one, as all of the books out there do not talk much to the
> rs.exe utility, but there is one site www.sqldbatips.com has a tool that can
> help out a ton: Reporting Services Scripter.
> I also have a method to debug rss scripts.
> In Visual Studio
> 1. Create a new Visual Basic Console Application Project: name it RSDebug
> 2. In the Soultion Explorer window - right click on the RSDebug Project and
> select the Add Web Reference option.
> 3. In the URL text box add:
> http://servername/reportserver/reportservice2005.asmx
> 4. Click Go Command Button (This step takes a while)
> 5. In the Web Reference Name text box add SSRSWebService
> 6. Click Add Reference Command Button
> 7. In the Code Window above the Module Module1 add:
> Imports RSDebug.SSRSWebService
> Imports System.Web.Services.Protocols
> 8. Below the Module Module1 add: Public rs As New ReportingService2005
> 9. Paste your script code in the Sub Main() procedure
> 10. Set your breakpoint and go.
>
> Example: (In this example System.IO is needed due to the use of the
> MemoryStream object)
>
> Imports RSDebug.SSRSWebService
> Imports System.Web.Services.Protocols
>
> Imports System.IO ' This was added because MemoryStream was used
>
> Module Module1
> Public rs As New ReportingService2005
>
> Sub Main()
> Dim strObjectName As String = "Report Name"
> Dim strObjectPath As String = "/Application"
> Dim strObjectFullPath As String = strObjectPath & "/" & strObjectName
> Dim strLocalFile As String = "Report Name.rdl"
> Dim strLocalPath As String = "C:\"
> Dim strLocalFullPath As String = strLocalPath & strLocalFile
> Dim objReportDefinition As Byte()
> Dim objMemoryStream As MemoryStream
> Dim objDocument As New System.Xml.XmlDocument()
>
> Try
> rs.Credentials = System.Net.CredentialCache.DefaultCredentials
> objReportDefinition = rs.GetReportDefinition(strObjectFullPath)
> objMemoryStream = New MemoryStream(objReportDefinition)
> objDocument.Load(objMemoryStream)
> objDocument.Save(strLocalFullPath)
> Console.WriteLine("Standard: Report downloaded successfully.")
> Catch e As SoapException
> Console.WriteLine("Error: " +
> e.Detail.Item("ErrorCode").InnerText + " (" +
> e.Detail.Item("Message").InnerText + ")")
> End Try
> End Sub
> End Module|||On Aug 7, 7:58 pm, TimS <timsts...@.msn.com(donotspam)> wrote:
> Thanks very much Reeves, this is great information.
> Tim
> "Reeves Smith" wrote:
> > Tim,
> > That is a hard one, as all of the books out there do not talk much to the
> > rs.exe utility, but there is one sitewww.sqldbatips.comhas a tool that can
> > help out a ton: Reporting Services Scripter.
> > I also have a method to debug rss scripts.
> > In Visual Studio
> > 1. Create a new Visual Basic Console Application Project: name it RSDebug
> > 2. In the Soultion Explorer window - right click on the RSDebug Project and
> > select the Add Web Reference option.
> > 3. In the URL text box add:
> >http://servername/reportserver/reportservice2005.asmx
> > 4. Click Go Command Button (This step takes a while)
> > 5. In the Web Reference Name text box add SSRSWebService
> > 6. Click Add Reference Command Button
> > 7. In the Code Window above the Module Module1 add:
> > Imports RSDebug.SSRSWebService
> > Imports System.Web.Services.Protocols
> > 8. Below the Module Module1 add: Public rs As New ReportingService2005
> > 9. Paste your script code in the Sub Main() procedure
> > 10. Set your breakpoint and go.
> > Example: (In this example System.IO is needed due to the use of the
> > MemoryStream object)
> > Imports RSDebug.SSRSWebService
> > Imports System.Web.Services.Protocols
> > Imports System.IO ' This was added because MemoryStream was used
> > Module Module1
> > Public rs As New ReportingService2005
> > Sub Main()
> > Dim strObjectName As String = "Report Name"
> > Dim strObjectPath As String = "/Application"
> > Dim strObjectFullPath As String = strObjectPath & "/" & strObjectName
> > Dim strLocalFile As String = "Report Name.rdl"
> > Dim strLocalPath As String = "C:\"
> > Dim strLocalFullPath As String = strLocalPath & strLocalFile
> > Dim objReportDefinition As Byte()
> > Dim objMemoryStream As MemoryStream
> > Dim objDocument As New System.Xml.XmlDocument()
> > Try
> > rs.Credentials = System.Net.CredentialCache.DefaultCredentials
> > objReportDefinition = rs.GetReportDefinition(strObjectFullPath)
> > objMemoryStream = New MemoryStream(objReportDefinition)
> > objDocument.Load(objMemoryStream)
> > objDocument.Save(strLocalFullPath)
> > Console.WriteLine("Standard: Report downloaded successfully.")
> > Catch e As SoapException
> > Console.WriteLine("Error: " +
> > e.Detail.Item("ErrorCode").InnerText + " (" +
> > e.Detail.Item("Message").InnerText + ")")
> > End Try
> > End Sub
> > End Module
I ordered Microsoft's new books for the MCITP certification program in
Business Intelligence. One covers the 70-445 exam and the other one
the 70-446 exam. I'm sure you'll find them very useful (once they're
published anyways!).
http://www.amazon.com/MCTS-Self-Paced-Training-Exam-70-445/dp/0735623414/ref=pd_bbs_1/102-9922975-8902553?ie=UTF8&s=books&qid=1186575878&sr=8-1
http://www.amazon.com/MCITP-Self-Paced-Training-Exam-70-446/dp/0735623848/ref=pd_bbs_2/102-9922975-8902553?ie=UTF8&s=books&qid=1186575878&sr=8-2

global vars in script files ?

Is it possible to declare a global variable in a SQL script ... or some
workaround ?
[ContentDB].[dbo].[PageTypes].[ptId] IDENTITY(int, 1,1)
In the following script, the local var @.ptId is lost once a "GO" is
executed.
USE [ContentDB]
GO
INSERT INTO [dbo].[PageTypes]
([ptName]
,[ptPath]
,[ptParamName])
VALUES
('unused'
,'/redirect.aspx'
,'url')
DECLARE @.ptId int
SET @.ptId = @.@.IDENTITY
.
.
.
.
<lots and lots of other SQL>
.
.
.
.
GO
.
.
.
.
<lots and lots of other SQL>
.
.
.There are no global variables in TSQL. You can use a temp table for this, or
check out SET
CONTEXT_INFO.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"John A Grandy" <johnagrandy-at-yahoo-dot-com> wrote in message
news:eYtWs8JRGHA.1688@.TK2MSFTNGP11.phx.gbl...
> Is it possible to declare a global variable in a SQL script ... or some wo
rkaround ?
> [ContentDB].[dbo].[PageTypes].[ptId] IDENTITY(int, 1,1)
> In the following script, the local var @.ptId is lost once a "GO" is execut
ed.
> USE [ContentDB]
> GO
> INSERT INTO [dbo].[PageTypes]
> ([ptName]
> ,[ptPath]
> ,[ptParamName])
> VALUES
> ('unused'
> ,'/redirect.aspx'
> ,'url')
> DECLARE @.ptId int
> SET @.ptId = @.@.IDENTITY
> .
> .
> .
> .
> <lots and lots of other SQL>
> .
> .
> .
> .
> GO
> .
> .
> .
> .
> <lots and lots of other SQL>
> .
> .
> .
>

GLOBAL CUBE slicing and security challenge

Hi Guys,

Context
I'm working with SSAS 2005 SP2. I'm quite new at it and I'm struggling with an interesting challenge.

I have to create cube files so that Sales Representatives can browse their data offline. A Sales Representative can see his own sales data but also the sales data of the other sales representative with whom he is sharing customers.

For this purpose, I want to use the CREATE GLOBAL CUBE-statement.

I don't know how to implement the last requirement. In T-SQL, it will look as follows for the Sales Reps named "J. Do".

WITH Sales_Reps_Cust(CUSTOMER_ID) AS
(
SELECT F_S.CUSTOMER_ID
FROM FACT.SALES F_S
INNER JOIN DIM.CUSTOMER D_C
ON F_S.CUSTOMER_ID = D_C.CUSTOMER_ID
INNER JOIN DIM.SALES_REPRESENTATIVE D_SR
ON F_S.SALES_REPRESENTATIVE_ID = D_SR.SALES_REPRESENTATIVE_ID
WHERE SALES_REPRESENTATIVE_NAME = 'J. Do'
GROUP BY F_S.CUSTOMER_ID
)
SELECT FS.*
FROM FACT.SALES FS

INNER JOIN Sales_Reps_Cust D_C
ON F_S.CUSTOMER_ID = D_C.CUSTOMER_ID

Questions
In fact, I create a set of all customers of 'J. Do' and then I want to use this set to slice my cube. How can I use this way of thinking in order to make my cube file with CREATE GLOBAL CUBE?

Please advice...

Kind regards,
Lohic Beneyzet-Jouy

Hi everybody,

This issue is solved in my other threads "PLEASE HELP!!!! MDX-EXPERTS!!! LOCAL CUBE SECURITY".

Kind regards,

Lohic Beneyzet-Jouy

GLOBAL CUBE slicing and security challenge

Hi Guys,

Context
I'm working with SSAS 2005 SP2. I'm quite new at it and I'm struggling with an interesting challenge.

I have to create cube files so that Sales Representatives can browse their data offline. A Sales Representative can see his own sales data but also the sales data of the other sales representative with whom he is sharing customers.

For this purpose, I want to use the CREATE GLOBAL CUBE-statement.

I don't know how to implement the last requirement. In T-SQL, it will look as follows for the Sales Reps named "J. Do".

WITH Sales_Reps_Cust(CUSTOMER_ID) AS
(
SELECT F_S.CUSTOMER_ID
FROM FACT.SALES F_S
INNER JOIN DIM.CUSTOMER D_C
ON F_S.CUSTOMER_ID = D_C.CUSTOMER_ID
INNER JOIN DIM.SALES_REPRESENTATIVE D_SR
ON F_S.SALES_REPRESENTATIVE_ID = D_SR.SALES_REPRESENTATIVE_ID
WHERE SALES_REPRESENTATIVE_NAME = 'J. Do'
GROUP BY F_S.CUSTOMER_ID
)
SELECT FS.*
FROM FACT.SALES FS

INNER JOIN Sales_Reps_Cust D_C
ON F_S.CUSTOMER_ID = D_C.CUSTOMER_ID

Questions
In fact, I create a set of all customers of 'J. Do' and then I want to use this set to slice my cube. How can I use this way of thinking in order to make my cube file with CREATE GLOBAL CUBE?

Please advice...

Kind regards,
Lohic Beneyzet-Jouy

Hi everybody,

This issue is solved in my other threads "PLEASE HELP!!!! MDX-EXPERTS!!! LOCAL CUBE SECURITY".

Kind regards,

Lohic Beneyzet-Jouy