Stored Procedures/ table permissions

Mark

Hi, is there any way that I can automate granting user permissions to
tables/ stored procedures in SQL server 2000?

I have a whole bunch of tables and rather than having to right click each
table/ then permissions in Enterprise manager I would like to be able to
iterate through each table object in a database and grant the relevant
permissions.... Same with stored procedures.

Is this possible?? If so, how can I do it

Thanks in advance
Mark

Jul 20 '05 #1

Subscribe Reply

5588

Mark

Hi all, I have just found a solution to my own problem

http://www.sql-server-performance.co...ecurity_sp.asp

Cheers
Mark
"Mark" <ma**@hotmailNOSPAMTHANKX.com> wrote in message
news:m6**********************@news.xtra.co.nz...

Hi, is there any way that I can automate granting user permissions to
tables/ stored procedures in SQL server 2000?

I have a whole bunch of tables and rather than having to right click each
table/ then permissions in Enterprise manager I would like to be able to
iterate through each table object in a database and grant the relevant
permissions.... Same with stored procedures.

Is this possible?? If so, how can I do it

Thanks in advance
Mark

Jul 20 '05 #2

Erland Sommarskog

Mark (ma**@hotmailNOSPAMTHANKX.com) writes:

Hi, is there any way that I can automate granting user permissions to
tables/ stored procedures in SQL server 2000?

I have a whole bunch of tables and rather than having to right click each
table/ then permissions in Enterprise manager I would like to be able to
iterate through each table object in a database and grant the relevant
permissions.... Same with stored procedures.

Is this possible?? If so, how can I do it

SELECT 'GRANT SELECT ON ' + name + ' TO whomever'
FROM sysobjects WHERE type = 'U'

SELECT 'GRANT EXEC ON ' + name + ' TO whomever'
FROM sysobjects WHERE type = 'P'

Cut and paste result. Use Query Analyzer for the operation.

If you want to skip the cut-and-paste part, you could set up a cursor
and use dynamic SQL.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Jul 20 '05 #3

Similar topics

7954

Inter-database stored procedures and permissions

by: TJ Olaes | last post by:

Hello all, this is my second post to this newsgroup. It's a question about stored procedures and permissions and how these behave between databases. Here's the scenario. I have a database that...

Microsoft SQL Server

1424

Stored Procedure Security Question

by: Martin Feuersteiner | last post by:

Dear Group I'm having two stored procedures, sp_a and sp_b Content of stored procedure A: CREATE PROCEDURE dbo.sp_a SELECT * FROM a GO Content of stored procedure B:

Microsoft SQL Server

1654

More Info: Stored Procedure Security Question

by: Martin Feuersteiner | last post by:

Dear Group I have found that table A had SELECT permissions for 'Public' but not table B. Giving 'Public' SELECT permissions on table B did the trick. HOWEVER, I don't want anyone to be able...

Microsoft SQL Server

3053

How to Identify Permissions for SQL Server Tables & Stored Proc. via VB Code

by: Brad H McCollum | last post by:

I'm writing an application using VB 6.0 as the front-end GUI, and the MSDE version of SQL Server as the back-end (it's a program for a really small # of users --- less then 3-4). I'm trying to...

Microsoft SQL Server

1968

Still Trying to Figure Out Identification of SQL Server Stored Proc. Permissions via VB

by: Brad H McCollum | last post by:

I've looked through many suggestions and partial examples all over this newsgroup and still am not coming up with anything that does specifically what I'm wanting to accomplish. I'm writing a VB...

Microsoft SQL Server

2605

SQL Injection - Stored Procedures

by: Bã§TãRÐ | last post by:

I have been working on this particular project for a little over 2 weeks now. This product contains between 700-900 stored procedures to handle just about all you can imagine within the product. I...

ASP / Active Server Pages

9726

Stored Procs and db_owner

by: db55 | last post by:

I have some users that I need to run stored procedures, but they can't seem to run them unless they are in the db_owner role of the database. How do I give them access to run the stored procs...

Microsoft SQL Server

3358

SELECTs vs Stored Procedures

by: John | last post by:

Hi When developing vb.bet winform apps bound to sql server datasource, is it preferable to use SELECTs or stored procedure to read and write data from/to SQL Server? Why? Thanks Regards

Visual Basic .NET

3429

ObjectDataSource - problem inserting empty field into stored proce

by: Dabbler | last post by:

I'm using an ObjectDataSource with a stored procedure and am getting the following error when trying to update (ExecuteNonQuery): System.Data.SqlClient.SqlException: Procedure or Function...

ASP.NET

7007

Changing the language in Windows 10

by: Hystou | last post by:

Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...

Windows Server

7174

Problem With Comparison Operator <=> in G++

by: Oralloy | last post by:

Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...

C / C++

6894

The easy way to turn off automatic updates for Windows 10/11

by: Hystou | last post by:

Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...

Windows Server

7388

Discussion: How does Zigbee compare with other wireless protocols in smart home applications?

by: tracyyun | last post by:

Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...

General

4600

Couldn’t get equations in html when convert word .docx file to html file in C#.

by: conductexam | last post by:

I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...

C# / C Sharp

3091

Windows Forms - .Net 8.0

by: adsilva | last post by:

A Windows Forms form does not have the event Unload, like VB6. What one acts like?

Visual Basic .NET

1427

transfer the data from one system to another through ip address

by: 6302768590 | last post by:

Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated ...

C# / C Sharp

665

How to add payments to a PHP MySQL app.

by: muto222 | last post by:

How can i add a mobile payment intergratation into php mysql website.

PHP

297

Comprehensive Guide to Website Development in Toronto: Expert Insights from BSMN Consultancy

by: bsmnconsultancy | last post by:

In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence...

General