Re: Adding Permissions

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 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: 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)
  • Connecting a user from a backup database to a new login
    ... In SQL Server 2000, I loaded a backup file into a new local database. ... The database has an existing user which owns several stored procedures, ... I want to create a login of the same ...
    (microsoft.public.sqlserver.security)
  • Re: how to secure a sql 2005 database?
    ... that's why i rather keep my stored procedures in my own source code, then pass it thru to sqlexec to execute. ... its like quickbooks turning over their internal structures, which they really don't, but provide an api to export data out. ... so are you saying that i can't create a user and make that user the only valid user to get into the database, and unless you know the password, even admins, you cant open the database at all. ...
    (microsoft.public.sqlserver.setup)
  • Re: Grant Execute right to the group of users
    ... Below is a script that will grant execute permissions to the specified role ... on all user procedures in the current database. ... > How could I grant the execute right to all stored procedures in a database for a group of users? ...
    (microsoft.public.sqlserver.programming)