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

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links


Posted By:      Posted Date: September 21, 2010    Points: 0   Category :Sql Server

Sorry if this is a repeat question but I dont know what I am doing wrong here. I have created a Stored Proc which when you pass in a parameter of SQL Login, it then passes through every database on the server and returns all the perms for that SQL Login on every database. This is a request from one of our Managers you wants to be able to see a users database perms at any point in time by just running the proc. As he is not 'sa' himself, my idea was to create a proc the set it to EXECUTE as SA. So that whenever he ran it he would get the info he needs.

So my proc works fine when I run it as I am 'sa'. I now need to add the "Execute as SA" command but it wont let me as follows

CREATE PROCEDURE dbo.usp_CheckUserPermsAll 
	-- Add the parameters for the stored procedure here
	@user varchar(255)



	-- SET NOCOUNT ON added to prevent extra result sets from

View Complete Post

More Related Resource Links

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


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.


Create stored procedure from asp.net



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.



Execute system stored procedure

This should be very simple, but I haven't found the solution yet.  I'm writing a Q&D application to setup log shipping for a large number of databases.  I need to execute several stored procedures (e.g. sp_add_log_shipping_primary_database) which will return a value plue two output parameters that I need.  I've taken the code generated by SQL Server and executed it in a SMSS query window.  I've tried configuring an ADODB command with EXEC sp_name parm1, parm2, ..., parm 12 OUTPUT and setup parameters on the command without success (The connection cannot be used to perform this operation.).  I tried stringing all of the statements needed together in one line separated by semi-colons (executed without error but didn't return any values). I'm using VB2010 and SQL Server 2008.  Any suggestions would be appreciated.

can't execute "create table" command with Visual Web Developer express

I have a folder with a web site I'm developing which is connected to a database.Every time I move the folder to a different computer it's a pain when I have to move the database and change the connection string.I've thought to use for the first time a database in the app_data folder and move all the database inside it.Nevertheless, when I try to recreate all the tables in the new database for the first time with the query "create table ..." I get and error saying that "SQL CREATE TABLE" is not supported.Is there a way to manage this situation?--   Giuseppe

Execute SSIS package stored in Database - From Stored Procedure

Dear frnds, I am trying to execute a SSIS package that is stored in a SQL Server 2005 database Want to execute from a stored procedure in same database.  What commands/operations are necessary ? I am also having Two parameter. Regards, sajid 

Need to create a stored procedure on a db if that db exists..

IF   EXISTS (SELECT * FROM sysdatabases WHERE [name] = 'test') BEGIN use test Create   Procedure test @empno int, @empname varchar (50), @loc varchar (50) as print   'it is a msg' end   getting the error msg Msg 156, Level 15, State 1, Line 6 Incorrect syntax near the keyword 'Procedure'. I need to deploy this sp  only if that db exists to around 400 db servers in a batch... I heard within begin.. end .. we should not include DDL's .. but i need to find a work around...  thanks   

Need to create a update procedure

I have to update 2 records in a table, sounds simple, but i need to do this after the first record is updated, then using the data from that record, look up the other record thats in the table to update it with bits of the first records data. What it boils down to is that we inherited a database, that wasnt designed all that great..so we are looking to update the 2nd record based on data within the 1st record. How can i build a one time update, to run against the entire table, and it needs to determine what the other similar record is then update that one with the 1st data.Here is a sample of what the table data looks like:t_id s_id due_date date_completed 331724 82043 7/21/2009 NULL 349084 82043 10/21/2009 7/21/2010 378073 82043 1/21/2010 7/2/2010 394205 82043 4/21/2010 7/14/2010 413299 82043 7/21/2010 7/23/2010 432701 82043 10/21/2010 7/23/2010 341201 87188 7/21/2009 9/4/2009 349123 87188 10/21/2009 12/7/2009 380062 87188 1/21/2010 7/22/2010 394382 87188 4/21/2010 3/11/2010 413354 87188 7/21/2010 6/11/2010 432710 87188 10/21/2010 7/23/2010 So what i need to do is in the above example is update t_id 331724 with the date_completed from 341201. But since there is nothing to link them together in this table, how can the update be written so that t

want to create common Stored procedure

Hello, I have more than 20 tables, for this i want to create a common Stored procedure which fetches data by id Column So I tried like this,but it doesnt work ALTER PROCEDURE [dbo].[ERS_SP_GetAllTableDataByID] -- Add the parameters for the stored procedure here @TableName nvarchar(100), @ColumnName nvarchar(100), @ColumnVal nvarchar(100) AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here DECLARE @ExecQuery nvarchar(100) SET @ExecQuery =@TableName -- Insert statements for procedure here select @ExecQuery = 'SELECT * FROM [' + @TableName + '] WHERE ' + '[' +@ColumnName +']=' + @ColumnVal exec (@ExecQuery) END please help me out Regards,

create procedure [User_Name].sp_myprocedure. Error since I use dbo schema

I am developing with VS2010. When I generate a store procedure (DATASET>RIGHT CLICK ADD>SELECT>NEW STORE PROCEDURE) for a data table of a dataset from VS2010, the enviroment generates this: IF EXISTS (SELECT * FROM sysobjects WHERE name = 'SelectQuery' AND user_name(uid) = 'DOMAIN\USER-LOGIN')  DROP PROCEDURE [DOMAIN\USER-LOGIN'].SelectQuery GO CREATE PROCEDURE [DOMAIN\USER-LOGIN'].SelectQuery AS  SET NOCOUNT ON; SELECT contract_id, product_id, contract_description FROM dbo.TContracts GO The creation of the procedure ends with erros since my schema is dbo. So far, I work around copying and pasting the store procedure changing the schema and starting again from the beginning: DATASET>RIGHT CLICK ADD>SELECT>FROM EXISTING STORE PROCEDURE. Is there any way to configure VS2100 to generate the scripts avoinding to prefix the supposed schema of my user. Many thanks.

How to use Store Procedure to execute the Function Of COM DLL (the DLL is Coded By C#)

Hi All, I find a question. How to use Store Procedure to execute the function of Dll? I tried as the steps: 1. Code a dll 2. Register the dll by "regasm XXX.dll /codebase" 3. Use the dll in SP But when I do it in step 3, I face some error, which is "Invalid class string" and the error ID is "0x800401F3" Could you help me solve this problem? Thank you very much.

How to create a second independent transaction inside a CLR stored procedure?

I use the context connection for the "normal" work in the CLR procedure.But I need to commit some data in every case. So I need to create a second transaction which is independent from the calling transaction.What is the best way to do this inside a CLR procedure?Thanx a lot

Error when trying to create a stored procedure

Hello allI am using c# to create a new stored procedure and i keep getting a stupid failed operation exception. This is the code i use static private void CreateProcedure(string connectionString, string userName, string password, string DTName, List<DataColumnsObj> parametersIn, List<DataColumnsObj> parametersOut) { SqlConnection connection = new SqlConnection(connectionString); ServerConnection serConnection = new ServerConnection(connection.DataSource); serConnection.LoginSecure = false; serConnection.Login = userName; serConnection.Password = password; Server server = new Server(serConnection); string storedProcedureName = _procedureName; StoredProcedure sp = new StoredProcedure(server.Databases[connection.Database], storedProcedureName); sp.Refresh(); try { sp.Drop(); sp = new StoredProcedure(server.Databases[connection.Database], storedProcedureName); } catch (Exception ex) { } sp.TextMode = false; sp.AnsiNullsStatus = false; sp.QuotedIdentifierStatus = false; AddParametersStoredProcedure(sp, parametersIn, false); if (parametersOut != null) { AddParamet

Create store Procedure for select Table by Passing Table Name In Parametere

I want to create a Procudure to Select all Information in Table Like (select * from TableName) but Condition is I do not want to Mention The Fix Table Name I will send the Table Name In a parameter from My Vb.net Project like This (select * from @Parameter ) and Then The Select Procudure work with on That Table. It will be change depend on my vb.net reqierment I already Created a Procudure But it Does not working. It is in below. alter proc AllSelect (@Table varchar (30)) as DECLARE @sql nvarchar(MAX) SET @sql = ' Select * from '+@Table+' ' If possible Please let me know also Update and Delete proc by same way. with Regards Suman Bangladesh

Calling Oracle procedure from SSIS 'Execute SQl task' is not working


Hi ,


Iam using 'Execute SQl task' which calls a stored procedure located in sql server database.The task's SQL source type is variable and the variable has the follwoing expression "EXEC PROC_SEL_MBO_REPORT "+@[User::V_SP_Job_Date]after evaluation it is like EXEC PROC_SEL_MBO_REPORT '01/NOV/2007'.It is working fine

Now the procedure is changed to Oracle.So I have changed it to "BEGIN  PROC_SEL_MBO_REPORT " + "("+ @[User::V_SP_Job_Date]+")"+"; END"+";" after evaluation it is like BEGIN  PROC_SEL_MBO_REPORT ('01/NOV/2007') END;.It is sucessfully executing from the task but no data is loaded into the tables which are used by the procedure internally.
Executing  'execute BEGIN  PROC_SEL_MBO_REPORT ('01/NOV/2007') END;' is perfectly alright from SQl developer or sql plus.

Please help me.. thanks in advance




can I create a job to call a stored procedure?




Can I create a job to call a stored procedure everyday? If yes, how to do it?



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...


flase and not exectuted msg...


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