Re: Adding Permissions

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@xxxxxxxxx> wrote in message
news:up6oLty7GHA.1256@xxxxxxxxxxxxxxxxxxxxxxx
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@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:FE43D341-3AD0-459D-AA6A-794CA3E02915@xxxxxxxxxxxxxxxx
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?





.

Relevant Pages

  • Re: Execute Persmission denied on object sp_OACreate
    ... > SQL Server is creating a job behind the scenes. ... > permissions. ... > SA account password and gaining access to the database. ... >>> How can get a user permissions to execute these stored procedures ...
    (microsoft.public.sqlserver.security)
  • Re: Execute Persmission denied on object sp_OACreate
    ... SQL Server doesn't check permissions on indirectly referenced objects as ... You can prevent ad-hoc execution of powerful master database procs while ... >I have a user who has execute permissions on a store procedure in a>database> which in turns executes 4 stored procedures in the master database. ...
    (microsoft.public.sqlserver.security)
  • Re: Data migration questions?
    ... Use SSIS or the SqlBulkCopy class to import the data into SQL Server. ... Anyone who has read my books knows that I'm not in favor of including BLOBs in the database. ... "Mervin Williams" wrote in message ... should I use a DataSet to bring the data down to the local machine that will run the code and execute the transformation logic from it. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: New user with no permissions can see and execute system stored procedures...
    ... Best regards ... But I can still see system views and stored procedures (though not system ... and map it to a database user and set its default schema to dbo. ... I have only tried to execute sys.sp_catalogs, but in my opinion a new ...
    (microsoft.public.sqlserver.security)
  • Re: Unable to view system stored procedures from .NET IDE and unable to debug SQL
    ... But what is the reason for the debug problem I mentioned below? ... try to step into a stored procedure in the Northwind database, ... Run SQL server setup or contact database ... I can't see any system stored procedures in master. ...
    (microsoft.public.dotnet.framework.adonet)