I created a new Role called GRPSELECT. I want to give this group the abilit
y to only run the SELECT statement on all the tables in my database. Right
now I can run "GRANT SELECT ON table TO GRPSELECT" in Query Analyzer, but it
only allows me to do this
to one table at a time.
How can I accomplish this to all 600 tables in the database without manually
typing in every single table?
Thanks in advance!Instead of creating a new role you can add these users/group to
db_datareader fixed db role.
Members of db_datareader fixed db role have select permissions on any
objects in the db.
Thanks,
Lyudmila Fokina
Please do not send e-mail directly to this alias. This alias is for
newsgroup purposes only
Disclaimer: This posting is provided "AS IS" with no warranties, and confers
no rights.
"Jon Jones" <Jon Jones@.discussions.microsoft.com> wrote in message
news:96F7208A-EA64-467A-8AD2-5477782ED32E@.microsoft.com...
> I created a new Role called GRPSELECT. I want to give this group the
ability to only run the SELECT statement on all the tables in my database.
Right now I can run "GRANT SELECT ON table TO GRPSELECT" in Query Analyzer,
but it only allows me to do this to one table at a time.
> How can I accomplish this to all 600 tables in the database without
manually typing in every single table?
> Thanks in advance!|||Jon,
You can use Transact-SQL to generate the script for you:
Ex:
SELECT 'GRANT SELECT ON ' + so.name + ' TO GRPSELECT'
FROM dbo.sysobjects so
WHERE so.type = 'u'
This output can then be copied.
Randy Dyess
"Jon Jones" wrote:
> I created a new Role called GRPSELECT. I want to give this group the ability to o
nly run the SELECT statement on all the tables in my database. Right now I can run
"GRANT SELECT ON table TO GRPSELECT" in Query Analyzer, but it only allows me to do
thi
s to one table at a time.
> How can I accomplish this to all 600 tables in the database without manual
ly typing in every single table?
> Thanks in advance!sql
Showing posts with label ability. Show all posts
Showing posts with label ability. Show all posts
Thursday, March 29, 2012
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. 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:
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:
>
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
Grant rights to Truncate table to specific user 2000/2005
Hi
For certain "work" tables only, I would like to grant truncate table
ability.
In SQL2005 I can do
GRANT ALTER ON dbo.tbl#### TO myuser
Is there one syntax will work in both versions.
I don't mind if myuser is an owner of that table as long as dbo and
myuser can truncate it and other users can access it as dbo.tbl####.
I do not want to create myuser.tbl####
ThanksHi
In SQL Server if I remember well you need GRANT ALTER TABLE ON dbo.tbl####
TO myuser
<terryshamir@.gmail.com> wrote in message
news:1194962955.797517.209350@.19g2000hsx.googlegroups.com...
> Hi
> For certain "work" tables only, I would like to grant truncate table
> ability.
> In SQL2005 I can do
> GRANT ALTER ON dbo.tbl#### TO myuser
> Is there one syntax will work in both versions.
> I don't mind if myuser is an owner of that table as long as dbo and
> myuser can truncate it and other users can access it as dbo.tbl####.
> I do not want to create myuser.tbl####
> Thanks
>|||On 13 Nov, 14:14, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Hi
> In SQL Server if I remember well you need GRANT ALTER TABLE ON dbo.tbl####
> TO myuser
>
I tried that it returned "privilege alter table may not be granted or
revoked"
"incorrect syntax near 'on'".
Its a pain cos I want to clear these tables down quickly and don't
wanna fill log up. But do not wanna make this user dbo..|||I haven't used it yet, but I beleive you can write a proc that runs as dbo
and grant your user the rights to run the proc.
Should be easy enough to test.
<terryshamir@.gmail.com> wrote in message
news:1194962955.797517.209350@.19g2000hsx.googlegroups.com...
> Hi
> For certain "work" tables only, I would like to grant truncate table
> ability.
> In SQL2005 I can do
> GRANT ALTER ON dbo.tbl#### TO myuser
> Is there one syntax will work in both versions.
> I don't mind if myuser is an owner of that table as long as dbo and
> myuser can truncate it and other users can access it as dbo.tbl####.
> I do not want to create myuser.tbl####
> Thanks
>
For certain "work" tables only, I would like to grant truncate table
ability.
In SQL2005 I can do
GRANT ALTER ON dbo.tbl#### TO myuser
Is there one syntax will work in both versions.
I don't mind if myuser is an owner of that table as long as dbo and
myuser can truncate it and other users can access it as dbo.tbl####.
I do not want to create myuser.tbl####
ThanksHi
In SQL Server if I remember well you need GRANT ALTER TABLE ON dbo.tbl####
TO myuser
<terryshamir@.gmail.com> wrote in message
news:1194962955.797517.209350@.19g2000hsx.googlegroups.com...
> Hi
> For certain "work" tables only, I would like to grant truncate table
> ability.
> In SQL2005 I can do
> GRANT ALTER ON dbo.tbl#### TO myuser
> Is there one syntax will work in both versions.
> I don't mind if myuser is an owner of that table as long as dbo and
> myuser can truncate it and other users can access it as dbo.tbl####.
> I do not want to create myuser.tbl####
> Thanks
>|||On 13 Nov, 14:14, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Hi
> In SQL Server if I remember well you need GRANT ALTER TABLE ON dbo.tbl####
> TO myuser
>
I tried that it returned "privilege alter table may not be granted or
revoked"
"incorrect syntax near 'on'".
Its a pain cos I want to clear these tables down quickly and don't
wanna fill log up. But do not wanna make this user dbo..|||I haven't used it yet, but I beleive you can write a proc that runs as dbo
and grant your user the rights to run the proc.
Should be easy enough to test.
<terryshamir@.gmail.com> wrote in message
news:1194962955.797517.209350@.19g2000hsx.googlegroups.com...
> Hi
> For certain "work" tables only, I would like to grant truncate table
> ability.
> In SQL2005 I can do
> GRANT ALTER ON dbo.tbl#### TO myuser
> Is there one syntax will work in both versions.
> I don't mind if myuser is an owner of that table as long as dbo and
> myuser can truncate it and other users can access it as dbo.tbl####.
> I do not want to create myuser.tbl####
> Thanks
>
Sunday, February 26, 2012
Gnerating SQL scripts for database creation
In Enterprise Manager there exists the ability to have SQL scripts
generated for the objects of each database. I do not see the ability to
have an an SQL script generated for the database itself. Does such an
ability exist ? I am using SQL Server 7, so maybe this ability does not
exist in that version but does in a later version.
I believe that it was added in 2000, but make sure you study the option ins the "Generate script"
dialog to make sure (I don't have a 7.0 to test on). Also, you might find something useful here:
http://www.karaszi.com/SQLServer/inf...ate_script.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Edward Diener" <eddielee_no_spam_here@.tropicsoft.com> wrote in message
news:edm%23EKWWFHA.3840@.tk2msftngp13.phx.gbl...
> In Enterprise Manager there exists the ability to have SQL scripts generated for the objects of
> each database. I do not see the ability to have an an SQL script generated for the database
> itself. Does such an ability exist ? I am using SQL Server 7, so maybe this ability does not exist
> in that version but does in a later version.
generated for the objects of each database. I do not see the ability to
have an an SQL script generated for the database itself. Does such an
ability exist ? I am using SQL Server 7, so maybe this ability does not
exist in that version but does in a later version.
I believe that it was added in 2000, but make sure you study the option ins the "Generate script"
dialog to make sure (I don't have a 7.0 to test on). Also, you might find something useful here:
http://www.karaszi.com/SQLServer/inf...ate_script.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Edward Diener" <eddielee_no_spam_here@.tropicsoft.com> wrote in message
news:edm%23EKWWFHA.3840@.tk2msftngp13.phx.gbl...
> In Enterprise Manager there exists the ability to have SQL scripts generated for the objects of
> each database. I do not see the ability to have an an SQL script generated for the database
> itself. Does such an ability exist ? I am using SQL Server 7, so maybe this ability does not exist
> in that version but does in a later version.
Sunday, February 19, 2012
giving a user the ability to add logins and users to a database
I need to give a user the ability to create logins and
then add that login to a database. Is it possible to do
this without granting System Administrator Role? I cannot
seem to do this any other way then giving the user
sysadmin. Any help would be greatly apperciated.
Thanks.Add their login to the securityadmin fixed server role and to the
db_accessadmin fixed database role.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Matt." <mattcioffi@.crd.com> wrote in message
news:2e2901c428a4$9d9aec20$a601280a@.phx.gbl...
I need to give a user the ability to create logins and
then add that login to a database. Is it possible to do
this without granting System Administrator Role? I cannot
seem to do this any other way then giving the user
sysadmin. Any help would be greatly apperciated.
Thanks.|||Members of the server role securityadmin can add logins,
grant access to Windows logins.
Members of the database roles db_owner and db_accessadmin
can grant access to the databases in which they are members
of that role.
-Sue
On Thu, 22 Apr 2004 13:01:35 -0700, "Matt."
<mattcioffi@.crd.com> wrote:
>I need to give a user the ability to create logins and
>then add that login to a database. Is it possible to do
>this without granting System Administrator Role? I cannot
>seem to do this any other way then giving the user
>sysadmin. Any help would be greatly apperciated.
>Thanks.|||This does not work. I just created a user gave it
security admin role on the server and then gave it both
accessadmin and securityadmin. I get permission denied
when I try to add the user.
Please help.
>--Original Message--
>Members of the server role securityadmin can add logins,
>grant access to Windows logins.
>Members of the database roles db_owner and db_accessadmin
>can grant access to the databases in which they are
members
>of that role.
>-Sue
>On Thu, 22 Apr 2004 13:01:35 -0700, "Matt."
><mattcioffi@.crd.com> wrote:
>
cannot[vbcol=seagreen]
>.
>|||It works. I'm not sure what you missed - double check the
databases where you wanted to grant the permissions for the
new user you created - make sure you were in the correct
database. Also double check the database where the new user
is trying to add users and make sure they are executing the
procedures in the correct database.
As an example, for SomeLogin to be able to add logins and
users you would use something like:
sp_addlogin 'SomeLogin', 'SomePassword'
or
sp_grantlogin 'SomeLogin'
go
sp_addsrvrolemember 'SomeLogin', 'securityadmin'
go
use YourDatabase
go
sp_grantdbaccess 'SomeLogin'
go
sp_addrolemember 'db_accessadmin', 'SomeLogin'
go
Then when SomeLogin logs into SQL Server, they can execute:
sp_addlogin 'NewUser', 'SomeOtherPassword'
or
sp_grantlogin 'NewUser'
go
use YourDatabase
go
sp_grantdbaccess 'NewUser'
go
-Sue
On Thu, 22 Apr 2004 14:03:40 -0700,
<anonymous@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>This does not work. I just created a user gave it
>security admin role on the server and then gave it both
>accessadmin and securityadmin. I get permission denied
>when I try to add the user.
>Please help.
>members
>cannot|||I rechecked everything and it does not work. Are there
any server configurations or database options that need to
be set? I will delete the new user and login and try one
more time but I have tried everything and it still gets
Server: Msg 15247, Level 16, State 1, Procedure
sp_adduser, Line 35
User does not have permission to perform this action.
login has security administrator server role, the user in
the database has accessadmin, securityadmin, and at
various times other roles in the database like db_owner
and it still will not allow this to work.
>--Original Message--
>It works. I'm not sure what you missed - double check the
>databases where you wanted to grant the permissions for
the
>new user you created - make sure you were in the correct
>database. Also double check the database where the new
user
>is trying to add users and make sure they are executing
the
>procedures in the correct database.
>As an example, for SomeLogin to be able to add logins and
>users you would use something like:
>sp_addlogin 'SomeLogin', 'SomePassword'
>or
>sp_grantlogin 'SomeLogin'
>go
>sp_addsrvrolemember 'SomeLogin', 'securityadmin'
>go
>use YourDatabase
>go
>sp_grantdbaccess 'SomeLogin'
>go
>sp_addrolemember 'db_accessadmin', 'SomeLogin'
>go
>Then when SomeLogin logs into SQL Server, they can
execute:
>sp_addlogin 'NewUser', 'SomeOtherPassword'
>or
>sp_grantlogin 'NewUser'
>go
>use YourDatabase
>go
>sp_grantdbaccess 'NewUser'
>go
>-Sue
>On Thu, 22 Apr 2004 14:03:40 -0700,
><anonymous@.discussions.microsoft.com> wrote:
>
db_accessadmin[vbcol=seagreen]
do[vbcol=seagreen]
>.
>|||Sorry...don't know what else to suggest. There are no
special server or database configurations, options needed to
allow a member of db_accessadmin to execute
sp_grantdbaccess.
-Sue
On Mon, 26 Apr 2004 06:09:57 -0700, "Matt"
<mattcioffi@.crd.com> wrote:
[vbcol=seagreen]
>I rechecked everything and it does not work. Are there
>any server configurations or database options that need to
>be set? I will delete the new user and login and try one
>more time but I have tried everything and it still gets
>Server: Msg 15247, Level 16, State 1, Procedure
>sp_adduser, Line 35
>User does not have permission to perform this action.
>login has security administrator server role, the user in
>the database has accessadmin, securityadmin, and at
>various times other roles in the database like db_owner
>and it still will not allow this to work.
>
>
>the
>user
>the
>execute:
>db_accessadmin
>do
then add that login to a database. Is it possible to do
this without granting System Administrator Role? I cannot
seem to do this any other way then giving the user
sysadmin. Any help would be greatly apperciated.
Thanks.Add their login to the securityadmin fixed server role and to the
db_accessadmin fixed database role.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Matt." <mattcioffi@.crd.com> wrote in message
news:2e2901c428a4$9d9aec20$a601280a@.phx.gbl...
I need to give a user the ability to create logins and
then add that login to a database. Is it possible to do
this without granting System Administrator Role? I cannot
seem to do this any other way then giving the user
sysadmin. Any help would be greatly apperciated.
Thanks.|||Members of the server role securityadmin can add logins,
grant access to Windows logins.
Members of the database roles db_owner and db_accessadmin
can grant access to the databases in which they are members
of that role.
-Sue
On Thu, 22 Apr 2004 13:01:35 -0700, "Matt."
<mattcioffi@.crd.com> wrote:
>I need to give a user the ability to create logins and
>then add that login to a database. Is it possible to do
>this without granting System Administrator Role? I cannot
>seem to do this any other way then giving the user
>sysadmin. Any help would be greatly apperciated.
>Thanks.|||This does not work. I just created a user gave it
security admin role on the server and then gave it both
accessadmin and securityadmin. I get permission denied
when I try to add the user.
Please help.
>--Original Message--
>Members of the server role securityadmin can add logins,
>grant access to Windows logins.
>Members of the database roles db_owner and db_accessadmin
>can grant access to the databases in which they are
members
>of that role.
>-Sue
>On Thu, 22 Apr 2004 13:01:35 -0700, "Matt."
><mattcioffi@.crd.com> wrote:
>
cannot[vbcol=seagreen]
>.
>|||It works. I'm not sure what you missed - double check the
databases where you wanted to grant the permissions for the
new user you created - make sure you were in the correct
database. Also double check the database where the new user
is trying to add users and make sure they are executing the
procedures in the correct database.
As an example, for SomeLogin to be able to add logins and
users you would use something like:
sp_addlogin 'SomeLogin', 'SomePassword'
or
sp_grantlogin 'SomeLogin'
go
sp_addsrvrolemember 'SomeLogin', 'securityadmin'
go
use YourDatabase
go
sp_grantdbaccess 'SomeLogin'
go
sp_addrolemember 'db_accessadmin', 'SomeLogin'
go
Then when SomeLogin logs into SQL Server, they can execute:
sp_addlogin 'NewUser', 'SomeOtherPassword'
or
sp_grantlogin 'NewUser'
go
use YourDatabase
go
sp_grantdbaccess 'NewUser'
go
-Sue
On Thu, 22 Apr 2004 14:03:40 -0700,
<anonymous@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>This does not work. I just created a user gave it
>security admin role on the server and then gave it both
>accessadmin and securityadmin. I get permission denied
>when I try to add the user.
>Please help.
>members
>cannot|||I rechecked everything and it does not work. Are there
any server configurations or database options that need to
be set? I will delete the new user and login and try one
more time but I have tried everything and it still gets
Server: Msg 15247, Level 16, State 1, Procedure
sp_adduser, Line 35
User does not have permission to perform this action.
login has security administrator server role, the user in
the database has accessadmin, securityadmin, and at
various times other roles in the database like db_owner
and it still will not allow this to work.
>--Original Message--
>It works. I'm not sure what you missed - double check the
>databases where you wanted to grant the permissions for
the
>new user you created - make sure you were in the correct
>database. Also double check the database where the new
user
>is trying to add users and make sure they are executing
the
>procedures in the correct database.
>As an example, for SomeLogin to be able to add logins and
>users you would use something like:
>sp_addlogin 'SomeLogin', 'SomePassword'
>or
>sp_grantlogin 'SomeLogin'
>go
>sp_addsrvrolemember 'SomeLogin', 'securityadmin'
>go
>use YourDatabase
>go
>sp_grantdbaccess 'SomeLogin'
>go
>sp_addrolemember 'db_accessadmin', 'SomeLogin'
>go
>Then when SomeLogin logs into SQL Server, they can
execute:
>sp_addlogin 'NewUser', 'SomeOtherPassword'
>or
>sp_grantlogin 'NewUser'
>go
>use YourDatabase
>go
>sp_grantdbaccess 'NewUser'
>go
>-Sue
>On Thu, 22 Apr 2004 14:03:40 -0700,
><anonymous@.discussions.microsoft.com> wrote:
>
db_accessadmin[vbcol=seagreen]
do[vbcol=seagreen]
>.
>|||Sorry...don't know what else to suggest. There are no
special server or database configurations, options needed to
allow a member of db_accessadmin to execute
sp_grantdbaccess.
-Sue
On Mon, 26 Apr 2004 06:09:57 -0700, "Matt"
<mattcioffi@.crd.com> wrote:
[vbcol=seagreen]
>I rechecked everything and it does not work. Are there
>any server configurations or database options that need to
>be set? I will delete the new user and login and try one
>more time but I have tried everything and it still gets
>Server: Msg 15247, Level 16, State 1, Procedure
>sp_adduser, Line 35
>User does not have permission to perform this action.
>login has security administrator server role, the user in
>the database has accessadmin, securityadmin, and at
>various times other roles in the database like db_owner
>and it still will not allow this to work.
>
>
>the
>user
>the
>execute:
>db_accessadmin
>do
Subscribe to:
Posts (Atom)