.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
sivanagamahesh
Post New Web Links

SQL Server 2005 Grant User Permission to Create Stored Procedure and Grant Execute Access to another

Posted By:      Posted Date: November 20, 2010    Points: 0   Category :Sql Server
 

I'm trying to determine the minimal amount of access I can give my developers to be able to run create stored procedure scripts with an inclusion of the grant execute to the application user group.

We have been developing some interesting work arounds, but I think there must be an easier way to grant the access.

Grant Create Stored Procedure to user1 on a specific database

Grant Permission to Grant Execute permission for Stored Procedures on a specific Database to user1 for any user.

 




View Complete Post


More Related Resource Links

how to create stored procedure in sql server 2005 and how to use that stored procedure in asp.net

  

Hi friends ,

       I am very newbie

I have created stored procedure and student database and also asp.net application for asp.net page

but it could not found stored procedure what is the mistake actually I don't no

Please help me



Warm Regards

Durga


GRANT permission on schema to user in SQL 2000

  
Hi,I am using following statement to grant specified permissions on schema to a user in SQL Server 2005. GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE on ::[Schema Name] to [My DB User]I need to perform the same in SQL 2000. However, this does not work in SQL Server 2000. Is there any equivalent syntax which can be used in both (SQL 2000 & 2005).Thanks in advance...

SQL Server 2005 - Specifying Optional Parameter in Stored Procedure

  
I need to alter the following stored procedure so that the UserID is an optional parameter. I have, as yet, been unsuccessful. Basically, if the user doesn't supply a User ID, I want to return all of the users that match the other criteria.

This seems like it should be a simple thing.......

ALTER PROCEDURE [QMS].[GetQueries]

@CompanyID tinyint

,@DepartmentID tinyint

,@ApplicationID int

,@UserID AS



SELECT [QueryID]

,[UserID]

,[QueryName]

,[Comments]

,[CreateDate]

,[UpdateDate]

,[ExpirationDate]

,[LastAccessedDate]

FROM [ReportQueries]

WHERE

[UserID] = @UserID

how to create stored procedure in sql server ???

  

here i have 3 attributes i want to write a sp it should take 1input value by tht i get remaining 2out put values

 table name: Employee

attributes: id,name,sal;

help me in this....


if this exected i should get succecss msg also and boolen is true...

else


flase and not exectuted msg...


also


With Recompile and Execute as clause in SQL 2005 Stored Procedure

  

Hi,

I am working on an asp.net application which uses quite a few sql 2005 procedures. Due to some change in requirement I had to modify a couple of the procedures.

During this change, I found that most of the procedures contain clause With Recompile, Execute As Caller.

I am planning to refactor the code in procedure but not sure if I can remove these clauses. Can any one please help me with these?

Thanks in advance.


SQL Server 2005 permissions error. The EXECUTE permission was denied on the object "xp_instance_reg

  

I have a SQL Server host running SQL 2005 9.00.4294 x86 Standard Edition running on Windows build 2195 SP4.  My client workstation is running only SQL Server 2005 workstaion components.   When a user of the host who has db_owner access attempts to view the properties of a table by right-clicking the table and then clicking Properties, the following error mesage is displayed:  

"The EXECUTE permission was denied on the object "xp_instance_regread", database 'mssqlsystemresource', schema 'sys'

For security purposes, I do not want to grant execute on xp_instance_regread to these users. Does anyone know of a workaround that will allow members of db_owner to access table properties using the abovementioned method and does not require execute access to be granted?

 

 

 


Execute permission denied on with the grant and with grant permission enabled. HELP PLEASE. Grantin

  

Here is the code that Im running.  The user Tester has the both Grant and With Grant checked.

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'AAAtestOW')

BEGIN

DROP Procedure dbo.AAAtestOW

END

GO

CREATE Procedure dbo.AAAtestOW

AS

select

'aaa'

GO


If i add the grant execute command for me I get
"Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself."

If I try

CRUD Stored Procedure Code/Scripts Generator for SQL SERVER 2005

  

I need a simple anf functionally CRUD Stored Procedure Code/Scripts Generator.

Anyone have a solution?

Thanks in Advance.


Microsoft Accounting Professional 2009 - controlling user access on SQL Server 2005

  

I have MS Accounting Professional 2009 installed on Windows Small Business Server 2008 with the accounting database in SQL Server 2005

I can access the database on the server using my client installation of MS Accounting and use it as expected

When I add another user I am unable to restrict the access rights of that user by selecting a role for them and restricting their access by denying them access to certain areas or providing read-only access to certain areas

What happens when we select a user and select a role and un-tick the Administrator rights box and click ok is that the user rights are not saved without administrator rights.  When the user's rights are opened again, the Administrator box has a tick in it again.

The sequence of events is exactly as described - untick the Administrator box, click ok, then select the user in the "Manage Users" list again, click "Edit" and there's a tick in the "Administrator" box.

In every other respect, MS Accounting appears to work ok, i.e. no error messages, no crashes.

We believe that we should be able to work around this issue by manually setting user access rights directly in the SQL database, but so far we have not been able to do that.

Has anyone ever experienced anything like this, can anyone provide any help or suggestions?


How to grant Anonymous permissions to Limited Access User.

  

The problem is granting a user access to only a single Document Library deep within a site;  this user can navigate directly to this document library, but this user can no longer see any anonymous content.

How can a limited access user also maintain the anonymous permissions before signing in? 


Execute stored procedure on linked DB2 server from MS SQL 2008 SP1 64 bit problem

  
Hi everybody. I am trying to execute stired procedure on linked DB2 server from MS SQL 2008 x64
I installed IBM Access client x64 and on provider tab showed up 3 providers IBMDASQL,IBMDA400,IBMDARLA
I installed linked server as shown on this two links:

1)http://www.mcpressonline.com/database/db2/db2-integration-with-sql-server-2005-part-i-linked-server-enhancements.html
2)http://stackoverflow.com/questions/329051/execute-db2-iseries-stored-procedure-from-a-sql-2005-linked-server

I installed linked server using all this 3 providers

sp_addlinkedserver
    @server=N'DB2',
    @srvproduct=N'DB2 UDB for iSeries',
    @provider=N'IBMDASQL',-- provider for example
    @datasrc=N'ASTEST', -- mydatasource
    @catalog='S65CF29B'
go
exec sp_addlinkedsrvlogin DB2,false,null,'telebank','password'

I can run procedure from the extended stored procedure, but when i try to execute it as shown on the linkes above i get the following error:
Could not execute statement on remote server 'DB2'.


declare
  @branch    as varchar(4),
  @cli   as varchar(6),
  @suffix    as varchar(3),
  @date1     a

SQL Server 2005 Transactional Replication Fails to Publish Stored Procedure Containing an Index Crea

  

I've experienced a bizarre problem with a SQL Server 2005 Transactional Publication. The issue is this: If the publication contains an article that is a stored procedure that contains a create index statement, then there is an error thrown when attempting to replicate the schema of the stored procedure to a subscriber.

The behavior is very odd, because even if the create index statement is commented out, it still gives the exception, and it will only work if it is removed altogether.

Here is the exact error that's being returned:

Command attempted: GRANT EXECUTE ON [dbo].[usp_Test] TO [CompanyDatabase_access]

(Transaction sequence number: 0x00000170000008B9000500000000, Command ID: 5)

Error messages: Cannot find the object 'usp_Test', because it does not exist or you do not have permission. (Source: MSSQLServer, Error number: 15151) Get help: http://help/15151 Cannot find the object 'usp_Test', because it does not exist or you do not have permission. (Source: MSSQLServer, Error number: 15151) Get help: http://help/15151

The error is accurate, because when I check on the subscriber, the stored procedure wasn't created as expected... but that was the purpose of the publication...

Calling stored procedure from funciton in sql server 2005

  

Is there any way to call stored procedure from funciton in sql server 2005?

If it is possible, please give some examples.

Thanks.

 


RAHUL Dot Net Developer

Send Email from SQL Server Express Using a CLR Stored Procedure

  
Problem
One of the nice things about SQL Server is the ability to send email using T-SQL. The downside is that this functionality does not exist in SQL Server Express. In this tip I will show you how to build a basic CLR stored procedure to send email messages from SQL Server Express, although this same technique could be used for any version of SQL Server.

Solution
If you have not yet built a CLR stored procedure, please refer to this tip for what needs to be done for the initial setup.

Create stored procedure from asp.net

  

Hi,

we are creating a custom report tool, which could be used for generate the report as per end user's needs. In that we are providing an option as user could create a query and procedure as well.

In sql server we can use "EXEC" function for execute dynamic query.

Could anyone help me for create the dynamic query in Oracle?

I just tried with "execute immediate", which would throws error as 

"insufficient privileges".

Please help me.

 

 


TSQL: Passing array/list/set to stored procedure (MS SQL Server)

  
Passing array/list/set to stored procedure is fairly common task when you are working with Databases. You can meet this when you want to filter some collection. Other case - it can be an import into database from extern sources. I will consider few solutions: creation of sql-query at server code, put set of parameters to sql stored procedure's parameter with next variants: parameters separated by comma, bulk insert, and at last table-valued parameters (it is most interesting approach, which we can use from MS SQL Server 2008). Ok, let's suppose that we have list of items and we need to filter this items by categories ("TV", "TV game device", "DVD-player") and by firms ("Firm 1", "Firm2", "Firm 3). It will look at database like this So we need a query which will return us list of items from database. Also we need opportunity to filter these items by categories or by firms. We will filter them by identifiers. Ok, we know the mission. How we will solve it? Most easy way, used by junior developers - it is creating SQL-instruction with C# code, it can be like this List<int> categories = new List<int>() { 1, 2, 3 };   StringBuilder sbSql = new StringBuilder(); sbSql.Append( @" select i.Name as ItemName, f.Name as FirmName, c.Name as CategoryName from Item i inner join Firm f on i.FirmId =
Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend