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...
>