Hey guys,
I'm pretty new to SQL configuration, and I need to give EXECUTE
persmissions for one of the SQL user roles. I am running SQL 2005
Management Studio Express - free version. I found the list of my
stored procedures, but I can not locate any permissions screen. Can
someone help point me in the right direction? Thanks!
HI,
would like to know how to give execute permissions for all the stored procedures in a database at one shot. please advise.
How can i add Execute permissions on the Stored Procedures under thecreated user permission iusing SQLDMO ?
View 1 Replies View RelatedGood evening:
When assigning permissions to logins/roles, etc., does a login/user with rights to a stored procedure need rights to all of the tables and views that it accesses?
In other words:
If you create a login/user with rights to 3 stored procs, but deny access to the same user to the tables and/or views that the SP uses, will the sproc still run?
Stupid question? Sorry if it is.
** Future Daddy
Is there any way by which I can grant the same permissions to all of my stored procedures by one command?
Thanks,
Adita
Till yesterday I was able to execute the stored procedure sp_cycle_errorlog.
I am member of Domain Admin on NT.
I am member of sysadmin server roles on SQL Server 7.0 (SP1).
Now I receive the following error:
Server: Msg 15003, Level 16, State 1, Line 0
Only members of the sysadmin role can execute this stored procedure.
I have already stop and restart SQL Server.
Any idea?
Thank you.
i have a stored procedure that calls another stored procedure which does the following:
exec @hr = sp_OACreate 'MSXML2.ServerXMLHttp', @obj OUT
if @hr < 0
begin
/* Raiserror('sp_OACreate MSXML2.ServerXMLHttp failed', 16, 1) */
return
end
exec @hr = sp_OAMethod @obj, 'Open', NULL, 'GET', @sUrl, false
now if I execute the first sp in management studio it doesn't error but if I call from asp it does error.
I gave the user permission to the first sp -- so I need to give permi
Using Server Management
Studio Express and SQL Server 2005 Express - is it possible to assign
Exec permissions for users on a sproc by sproc basis. If so, how do I
do this?
TIA
--
Mike Brind
I have an app which calls a SP, which in turn calls a CLR Stored Procedure.
The CLR stored procedure calls a number of different tables, using a Context connection string.
The issue is that the CLR SP requires the user to have permissions to the tables directly, instead of just permissions to the SP which was expected.
If I just give permission to the SP, then the CLR SP fails. So I then add the table permissions, and it then works.
So the question is, how do I raise security so the app does not have permissions on the tables?
I need to understand the permissions that a CLR stored procedure needs when it accesses tables. In a dbo TSQL stored procedure it has owner permissions on all dbo tables, so there is no need to grant permissions on tables to the database user.
Some developers recently implemented a CLR stored procedure that returned an error with update permission denied on table name. Once I granted the user account update permission on that table, it was able to execute OK.
I have been looking for a good explanation for the way the permissions to database objects need to be setup for CLR stored procedures. For example, could I have said to modify the procedure to use the EXECUTE AS clause, instead of granting the user account direct permission on the table? Does anyone have any links to good articles on this subject?
Edit:
I have a feeling I'm on my own trying to figure out how this works. I've been searching the web for hours, and I haven't found anything that directly addresses this.
CODO ERGO SUM
Hi, is there any way that I can automate granting user permissions totables/ stored procedures in SQL server 2000?I have a whole bunch of tables and rather than having to right click eachtable/ then permissions in Enterprise manager I would like to be able toiterate through each table object in a database and grant the relevantpermissions.... Same with stored procedures.Is this possible?? If so, how can I do itThanks in advanceMark
View 2 Replies View RelatedBit of an emergency!
I do not have direct access to our SQL Server but I have full FTP access to the web server and have the db Username/passwords.
I need to grant execute permissions on a stored procedure, can I do this from an asp/ASP.NET page?
The DB guys take 24 hours to run a script against the database!
Any help would be greatfully recieved.
Rich
In our development and test environments the developers need to create and execute stored procedures as dbo without having any other dbo permissions. If I place them in db_owner, they have too many permissions. Is there a way to address this situation?
I'm also curious how other companies address the subject of creating stored procedures in development and test environments. If I give developers create and execute permission in a database, all objects would be created as JohnDoe.storedprocedurename instead of dbo.storedprocedurename. Any help in this area is appreciated.
Dave
I have a DataSet (Data Component in Beta 1) and I want to add Fill and
Get methods by using a Stored Procedure that was created by VS 2005 (aspnet_Membership_GetAllUsers). I probably need to use Enterprise Manager to do so but I am not sure what permissions I need to set and how to set them.
Hello all, this is my second post to this newsgroup. It's a questionabout stored procedures and permissions and how these behave betweendatabases.Here's the scenario. I have a database that stores information for asystem "A", and I have a different database on the same SQL serverthat stores the login and other info "LOGIN". I write a storedprocedure in the "A" database that checks some tables in the "LOGIN"database, let's call this "SP_A".Additionally I have a user account that accesses all appropriatestored procedures in "A" called "USER_A", and the same for the "LOGIN"database, "USER_LOGIN".Here's the part that raised my curiosity. I log into the server viaQuery Analyzer using the "USER_A" account. I run "SP_A" which does ajoin between some table in "A" and another table in "LOGIN". I give"USER_A" execute permission on "SP_A", then I try to run "SP_A" andget an error:SELECT permission denied on object '(table in "LOGIN" database)',database '(real name of "LOGIN")', owner 'dbo'Huh? how come I need to assign additional select permissions in thisdatabase if I'm not doing an actual select statement? I'm not evendynamically running a select statement through an exec function. Thisjust struck me as odd, seeing as how I never explicitly set SELECTpermission on any table in "A" for "USER_A", yet my stored procedureworks, but between databases I have to assign extra permissions for astored procedure "SP_A" access to the tables in "LOGIN".Anyone able to explain this behavior? Because I'm at a loss and I'veonly been doing this DB thing for about 2 years.Thanks in advance, all.-TJ
View 4 Replies View RelatedAll,
Here is my problem, its very simple, But I dont have a solution.
To run / import / what ever I else I forgot (?) SSIS in SQL SERVER, what are permissions I'll need.
So far I have developed everything in BIDS, when I try to migrate it to a sql server by using Import package in Integration Services I got the below error.
TITLE: Import Package
------------------------------
The EXECUTE permission was denied on the object 'sp_dts_listpackages', database 'msdb', schema 'dbo'. (Microsoft SQL Native Client)
------------------------------
ADDITIONAL INFORMATION:
The EXECUTE permission was denied on the object 'sp_dts_listpackages', database 'msdb', schema 'dbo'. (Microsoft SQL Native Client)
The error is very clear in itself, While I have raised a request for the execute permission of this stored procedure, i also like to know what kind of permissions I will need in MSDB to work with out any problems. So that I dont have to go to DBA for execute permission for each error I may get for this.Right now I dont have execute permission on any of the Stored Procs in MSDB.
If any body can show any pointers that would be help full.
I have stored procedures. I have heard that it is a best practice to use stored procedures to encapsulate some SQL statements and then grant permissions to execute the stored procedure. But when I try this and use EXECUTE AS to test it out, the user in question gets errors about not having access to some of the underlying objects.
How does this best practice work? If I need to grant the user permissions to the underlying objects anyway, I am not sure why a stored procedure is considered best practice in this regard.
Hi
My website uses GET variables a lot and i'm trying to safe guard as much as possible against SQL injection attacks. I'm trying to create permissions which will deny a user to Delete/Insert/Update various tables.
I have managed this with the tables themselves, but when using a stored procedure, the tables do not take into account the user permissions which were set for that table!
Basically, how do i stop a stored procedure from Deleting/Inserting/Updating tables? :(
many thanks
I am writing a SQL 2000 stored procedure which uses an EXEC @sqlString statement. The @sqlString is generated at runtime. I want to give as few permissions as possible and currently allow users to access the database tables using only the stored procedures provided. However, with Exec I discover that I need to grant permissions on the actual tables to the users or groups. I would like to avoid this. I would also prefer not having to maintain a separate user with table level permissions and hardcoding the stored procedure with these details.
Is there anyway for me to dynamically generate the required SQL statement within my stored procedure and let SQL know that this stored procedure is allowed to select whatever tables it wants to without having to define permissions on the tables?
We have a generic sql login "prduser". Applications use this login. We want the login NOT to have ALTER PROCEDURE and DROP PROCEDURE permissions only on the stored procedures(there are thousands of them).
View 17 Replies View RelatedWhat permission is required to run debug feature in SSMS(debug Stored Procedures). This is a development machine and developer requested for this.
EXECUTE permission was denied on the object 'sp_enable_sql_debug', database 'mssqlsystemresource', schema 'sys'.
EXECUTE permission was denied on object 'sp_sql_debug', database 'master'.
Is there any option other than giving sysadmin privilege on SQL?
Hi all,
I would like to enable users that do not belong to groups (server roles) such as sysadmin, serveradmin and don't have db permissions such as ddl_admin or db_owner to run some of the system stored procedures (such as sp_addumpdevice sp_configure sp_serveroption ...) and DBCC commands (such as DBCC CHECKFILEGROUP - requires ob_owner or sysadmin permission).
Is it possible to change permissions of SQL Server system stored procedures?
Is it possible to change permissions of SQL Server DBCC commands?
Thanks,
Assaf
Hello SQL Experts,
we've got a Windows Server 2003 environment with SQL Server 2000 Sp 3.
A stored procedure selects specific data from a user-table which depend on the user executing it. The users are granted execute permission on the stored procedure. But execution fails, if the user is not granted select permission on the user-table, too.
The problem is, that the user must not have the permission on all data in the user-table but on the data concerning him.
In earlier versions of SQL Server and Windows the execute permission has granted sufficient rights to select from the underlying tables. How can this be re-established?
The Owner of sp and table is dbo.
Thanks for your replies!
I tried this morning to check some of the system stored procedures and ran into trouble. Only four of them executed: sp_alterdiagram, sp_creatediagram, sp_dropdiagram and sp_helpdiagramdefinition. I could not check all the rest, I did it selectively. The typical error message was: Invalid object on RETURN statement. Some had syntactical errors.
sp_ActiveDirectory_Obj
Invalid object name 'sys.sp_ActiveDirectory_SCP' Line 171
sp_ActiveDirectory_SCP
Invalid object name 'sys.sp_ActiveDirectory_SCP' Line 171
sp_ActiveDirectory_Start
Invalid object name 'sys.sp_ActiveDirectory_Start' Line 19
Invalid object name 'sys.sp_add_agent_parameter' Line 60
Invalid object name 'sys.sp_add_agent_profile' Line 123
Invalid object name 'sys.sp_add_data_file_recover_suspect_db' Line 17
sp_addalias
Msg 102, Level 15, State 1, Procedure sp_addalias, Line 44
Incorrect syntax near '%'.
Msg 195, Level 15, State 10, Procedure sp_addalias, Line 64
'get_sid' is not a recognized built-in function name.
Msg 102, Level 15, State 1, Procedure sp_addalias, Line 78
Incorrect syntax near '%'.
Msg 102, Level 15, State 1, Procedure sp_addalias, Line 119
Incorrect syntax near '%'.
sp_bindefault
Msg 102, Level 15, State 1, Procedure sp_bindefault, Line 95
Incorrect syntax near '%'.
Msg 102, Level 15, State 1, Procedure sp_bindefault, Line 134
Incorrect syntax near '%'.
Msg 102, Level 15, State 1, Procedure sp_bindefault, Line 182
Incorrect syntax near '%'.
Msg 102, Level 15, State 1, Procedure sp_bindefault, Line 208
Incorrect syntax near '%'.
Msg 102, Level 15, State 1, Procedure sp_bindefault, Line 228
Incorrect syntax near '%'.
Msg 102, Level 15, State 1, Procedure sp_bindefault, Line 264
Incorrect syntax near '%'.
Msg 102, Level 15, State 1, Procedure sp_bindefault, Line 273
Incorrect syntax near '%'.
sp_databases
Invalid object name 'sys.sp_databases'. Line 6
sp_tables
Invalid object name 'sys.sp_tables'
Is there a chance that these errors are due to the fact that I executed them without parameters?
Thanks
Hi
I am currently using SQL server 2005 express edition for a website I have created using Asp.Net 2.
For this website I call stored procedures that I have created in the databse to return any page data. However, I keep getting error messages say that the login does not have execute permission for the stored procedure.
In Sql Server 2005 there does not seem to be an easy way to grant permissions to a stored procedure as you add them. I say this because when I used Sql Server 2000 I would just add the stored procedure, rigth click on it and grant permission to the user.
Now this does not seem to be the case with the new version of sql server and I was just wondering whether there is now a new, easy way of doing this.
If anyone can point me in the right direction on this...
I have managed to get this working by going into the properties of the users atached to the database, adding a list of stored procedures to the "scalables" area and individually ticking the execute checkboxs. However, when I return to add a new stored procedure, the list has disapeared. Is this a bug with Sql server 2005?
Thanking you in advance
I have created multiple stored procedures that search different tables for similiar information.
Is it possible to have one main stored procedure that calls and executes each of these individual stored procedures and then use the UNION keyword to combine the results?
For example
Code:
CREATE PROCEDURE [dbo].[Return_Detail]
@IDVarChar(200)
AS
--Get the 1st Detail
EXECReturn_1st_Detail @ID = @ID
UNION
--Get the 2nd Detail
EXECReturn_2nd_Detail @ID = @ID
GO
I'm running this legacy DTS package under SQL Server 2005, and need to make some changes.
I have some (say, 3) stored procedures, and they all have one same parameter. Each SP returns a dataset. I need to import each dataset to a table using DTS. Both the SPs and the destination tables are in the same database.
So, I created an "Execute SQL Task" and typed in "SQL Statement" something like this:
INSERT INTO TABLE TABLE1
EXEC SP1 ?
GO
INSERT INTO TABLE TABLE2
EXEC SP2 ?
GO
INSERT INTO TABLE TABLE3
EXEC SP3 ?
GO
And the execution returns an error: No value given for one or more required parameters.
Is it possible to assign a parameter to all three SPs? What is the work out if I don't want to change the SPs, and I don't want to create 3 Execute SQL Tasks?
I surfed a lot of the internet piecing this together. I have a database that gets copied every night from a live database so users can run queries against it, so I needed to be able to recreate a stored procedure from within ASP.NET. Also, I needed to take information I was importing from a .CSV file and compare it using the stored procedure to find specific information and import it into another database. I was able to do this using the SQLDMO reference object.
Here are some clipits of what you need.
Imports SQLDMOPrivate myServer As New SQLServer
Dim serverName As String = System.Configuration.ConfigurationManager.AppSettings("Server").ToString()Dim userName As String = System.Configuration.ConfigurationManager.AppSettings("UserName").ToString()
Dim password As String = System.Configuration.ConfigurationManager.AppSettings("Password").ToString()
Dim storedProcedure As New StoredProcedure
Dim qresults As SQLDMO.QueryResults
Example Stored Procedure
Try
myServer.Connect(serverName, userName, password)
storedProcedure.Text = "IF EXISTS (SELECT * FROM sysobjects WHERE name='memb_proc' AND user_name(uid)='dbo') DROP PROCEDURE dbo.memb_proc"myServer.Databases.Item("<databasename>", "dbo").StoredProcedures.Add(storedProcedure)
storedProcedure.Text = "CREATE PROCEDURE dbo.memb_proc @lastname varchar(50) = '', @firstname varchar(50) = '', @zip varchar(5) = '', @membrowno varchar(50) OUTPUT AS Select @membrowno = rowno from memb where lastname LIKE @lastname and name LIKE @firstname and zip1 LIKE @zip "myServer.Databases.Item("<databasename>", "dbo").StoredProcedures.Add(storedProcedure)
Label1.Text = "Creation of stored procedure successful"Catch ex As Exception
Label1.Text = "Creation of stored procedure failed"
Finally
myServer.DisConnect()End Try
Run Stored Procedure and Obtain Results
myServer.Connect(serverName, userName, password)
qresults = myServer.ExecuteWithResults("USE [databasename] DECLARE @return_value int,@membrowno varchar(50) EXEC @return_value = [dbo].[memb_proc] @lastname = N'" & Last & "', @firstname = N'" & First & "',@zip = N'" & ZIP & "', @membrowno = @membrowno OUTPUT SELECT @membrowno as N'@membrowno'")For num = 1 To qresults.Rows
MemberRowNo = qresults.GetColumnString(num, 1)
Next
myServer.DisConnect()
Hello,
Is it possible to execute DBCC statements remotely in destination servers through stored procedures existing in source server.
I dont want to use isql, osql, openquery, openresultset because I have to pass user id or password.
I have linked the required SQL servers using Linked servers.
If so please give some example.
Thanks in Advance,
Sateesh
We are using a third party tool that does not store passwords in an encrypted format therefore we created a user with minimal rights. Isn't there a way to grant "execute any stored procedure" to a user/Login? Do we really have to grant execute on each procedure to the user? And then do the same for each new store procedure? The only other option we have found to be able to "see" and execute the stored procedures is by granting "db_owner". I would think that would negate the user being minimal rights.
View 5 Replies View RelatedI have a column in a table, which have the stored procedure name stored in each row. Now, I need to execute each SP in the table dynamically. I'm trying to construct a SQL but not able to fire them!!
DECLARE @sql VARCHAR(MAX)
SELECT @sql = STUFF((SELECT '; GO EXEC ' + StoredProcedureName + '' FROM MyTable FOR XML PATH ('')),1,5,'')
print @sql
EXEC sp_executesql @sql
Hi,
I have a problem with sp execution.:
objects of [dbo]
Tables of [nuran]
Grants of [nuran]
[dbo].tabloA
[nuran].tmptabloA
Select,insert, update on [dbo].tabloA
[dbo].tmptabloA
Deny for [dbo].tmptabloA
[dbo].sp_yordam
Grant for executing [dbo].sp_yordam
(1)
create PROCEDURE [dbo].[SP_yordam]
AS
BEGIN
BEGIN TRANSACTION @Tran1
¦¦¦¦¦. ¦¦¦¦¦¦ ¦¦¦¦.
INSERT INTO [tabloA]
(, ,)
SELECT ,,
FROM [tmptabloA] WHERE ......
¦¦¦ ¦¦¦ ¦¦¦.
DELETE FROM [tmptabloA]
COMMIT TRANSACTION @Tran1
When user [nuran] execute the procedure sp_yordam by a VB program, the procedure use [dbo].tmptabloA not [nuran].[tmptaboA]. If there are data in the [dbo].tmptabloA, the procedure insert data to [dbo].tabloA from [dbo].tmptabloA. But when I checked user name in the procedure during execution, the user was [nuran].
If I write the procedure like that:
(2)
create PROCEDURE [dbo].[SP_tmpSil]
AS
declare @tablo1 as varchar(50),
DECLARE @sil as nvarchar(max)
select @tablo1='[tmptabloA]'
SELECT @sil = ' DELETE FROM ' + @tablo1 + ';'
EXEC (@sil)
END
And it executed by user [nuran],then it used the correct table [nuran].tmptabloA
Is there any way to use users table in an stored procedure without using the user name :
(3)
create PROCEDURE [dbo].[SP_yordam]
AS
BEGIN
BEGIN TRANSACTION @Tran1
¦¦¦¦¦. ¦¦¦¦¦¦ ¦¦¦¦.
INSERT INTO [tabloA]
(, ,)
SELECT ,,
FROM [nuran].[tmptabloA] WHERE ......
¦¦¦ ¦¦¦ ¦¦¦.
DELETE FROM [nuran].[tmptabloA]
COMMIT TRANSACTION @Tran1
I don't want to use (2) and (3) code methods, I prefer to use (1) script. Is there any compilation method, or any aditional way for using script (1) with correct user rights?
Thanks a lot
Nuran
While doing maintenance on a development server we detached a database and later reattached it to the same server.
For some unknown reason web services execute but return no results. There are no errors reported in event viewer. I connected to the database in Visual Studio 2005 and attempted to execute a simple select statement but it returns no row.
It appears to execute the stored procedure but does not return any data. I have cut and pasted the select statement into a View which does execute.
Lastly I just created a new store procedure to test the database and it also does not return any rows. This is all very strange.
Could use a few suggestions at this point.
Larry