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:
Showing posts with label easily. Show all posts
Showing posts with label easily. Show all posts
Tuesday, March 27, 2012
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:
>
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
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
Sunday, February 19, 2012
GK - Blocking the unauthorized use of the SQL Management Console ( or third party tool
Hi to all,
Whenever SQL server is installed onto a server it can be accessed by the SQL
MC and easily registered for use. Whenever someone not authorized installs
the SQL MC or a third party tool with the same capabilities all database con
tent can be altered !! THIS
IS A VERY SERIOUS THREAD !
Is there a way to prevent this?
GKramer
The NetherlandsCan you please elaborate on your concerns? Only users who have been granted
access to SQL Server can connect and access database objects. When SQL
Server is initially installed, only members of the local Administrators
group can connect. Once you grant an account access to SQL Server, they can
use any tool but are limited by the SQL Server permissions you have granted.
Hope this helps.
Dan Guzman
SQL Server MVP
"Gkramer" <anonymous@.discussions.microsoft.com> wrote in message
news:2BDA7DD5-3FDE-45CC-BD95-BDCD8AD01BDE@.microsoft.com...
> Hi to all,
> Whenever SQL server is installed onto a server it can be accessed by the
SQL MC and easily registered for use. Whenever someone not authorized
installs the SQL MC or a third party tool with the same capabilities all
database content can be altered !! THIS IS A VERY SERIOUS THREAD !
> Is there a way to prevent this?
> GKramer
> The Netherlands
>
Whenever SQL server is installed onto a server it can be accessed by the SQL
MC and easily registered for use. Whenever someone not authorized installs
the SQL MC or a third party tool with the same capabilities all database con
tent can be altered !! THIS
IS A VERY SERIOUS THREAD !
Is there a way to prevent this?
GKramer
The NetherlandsCan you please elaborate on your concerns? Only users who have been granted
access to SQL Server can connect and access database objects. When SQL
Server is initially installed, only members of the local Administrators
group can connect. Once you grant an account access to SQL Server, they can
use any tool but are limited by the SQL Server permissions you have granted.
Hope this helps.
Dan Guzman
SQL Server MVP
"Gkramer" <anonymous@.discussions.microsoft.com> wrote in message
news:2BDA7DD5-3FDE-45CC-BD95-BDCD8AD01BDE@.microsoft.com...
> Hi to all,
> Whenever SQL server is installed onto a server it can be accessed by the
SQL MC and easily registered for use. Whenever someone not authorized
installs the SQL MC or a third party tool with the same capabilities all
database content can be altered !! THIS IS A VERY SERIOUS THREAD !
> Is there a way to prevent this?
> GKramer
> The Netherlands
>
Subscribe to:
Posts (Atom)