Adding Permissions

We created a database with approximately 80 tables and 170 stored procedures
.
We were not too bright to begin with and one thing we did not consider was
not using sa as the login. Now, we need to create a user that does not have
full sa privileges but can execute all stored procedures and view all data i
n
the tables. Some of the stored procedures use dynamic sql (sp_executesql). W
e
also will be creating a new database, which corresponds to a specific test
event, about two or three times a month.
Question I have is can we create one user that can access all the stored
procedures and tables in ALL databases (as they are created) and is there a
fast way of granting privileges to all these objects without having to go in
the permissions for each one?You can create a login and create a user which is in the data_reader role in
every database and grant it rights to execute all stored procedures.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Wannabe" <Wannabe@.discussions.microsoft.com> wrote in message
news:FE43D341-3AD0-459D-AA6A-794CA3E02915@.microsoft.com...
> We created a database with approximately 80 tables and 170 stored
> procedures.
> We were not too bright to begin with and one thing we did not consider was
> not using sa as the login. Now, we need to create a user that does not
> have
> full sa privileges but can execute all stored procedures and view all data
> in
> the tables. Some of the stored procedures use dynamic sql (sp_executesql).
> We
> also will be creating a new database, which corresponds to a specific test
> event, about two or three times a month.
> Question I have is can we create one user that can access all the stored
> procedures and tables in ALL databases (as they are created) and is there
> a
> fast way of granting privileges to all these objects without having to go
> in
> the permissions for each one?
>|||Also note that in SQL Server 2005, you can grant SELECT and EXECUTE
permissions at database level, so you don't need to perform grants for each
table or procedure.
Do you need to do this in all databases or only in databases of a certain
type? If the answer is all, consider setting this up in the model database.
Thanks
Laurentiu Cristofor [MSFT]
Software Design Engineer
SQL Server Engine
http://blogs.msdn.com/lcris/
This posting is provided "AS IS" with no warranties, and confers no rights.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:up6oLty7GHA.1256@.TK2MSFTNGP04.phx.gbl...
> You can create a login and create a user which is in the data_reader role
> in every database and grant it rights to execute all stored procedures.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Wannabe" <Wannabe@.discussions.microsoft.com> wrote in message
> news:FE43D341-3AD0-459D-AA6A-794CA3E02915@.microsoft.com...
>

Adding additional lines to the report

Hello,
I've created a report using SQL Server reporting services that looks
approximately like this:
MONTH Non-Operational DRs Operational DRs Monthly Total
01/2006 6 32 38
02/2006 18 25 43
04/2006 19 41 60
05/2006 6 27 33
09/2006 14 14
10/2006 11 5 16
11/2006 2 2
TOTAL 69 153 222
This is my query behind this report (Oracle):
SELECT SUM(DISC) AS DISC, IMPACT, ADDDATE, YEAR, MONTH
FROM RPT_DRS_BY_MONTH_VU
WHERE (TO_DATE(ADDDATE, 'MM/YYYY') BETWEEN TO_DATE(:pm_date1,
'MM/YYYY') AND TO_DATE(:pm_date2, 'MM/YYYY')) AND (TO_CHAR(TE_DM_S_ID)
LIKE :pm_sid) AND (TO_CHAR(TE_DM_L_ID) LIKE
:pm_lid)
GROUP BY IMPACT, ADDDATE, YEAR, MONTH
ORDER BY YEAR, MONTH
This report doesn't have any data for 03/2006, 06-08/2006. I need to
add these rows with the values 0, so the report would look like this:
MONTH Non-Operational DRs Operational DRs Monthly Total
01/2006 6 32 38
02/2006 18 25 43
03/2006 0 0 0
04/2006 19 41 60
05/2006 6 27 33
06/2006 0 0 0
07/2006 0 0 0
08/2006 0 0 0
09/2006 14 14
10/2006 11 5 16
11/2006 2 2
TOTAL 69 153 222
What would be the best way to do this?
I think I should use a UNION query. I know couple ways to do this (like
creating a table with all values then selecting missing values and
adding them using UNION), but they are bulky and not very efficient.
What would be the best way to achieve this?
I would appreciate your help.
Thank you,
PeterThere are so many ways you can do, you can create a table with all 12 months
in a column and do a outer join with the other table to get even 0 values,
probabily you need to used isnull to make it 0, because it returns null for
values not existing.
Or create a temp table with all 12 months values and insert all the values
you can put this in a stored proc.
These are some of the way
Amarnath
"Peter" wrote:
> Hello,
> I've created a report using SQL Server reporting services that looks
> approximately like this:
> MONTH Non-Operational DRs Operational DRs Monthly Total
> 01/2006 6 32 38
> 02/2006 18 25 43
> 04/2006 19 41 60
> 05/2006 6 27 33
> 09/2006 14 14
> 10/2006 11 5 16
> 11/2006 2 2
> TOTAL 69 153 222
> This is my query behind this report (Oracle):
> SELECT SUM(DISC) AS DISC, IMPACT, ADDDATE, YEAR, MONTH
> FROM RPT_DRS_BY_MONTH_VU
> WHERE (TO_DATE(ADDDATE, 'MM/YYYY') BETWEEN TO_DATE(:pm_date1,
> 'MM/YYYY') AND TO_DATE(:pm_date2, 'MM/YYYY')) AND (TO_CHAR(TE_DM_S_ID)
> LIKE :pm_sid) AND (TO_CHAR(TE_DM_L_ID) LIKE
> :pm_lid)
> GROUP BY IMPACT, ADDDATE, YEAR, MONTH
> ORDER BY YEAR, MONTH
> This report doesn't have any data for 03/2006, 06-08/2006. I need to
> add these rows with the values 0, so the report would look like this:
> MONTH Non-Operational DRs Operational DRs Monthly Total
> 01/2006 6 32 38
> 02/2006 18 25 43
> 03/2006 0 0 0
> 04/2006 19 41 60
> 05/2006 6 27 33
> 06/2006 0 0 0
> 07/2006 0 0 0
> 08/2006 0 0 0
> 09/2006 14 14
> 10/2006 11 5 16
> 11/2006 2 2
> TOTAL 69 153 222
> What would be the best way to do this?
> I think I should use a UNION query. I know couple ways to do this (like
> creating a table with all values then selecting missing values and
> adding them using UNION), but they are bulky and not very efficient.
> What would be the best way to achieve this?
> I would appreciate your help.
> Thank you,
> Peter
>|||Thank you, Amarnath,
Yes, I think I can. I tried to avoid creating table, but I guess it's
an easiest way.
Peter
Amarnath wrote:
> There are so many ways you can do, you can create a table with all 12 months
> in a column and do a outer join with the other table to get even 0 values,
> probabily you need to used isnull to make it 0, because it returns null for
> values not existing.
> Or create a temp table with all 12 months values and insert all the values
> you can put this in a stored proc.
> These are some of the way
> Amarnath
> "Peter" wrote:
> > Hello,
> >
> > I've created a report using SQL Server reporting services that looks
> > approximately like this:
> >
> > MONTH Non-Operational DRs Operational DRs Monthly Total
> > 01/2006 6 32 38
> > 02/2006 18 25 43
> > 04/2006 19 41 60
> > 05/2006 6 27 33
> > 09/2006 14 14
> > 10/2006 11 5 16
> > 11/2006 2 2
> > TOTAL 69 153 222
> >
> > This is my query behind this report (Oracle):
> >
> > SELECT SUM(DISC) AS DISC, IMPACT, ADDDATE, YEAR, MONTH
> > FROM RPT_DRS_BY_MONTH_VU
> > WHERE (TO_DATE(ADDDATE, 'MM/YYYY') BETWEEN TO_DATE(:pm_date1,
> > 'MM/YYYY') AND TO_DATE(:pm_date2, 'MM/YYYY')) AND (TO_CHAR(TE_DM_S_ID)
> > LIKE :pm_sid) AND (TO_CHAR(TE_DM_L_ID) LIKE
> > :pm_lid)
> > GROUP BY IMPACT, ADDDATE, YEAR, MONTH
> > ORDER BY YEAR, MONTH
> >
> > This report doesn't have any data for 03/2006, 06-08/2006. I need to
> > add these rows with the values 0, so the report would look like this:
> >
> > MONTH Non-Operational DRs Operational DRs Monthly Total
> > 01/2006 6 32 38
> > 02/2006 18 25 43
> > 03/2006 0 0 0
> > 04/2006 19 41 60
> > 05/2006 6 27 33
> > 06/2006 0 0 0
> > 07/2006 0 0 0
> > 08/2006 0 0 0
> > 09/2006 14 14
> > 10/2006 11 5 16
> > 11/2006 2 2
> > TOTAL 69 153 222
> >
> > What would be the best way to do this?
> >
> > I think I should use a UNION query. I know couple ways to do this (like
> > creating a table with all values then selecting missing values and
> > adding them using UNION), but they are bulky and not very efficient.
> >
> > What would be the best way to achieve this?
> >
> > I would appreciate your help.
> >
> > Thank you,
> >
> > Peter
> >
> >