Granting permissions

I have a production server with a few databases. Each database has about
100-150 tables, and 200 or so Stored Procs.
I have a new user john that gets created on each database. I need to give
him the ability to select, insert, update and delete from any user table, and
to be able to execute any Stored Procedure.
If I grant this new user membership to db_datareader, he gets select from
any user table.
If I grant this new user membership to db_datawriter, he gets insert,
update, delete to any user table.
So far, so good. Now the problem -
What role can I grant that would allow this user to be able to run any
stored procedure? Note that my manager will not let me grant him db_owner, as
this gives him too much rights.
And what every I do to give him the above rights, he can't be allowed to
create tables, create views or create any stored procedures
I could do this manually through the GUI, but with over 200 stored procs per
table, this is too time consuming. I could script, but this is also something
labor intensive, am looking for an easier way.
Thank you in advance.
Sam
Think I found the solution. For each table, use the:
DENY CREATE VIEW to john
DENY CREATE TABLE to john
DENY CREATE SP to john
Still not sure how to give a user rights to execute all Stored Procedures,
though.
Sam
"Sam" wrote:

> I have a production server with a few databases. Each database has about
> 100-150 tables, and 200 or so Stored Procs.
> I have a new user john that gets created on each database. I need to give
> him the ability to select, insert, update and delete from any user table, and
> to be able to execute any Stored Procedure.
> If I grant this new user membership to db_datareader, he gets select from
> any user table.
> If I grant this new user membership to db_datawriter, he gets insert,
> update, delete to any user table.
> So far, so good. Now the problem -
> What role can I grant that would allow this user to be able to run any
> stored procedure? Note that my manager will not let me grant him db_owner, as
> this gives him too much rights.
> And what every I do to give him the above rights, he can't be allowed to
> create tables, create views or create any stored procedures
> I could do this manually through the GUI, but with over 200 stored procs per
> table, this is too time consuming. I could script, but this is also something
> labor intensive, am looking for an easier way.
> Thank you in advance.
> Sam
|||Create a new role in each database.
Grant the appropriate permissions to this role. (I know it's time
consuming, but it's a one time deal).
Then add your new users to this role in each db.
If you check around, there have been several scripts already created that
will grant permissions. You just need to tweak them a bit. My guess is
that in an hour worth of your time, you will have the script completed.
This newsgroup has had several posted to it within the last two weeks.
Rick Sawtell
MCT, MCSD, MCDBA
"Sam" <Sam@.discussions.microsoft.com> wrote in message
news:E7839D71-7EC7-47A0-96DF-4A62DAEED1D0@.microsoft.com...
> I have a production server with a few databases. Each database has about
> 100-150 tables, and 200 or so Stored Procs.
> I have a new user john that gets created on each database. I need to give
> him the ability to select, insert, update and delete from any user table,
and
> to be able to execute any Stored Procedure.
> If I grant this new user membership to db_datareader, he gets select from
> any user table.
> If I grant this new user membership to db_datawriter, he gets insert,
> update, delete to any user table.
> So far, so good. Now the problem -
> What role can I grant that would allow this user to be able to run any
> stored procedure? Note that my manager will not let me grant him db_owner,
as
> this gives him too much rights.
> And what every I do to give him the above rights, he can't be allowed to
> create tables, create views or create any stored procedures
> I could do this manually through the GUI, but with over 200 stored procs
per
> table, this is too time consuming. I could script, but this is also
something
> labor intensive, am looking for an easier way.
> Thank you in advance.
> Sam

Granting permissions

I have a production server with a few databases. Each database has about
100-150 tables, and 200 or so Stored Procs.
I have a new user john that gets created on each database. I need to give
him the ability to select, insert, update and delete from any user table, an
d
to be able to execute any Stored Procedure.
If I grant this new user membership to db_datareader, he gets select from
any user table.
If I grant this new user membership to db_datawriter, he gets insert,
update, delete to any user table.
So far, so good. Now the problem -
What role can I grant that would allow this user to be able to run any
stored procedure? Note that my manager will not let me grant him db_owner, a
s
this gives him too much rights.
And what every I do to give him the above rights, he can't be allowed to
create tables, create views or create any stored procedures
I could do this manually through the GUI, but with over 200 stored procs per
table, this is too time consuming. I could script, but this is also somethin
g
labor intensive, am looking for an easier way.
Thank you in advance.
SamThink I found the solution. For each table, use the:
DENY CREATE VIEW to john
DENY CREATE TABLE to john
DENY CREATE SP to john
Still not sure how to give a user rights to execute all Stored Procedures,
though.
Sam
"Sam" wrote:

> I have a production server with a few databases. Each database has about
> 100-150 tables, and 200 or so Stored Procs.
> I have a new user john that gets created on each database. I need to give
> him the ability to select, insert, update and delete from any user table,
and
> to be able to execute any Stored Procedure.
> If I grant this new user membership to db_datareader, he gets select from
> any user table.
> If I grant this new user membership to db_datawriter, he gets insert,
> update, delete to any user table.
> So far, so good. Now the problem -
> What role can I grant that would allow this user to be able to run any
> stored procedure? Note that my manager will not let me grant him db_owner,
as
> this gives him too much rights.
> And what every I do to give him the above rights, he can't be allowed to
> create tables, create views or create any stored procedures
> I could do this manually through the GUI, but with over 200 stored procs p
er
> table, this is too time consuming. I could script, but this is also someth
ing
> labor intensive, am looking for an easier way.
> Thank you in advance.
> Sam|||Create a new role in each database.
Grant the appropriate permissions to this role. (I know it's time
consuming, but it's a one time deal).
Then add your new users to this role in each db.
If you check around, there have been several scripts already created that
will grant permissions. You just need to tweak them a bit. My guess is
that in an hour worth of your time, you will have the script completed.
This newsgroup has had several posted to it within the last two weeks.
Rick Sawtell
MCT, MCSD, MCDBA
"Sam" <Sam@.discussions.microsoft.com> wrote in message
news:E7839D71-7EC7-47A0-96DF-4A62DAEED1D0@.microsoft.com...
> I have a production server with a few databases. Each database has about
> 100-150 tables, and 200 or so Stored Procs.
> I have a new user john that gets created on each database. I need to give
> him the ability to select, insert, update and delete from any user table,
and
> to be able to execute any Stored Procedure.
> If I grant this new user membership to db_datareader, he gets select from
> any user table.
> If I grant this new user membership to db_datawriter, he gets insert,
> update, delete to any user table.
> So far, so good. Now the problem -
> What role can I grant that would allow this user to be able to run any
> stored procedure? Note that my manager will not let me grant him db_owner,
as
> this gives him too much rights.
> And what every I do to give him the above rights, he can't be allowed to
> create tables, create views or create any stored procedures
> I could do this manually through the GUI, but with over 200 stored procs
per
> table, this is too time consuming. I could script, but this is also
something
> labor intensive, am looking for an easier way.
> Thank you in advance.
> Sam

Granting permissions

I have a production server with a few databases. Each database has about
100-150 tables, and 200 or so Stored Procs.
I have a new user john that gets created on each database. I need to give
him the ability to select, insert, update and delete from any user table, and
to be able to execute any Stored Procedure.
If I grant this new user membership to db_datareader, he gets select from
any user table.
If I grant this new user membership to db_datawriter, he gets insert,
update, delete to any user table.
So far, so good. Now the problem -
What role can I grant that would allow this user to be able to run any
stored procedure? Note that my manager will not let me grant him db_owner, as
this gives him too much rights.
And what every I do to give him the above rights, he can't be allowed to
create tables, create views or create any stored procedures
I could do this manually through the GUI, but with over 200 stored procs per
table, this is too time consuming. I could script, but this is also something
labor intensive, am looking for an easier way.
Thank you in advance.
SamThink I found the solution. For each table, use the:
DENY CREATE VIEW to john
DENY CREATE TABLE to john
DENY CREATE SP to john
Still not sure how to give a user rights to execute all Stored Procedures,
though.
Sam
"Sam" wrote:
> I have a production server with a few databases. Each database has about
> 100-150 tables, and 200 or so Stored Procs.
> I have a new user john that gets created on each database. I need to give
> him the ability to select, insert, update and delete from any user table, and
> to be able to execute any Stored Procedure.
> If I grant this new user membership to db_datareader, he gets select from
> any user table.
> If I grant this new user membership to db_datawriter, he gets insert,
> update, delete to any user table.
> So far, so good. Now the problem -
> What role can I grant that would allow this user to be able to run any
> stored procedure? Note that my manager will not let me grant him db_owner, as
> this gives him too much rights.
> And what every I do to give him the above rights, he can't be allowed to
> create tables, create views or create any stored procedures
> I could do this manually through the GUI, but with over 200 stored procs per
> table, this is too time consuming. I could script, but this is also something
> labor intensive, am looking for an easier way.
> Thank you in advance.
> Sam|||Create a new role in each database.
Grant the appropriate permissions to this role. (I know it's time
consuming, but it's a one time deal).
Then add your new users to this role in each db.
If you check around, there have been several scripts already created that
will grant permissions. You just need to tweak them a bit. My guess is
that in an hour worth of your time, you will have the script completed.
This newsgroup has had several posted to it within the last two weeks.
Rick Sawtell
MCT, MCSD, MCDBA
"Sam" <Sam@.discussions.microsoft.com> wrote in message
news:E7839D71-7EC7-47A0-96DF-4A62DAEED1D0@.microsoft.com...
> I have a production server with a few databases. Each database has about
> 100-150 tables, and 200 or so Stored Procs.
> I have a new user john that gets created on each database. I need to give
> him the ability to select, insert, update and delete from any user table,
and
> to be able to execute any Stored Procedure.
> If I grant this new user membership to db_datareader, he gets select from
> any user table.
> If I grant this new user membership to db_datawriter, he gets insert,
> update, delete to any user table.
> So far, so good. Now the problem -
> What role can I grant that would allow this user to be able to run any
> stored procedure? Note that my manager will not let me grant him db_owner,
as
> this gives him too much rights.
> And what every I do to give him the above rights, he can't be allowed to
> create tables, create views or create any stored procedures
> I could do this manually through the GUI, but with over 200 stored procs
per
> table, this is too time consuming. I could script, but this is also
something
> labor intensive, am looking for an easier way.
> Thank you in advance.
> Sam

Granting permission on multiple stored procs

Is there a better way of granting permission to a stored proc for a selected user other than (enterprise manager) select sp then accessing propertys then permissions, then user?GRANT EXEC ON <proc> TO <MyUser>

just about anything you can do in the EM can be scripted saved as script and used again. which makes you more efficient.|||but the rest of the story is this...
the project is a work in progress thing. we are added new stored procs as needed (up to 83 as of this morning) and now we wanted a selected user to test the new project. I was looking for a way to not have to type out all 83 stored proc names in order to grant access to them for this user. I was hoping for a solution/feature that i was not aware of in EM. so it looks like i will have to do this process manually either way (EM or Script)...|||select 'grant execute on ' + name + ' to user'
from sysobjects
where type = 'P'
and crdate > getdate() -1|||You are using Database Roles, I hope.

Granting EXECUTE permissions to all stored procedures

I want to allow my user to have exec permissions on all stored procs in the
database. Is there a quick way to do this? Right now, the only way I know ho
w to do this is to go into the permissions for the user and check the EXEC c
heckbox for each individual
stored proc...Hi
Execute the following in Query Analyzer with text result (Query menu click
result in text) and copy and paste results
to give you the required script.
select 'grant exec on ' + QUOTENAME(name) + ' to [user_name]'
from sysobjects where type = 'P'
and objectproperty(id,'IsMSShipped')=0
Note:
Replace the user_name with actual user name or role name.
Tahnks
Hari
MCDBA
"DBA72" <anonymous@.discussions.microsoft.com> wrote in message
news:77702A18-57F2-4B75-B6AC-B4D769DA8951@.microsoft.com...
> I want to allow my user to have exec permissions on all stored procs in
the database. Is there a quick way to do this? Right now, the only way I
know how to do this is to go into the permissions for the user and check the
EXEC checkbox for each individual stored proc...

Grant execute on many procs in 2005

I need to grant execute permissions to many stored procs to a user. How do
you accomplish this in 2005?Cheep and cheerfull method: -
execute
select 'GRANT EXECUTE ON ' + ROUTINE_NAME + ' TO <<YourUser>>' from
information_schema.routines
grab result from results pane and execute them
Paul
"Andre" <Andre@.discussions.microsoft.com> wrote in message
news:3ABC3458-7AC0-4D8F-8C63-8CCB4CB9757B@.microsoft.com...
>I need to grant execute permissions to many stored procs to a user. How do
> you accomplish this in 2005?

Global Variables

Hi,

Is there a way to declare a persistent global variable in SQL Server?

I'd like my stored procs to fetch data in a different source depending on a debug (or development) variable.

For example, I'd like to be able to set a variable to either 0 or 1 (true or false) and have a static SP defined as:

IF @.MYVARIABLE = 1
SELECT * FROM Openquery(Server1, 'SELECT * FROM Table1")
ELSE
SELECT * FROM Openquery(Server2, 'SELECT * FROM Table2')

What do you think? Since these SPs should be called a lot, I don't want to store the info in a table, I want it as a global variableso it will be as fast as possible.

Any other suggestions are also welcomed.

Thanks,

Skip.Wouldn't you just put this into a table? Then add the statement:

DECLARE
@.Environment bit

SELECT @.Environment = Environment FROM tblFlags

IF @.Environment = 1
SELECT * FROM Openquery(Server1, 'SELECT * FROM Table1")
ELSE
SELECT * FROM Openquery(Server2, 'SELECT * FROM Table2')

But wouldn't it be better to have a dedicated test server with data refreshed as you require? Sorry, may not always be realistic, but just a thought.

Regards,

hmscott|||First of all, yes having a dev server makes way more sense. Actually, this is what we have here, the switch (the if...else) will be used to create the linked server to point to the correct server instance. The example above was only a reference and did not represent an actual situation.

Second, what I have now is a variable in a "commands" table. It works fine but the function is called so many times that it slows my system. I'd like to have a global variable in memory to speed up the process of reading its value.

Any other suggestions?

Thanks,

Skip.|||Nothing comes to mind. You can use the PINTABLE command to put that table into memory, but if it is called so frequently, it's probably already there.

You said you have a "commands" table, but that the function (what function?) is called so often that it slows the system. Just to check, is the table very large? Is it properly indexed? What do you consider to be heavy usage?

We have a particular sp that is called more or less each time a user hits a page on our site (to pull back configuration and web settings). We have about 300 users and about 2000 page hits per day (frequently more). I've never had any complaints about this particular sp running slowly.

Regards,

hmscott