Grant Permission Statement

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

Grant Permission issue

Hi,
I have a user called as 'TestUser', a dbrole called as 'OrderUsers' and a
database called as 'Orders'
How do I grant Select, Insert, Update, delete permission to all tables and
views in the database to User 'TestUser' and to the dbrole 'OrderUsers'
How do i grant Execute permission to all stored procedures in the database
'Orders' to user 'TestUser' and to the dbrole 'OrderUsers'
Could you please provide SQL statement to achive the above functionality.
Thanks in Advance
-Spsk
I believe the roles apply to views as well as tables...if so, add TestUser
and OrderUsers to the database roles db_datareader and db_datawriter.
For the procs...try something like this (not fully tested):
select 'GRANT EXECUTE ON [dbo].[' + name + '] TO [TestUser], [OrderUsers]'
from sysobjects
where xtype = 'P'
and name not like 'dt_%'
Then copy/paste the resultset in the query pane and run it.
HTH
Jerry
"Spsk" <Spsk@.discussions.microsoft.com> wrote in message
news:AF177A1A-DD1E-459F-A722-18FBB2EF68E4@.microsoft.com...
> Hi,
> I have a user called as 'TestUser', a dbrole called as 'OrderUsers' and a
> database called as 'Orders'
> How do I grant Select, Insert, Update, delete permission to all tables and
> views in the database to User 'TestUser' and to the dbrole 'OrderUsers'
> How do i grant Execute permission to all stored procedures in the database
> 'Orders' to user 'TestUser' and to the dbrole 'OrderUsers'
> Could you please provide SQL statement to achive the above functionality.
> Thanks in Advance
> -Spsk
|||Thanks for your immediate reply.
Is there any single sql statement /sql block to assign execute permission to
procs.
Best Regards,
Senthil
"Jerry Spivey" wrote:

> I believe the roles apply to views as well as tables...if so, add TestUser
> and OrderUsers to the database roles db_datareader and db_datawriter.
> For the procs...try something like this (not fully tested):
> select 'GRANT EXECUTE ON [dbo].[' + name + '] TO [TestUser], [OrderUsers]'
> from sysobjects
> where xtype = 'P'
> and name not like 'dt_%'
> Then copy/paste the resultset in the query pane and run it.
> HTH
> Jerry
>
> "Spsk" <Spsk@.discussions.microsoft.com> wrote in message
> news:AF177A1A-DD1E-459F-A722-18FBB2EF68E4@.microsoft.com...
>
>
|||Not that I am aware of.
"Spsk" <Spsk@.discussions.microsoft.com> wrote in message
news:1B9A9367-8643-4014-9E22-BC40415DC5CC@.microsoft.com...[vbcol=seagreen]
> Thanks for your immediate reply.
> Is there any single sql statement /sql block to assign execute permission
> to
> procs.
> Best Regards,
> Senthil
> "Jerry Spivey" wrote:
|||Correct. There's none at the moment. You can write some code that loops all users and grants execute
permissions. In 2005, there will be, like:
GRANT EXECUTE ON DATABASE::pubs TO username
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:el50sZvwFHA.2132@.TK2MSFTNGP15.phx.gbl...
> Not that I am aware of.

> "Spsk" <Spsk@.discussions.microsoft.com> wrote in message
> news:1B9A9367-8643-4014-9E22-BC40415DC5CC@.microsoft.com...
>
sql

Grant Permission issue

Hi,
I have a user called as 'TestUser', a dbrole called as 'OrderUsers' and a
database called as 'Orders'
How do I grant Select, Insert, Update, delete permission to all tables and
views in the database to User 'TestUser' and to the dbrole 'OrderUsers'
How do i grant Execute permission to all stored procedures in the database
'Orders' to user 'TestUser' and to the dbrole 'OrderUsers'
Could you please provide SQL statement to achive the above functionality.
Thanks in Advance
-SpskI believe the roles apply to views as well as tables...if so, add TestUser
and OrderUsers to the database roles db_datareader and db_datawriter.
For the procs...try something like this (not fully tested):
select 'GRANT EXECUTE ON [dbo].[' + name + '] TO [TestUser],
1;OrderUsers]'
from sysobjects
where xtype = 'P'
and name not like 'dt_%'
Then copy/paste the resultset in the query pane and run it.
HTH
Jerry
"Spsk" <Spsk@.discussions.microsoft.com> wrote in message
news:AF177A1A-DD1E-459F-A722-18FBB2EF68E4@.microsoft.com...
> Hi,
> I have a user called as 'TestUser', a dbrole called as 'OrderUsers' and a
> database called as 'Orders'
> How do I grant Select, Insert, Update, delete permission to all tables and
> views in the database to User 'TestUser' and to the dbrole 'OrderUsers'
> How do i grant Execute permission to all stored procedures in the database
> 'Orders' to user 'TestUser' and to the dbrole 'OrderUsers'
> Could you please provide SQL statement to achive the above functionality.
> Thanks in Advance
> -Spsk|||Thanks for your immediate reply.
Is there any single sql statement /sql block to assign execute permission to
procs.
Best Regards,
Senthil
"Jerry Spivey" wrote:

> I believe the roles apply to views as well as tables...if so, add TestUser
> and OrderUsers to the database roles db_datareader and db_datawriter.
> For the procs...try something like this (not fully tested):
> select 'GRANT EXECUTE ON [dbo].[' + name + '] TO [TestUser], &
#91;OrderUsers]'
> from sysobjects
> where xtype = 'P'
> and name not like 'dt_%'
> Then copy/paste the resultset in the query pane and run it.
> HTH
> Jerry
>
> "Spsk" <Spsk@.discussions.microsoft.com> wrote in message
> news:AF177A1A-DD1E-459F-A722-18FBB2EF68E4@.microsoft.com...
>
>|||Not that I am aware of.
"Spsk" <Spsk@.discussions.microsoft.com> wrote in message
news:1B9A9367-8643-4014-9E22-BC40415DC5CC@.microsoft.com...[vbcol=seagreen]
> Thanks for your immediate reply.
> Is there any single sql statement /sql block to assign execute permission
> to
> procs.
> Best Regards,
> Senthil
> "Jerry Spivey" wrote:
>|||Correct. There's none at the moment. You can write some code that loops all
users and grants execute
permissions. In 2005, there will be, like:
GRANT EXECUTE ON DATABASE::pubs TO username
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:el50sZvwFHA.2132@.TK2MSFTNGP15.phx.gbl...
> Not that I am aware of.

> "Spsk" <Spsk@.discussions.microsoft.com> wrote in message
> news:1B9A9367-8643-4014-9E22-BC40415DC5CC@.microsoft.com...
>

Global Search Criteria - 1 criteria for multiple columns

I am curious of other's opinions on this. It may be more a UI issue than a
database one, but there is a database side to it, and I have seen that folks
here have a broader knowledge base than just proper table structures, so...
I have an application where we need to search for an employee. You can
search on name, employee ID, or department. The determination has been made
to allow the user to enter one value, and then search for the value in any
of the above columns.
i.e.
employee_id = @.SearchString
or
employee_name like '%' + @.SearchString + '%'
or
department_name like '%' + @.SearchString + '%'
The question is, what has your experience been with this approach, from both
a database perspective, and an end user / UI perspective.One technique in such situations is to use a computed column like:
CREATE TABLE tbl (
...,
employee_id ...,
employee_name...,
department_name...
comp_col AS ( employee_id + employee_name + department_name ) );
and create an index on this comp_col column.
Now change your search pattern in your query to use comp_col LIKE '%' + @.p +
'%'. Depending on your usage you can create a covering index on all the
three columns & avoid the computed column altogether as well.
Anith|||Thanks, I'll give that approach a try to help performance.
Does anyone have any non-database related feedback on this approach?
Usability, clarity to the end user, etc?
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:ugqOHufdGHA.4900@.TK2MSFTNGP02.phx.gbl...
> One technique in such situations is to use a computed column like:
> CREATE TABLE tbl (
> ...,
> employee_id ...,
> employee_name...,
> department_name...
> comp_col AS ( employee_id + employee_name + department_name ) );
> and create an index on this comp_col column.
> Now change your search pattern in your query to use comp_col LIKE '%' + @.p
+
> '%'. Depending on your usage you can create a covering index on all the
> three columns & avoid the computed column altogether as well.
> --
> Anith
>|||"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:ugqOHufdGHA.4900@.TK2MSFTNGP02.phx.gbl...
> One technique in such situations is to use a computed column like:
> CREATE TABLE tbl (
> ...,
> employee_id ...,
> employee_name...,
> department_name...
> comp_col AS ( employee_id + employee_name + department_name ) );
> and create an index on this comp_col column.
> Now change your search pattern in your query to use comp_col LIKE '%' + @.p
> + '%'. Depending on your usage you can create a covering index on all the
> three columns & avoid the computed column altogether as well.
> --
> Anith
>
Anith,
Could you explain the performance advantage to using either the computed
column or the covering index compared to just a straight where clause (e.g.
WHERE employee_id + employee_name + department_name LIKE '%' + @.p + '%' ) ?
I'm not knocking the approach, I would just like to understand. Also, it
seems as though once you use the LIKE clause, any advantage to an index
disappears.
Thanks,
Kevin|||I think I messed this one up :-( You are right, the wild card in the
beginning of the parameter will nullify the index usage.
Anith