How To Apply Permissions Automatically To A Stored Procedure
Dec 12, 2007
I am using SQL 2005, and I am wondering if there is a way to set up a role such that when a new stored procedure is created, the role will automatically be given "execute" permission on that stored procedure.
We have our own dedicated server, but I also administer a database on a shared server (on DiscountASP.net), and they have it set up that way -- when a new stored procedure is created, the user set up by DiscountASP automatically has EXECUTE permission on that procedure.
I keep trying to duplicate this on our dedicated server, but to no avail. I would like to make it so a specific role always has execute permission on every new stored procedure, without having to explicitly add permissions to the role using the properties of the stored procedure.
Is this possible? It seems like it should be, but I can't figure out how. (I'm primarily a programmer, not a DBA, so apologies if this is a dumb question.)
View 3 Replies
ADVERTISEMENT
Jul 23, 2005
Hi All,I'm wondering if anyone can point me at some online resources thatdemonstrate how to run a stored procedure against an SQL Server 2000server at 6am each day?Any help much appreciated!Much warmth,planetthoughtful
View 2 Replies
View Related
Dec 13, 2006
I don't know if this is a newbie question or not, I want SQL Server to automatically execute a stored procedure at the first of each month, how can I do this ?
Regards,
AJ
Be nice to nerds. Chances are you'll end up working for one. (Bill Gates)
View 1 Replies
View Related
Nov 8, 2006
Hi,
I want to run a stored procedure automatically at regular intervals.The time duration is given by me as input details.for example i will
give the date and i should send email to some recipients just one day before the given date.I want this to be done in sql stored procedures.
Can anyone help?
Thanks in advance,
Nithya
View 7 Replies
View Related
Aug 20, 1999
Is anyone else having trouble with the Apply button under the permissions tab?
I can change permissions, click apply, the changes are shown, but when I access the database, the changes arn't made. If I close the permissions windows and re open the changes arn't made.
Will SP1 fix this?(it's not listed in the fix text file)
View 2 Replies
View Related
May 12, 2007
Hi frdz, I m creating my web-application in asp.net with C# 2005 and using sql server 2005. I have created the stored procedure for the insert,update. I want to know how to write the mathematical calculations in the stored procedure.. Pls tell me from the below stored procedure were i m making the mistake ?? As the discount and the total amount are not calculated by itself....and stored in the database How to convert the @discpercent numeric(5,2) to@discpercent ="NoDiscount" should be displayed when no discount is being given to the customers.... ALTER PROCEDURE CalculationStoredProcedure
@accountid int output,
@accountname varchar(20),
@opbal numeric(10, 2),
@opbalcode char(2),
@total numeric(10, 2),
@clbal numeric(10, 2),
@clbalcode char(2),
@discpercent numeric(5,2),
@discamt numeric(10, 2)
as
begin
set nocount on
if @opbal IS NULL OR @opbal = 0
begin
select @opbal=0
select @opbalcode= ' '
select @clbal= 0
select @total= 0
select @clbalcode= ' '
@discpercent ="NoDiscount"
@discamt=0
end
select @accountid = isnull(max(accountid),0) + 1 from accountmaster
select @total=@opbal - @clbal from accountmaster
select @discamt=@total* @discpercent/100 from accountmaster
begin
insert into accountmaster
(
accountname,opbal,opbalcode,clbal,clbalcode
)
values
(
@accountname,@opbal,@opbalcode,@clbal,@clbalcode
)
end
set nocount off
end
Thanxs in adv...
View 7 Replies
View Related
Jan 6, 2006
Hi,
This is my problem :
I have listBox1, listBox2 controls bound to sqldatasource1 and sqldatasource2.
I wrote one stored procedure to retrieve data from my sql database like :
select Id, Name,Address,Amount from KalakaDB
Now I want my listBox1 to display Name By name and my listBox2 to display Amount by decrease order
how can i connect my sqldatacontrol to the stored procedure ?
Thanks
View 1 Replies
View Related
Jul 11, 2006
Hi guys,
I'd appreciate your thoughts on this. Not done too much DB admin. Usually I do development work and the admins to the admin.
The database is behind an API of stored procedures to manipulate the data, and views to select the data.
The database needs to be accessed remotely by multiple clients.
How best to keep the database secure?
Create a new user and login on the database which is made known to all client applications. Then grant execute permission on the stored procs and grant select on the views?
There is probably a better way than one login for all? Should I be looking at roles and groups etc? If so, how best to set that up?
A few pointers would be gratefully received!
View 5 Replies
View Related
May 27, 2007
I have one stored procedure for insert, update and delete operations, I need automatically logged the insert,update and delete operations.
How can I set auto logged mechanism and how can I access the logs in c#?
Thanks
View 1 Replies
View Related
Oct 6, 1999
I have revoked an update stored procedure permission to a SQL user. The first time the user tries to execute the stored procedure he gets the error "Execute permission denied." But if he attempts it a second time the stored procedure will execute with success. I want to deny the user EXEC on the stored procedure forever. Does anybody have any advice to make this happen with success? Thanks.
View 1 Replies
View Related
Jun 7, 2002
I have created a Stored Procedure that will not insert into a particular table.
Yet, when I run the same code in Query Analyzer it runs as it should and completes the Insert.
I have tried to both recreate the SP and searched for authorization issues, with no luck.
Any suggestions?
View 1 Replies
View Related
Jun 12, 2008
What role does a user have to be in to execute a stored procedure?
I am trying to run an exec statement from a web app and am getting "The EXECUTE permission was denied".
Thanks.
View 3 Replies
View Related
May 4, 2006
Help!
I usually use SQL 2000 at work but upon deciding to work from home have installed and setup SQL express 2005. I use the management studio to write table and sps but for new sp I cannot find how to allow permission. I have been able to allow permission for all sps on the database I restored that I am now working on and have successfully allocated permissions to a new table but cannot do the same with the sp. The sp is viewable in the database but permission is denied when attempting to execute via my ASP script.
Any ideas??
Error Type:
Microsoft SQL Native Client (0x80040E09)
EXECUTE permission denied on object 'procBannerSlotList', database 'HotLizardWebsite', schema 'dbo'.
View 1 Replies
View Related
Aug 1, 2006
Okay, I have sort of a peculiar permissions question I am wondering if someone can help me with. Basically, here's the scenario...
I have a CLR stored procedure which does some dynamic SQL building based on values sent in via XML. It's a CLR stored procedure using XML because I want to build a parameterized statement (to guard against SQL Injection) based on a flexible number of parameters which are basically passed in the XML.
The dynamic SQL ends up reading from a table I'll call TableX and I actually discovered an (understandable) quirk with security.
Basically, the connection context is using security for a low-privilaged Windows account ("UserX") and UserX has no permission to the table referenced in the dynamic SQL but because of the dyanmic nature of the query, the stored procedure ends up adopting the security context of UserX. Naturally, this throws a security exception saying UserX has no SELECT permission on TableX.
Now, I can give UserX read permission to the table in question to get things running, but one of the points of using stored procedures is to defer security to the procedure level vs. configuration for tables or columns.
So in striving toward my ideal of security at the procedure level, my question is what is the best way to allow minimum privilege in this case?
I thought about having the internals of the CLR stored procedure run under a different (low-privalaged) security context, but I am wondering if there's an alternate configuration that may be as secure, but simpler.
PS - Please don't let this degenerate into a conversation about OR mappers. I know that happens a lot on these forums.
View 3 Replies
View Related
Mar 14, 2007
I am using SQL 2000 with the Server Enterprise and the Query Analyzer programs. Almost everytime I create a new Stored Procedure, I forget to go into Server Enterprise and grant Execute permissions to my users.
Is there any way in a Stored Procedure to set the permissions when the Procedure is created?
View 4 Replies
View Related
Jan 16, 2002
Hi,
I am testing a method for users to only have the ability to execute only stored procedures that return data and not be able to execute procs that modify data. For testing purposes I have created a 'select' procedure and an 'insert' procedure. I created a user with db_datareader and execute permissions on the two procs and I was still able to execute the 'insert' proc as this user. I also attempted to deny insert permissions on the referenced table to no avail.
Short of establishing a role and granting execute permission to the appropriate procedures, is there a simpler way to do this?
Thank You
Michael
View 1 Replies
View Related
Apr 30, 2008
A simple one (not for me)
MS SQL Server 2005
Which permissions do I need to have (as a User) to create a Stored Procedure
Which other entity's properties do I need to change in order to create a Stored Procedure
Thanks
View 2 Replies
View Related
Nov 29, 2007
Hi All,
For listing login details we have sp_helplogins 'LOGIN NAME'
In the same way i want ti know for particular procedure.
can any one help in this.
Thanks in advance.
malathi
View 4 Replies
View Related
Jul 23, 2005
I have written an stored proc that reads from a text file and executesthe script as dynamic sql.If the text file contains malicious code,I want to be able to detect itand prevent the stored procedure from executing.I've tried revoking delete,insert,update rights all tables in thedatabase to the user .I then granted execute rights to the stored procedure for the sameuser. But the user is still able to delete a record from the table byexecuting the stored procedure.Is there any means to I revoke,insert,delete ,update rights to a storedproc?
View 2 Replies
View Related
Jul 20, 2005
Is there any way to allow a user to use the xp_cmdshell extendedstored procedure without giving that user execute permissions toxp_cmdshell in SQL server 6.5? Let me clarify. Lets say I (as thedbo) create a stored procedure called sp_send_err:CREATE PROCEDURE sp_send_err @CompID varchar(20) ASdeclare @strCMD varchar(255)select @strCMD = "master.dbo.xp_cmdshell 'net send " + @CompID + """ERROR!""', no_output"execute (@strCMD)GONow lest say I give "user1" execute permissions on sp_send_err, but nopermissions on xp_cmdshell. When I run sp_send_error I get thefollowing error:"EXECUTE permission denied on object xp_cmdshell, database master,owner dbo".Why doesn't this work? What else can I do?
View 1 Replies
View Related
Jul 20, 2005
We are running SQL Server 2000 Developer Edition. I don't want tomake the developers the sysadmin or even the dbo in the userdatabases. Is there a way to give them access to only view thepermissions for the stored procedures in the user database withoutmaking them dbo?When I take them out of the db_owner role, when they open a storedprocedure they no longer see the permissions tab. I would like forthem to see the permissions tab and be able to view the permissionsbut not change the permissions.Is that doable?
View 1 Replies
View Related
Nov 27, 2006
hello all....can anyone tell me how can i manage the stored procedure permissions in SQL Server Express. Untill now i have developed in SQL Server 2000 and there i used SQL Enterprise Manager for this problem..in SQL Express i can't handle it...Manny thanks.....
View 3 Replies
View Related
Nov 21, 2005
Bit 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
View 1 Replies
View Related
Aug 27, 2002
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
View 1 Replies
View Related
Jan 15, 2015
I am logging into a SQL instance to run the following query:
DECLARE @ReturnCode int EXECUTE @ReturnCode = [master].dbo.xp_create_subdir N'sharemasterFULL' IF @ReturnCode <> 0 RAISERROR('Error creating directory.', 16, 1)
The share in which the folder is to be created has my account added with full permissions to create files. However this command fails unless I add the SQL Service account user with rights to the folder also.
Is this expected behaviour, is this something specific to extended stored procedures?
View 0 Replies
View Related
May 12, 2008
I'm getting a strange error and I've run out of places to look to fix it. I'm running the following statement when connected as APP_USER in SQL Server Managment Studio (SSMS).
select * from cs.dbo.order
I get the following error.
Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object 'ORDER', database 'CS', schema 'dbo'.
Even after running the following as SA, the result is the same
grant select on cs.dbo.order to APP_USER
Running the query as SA or as a user with datareader permissions works fine. The APP_USER can also select from another table in the same database and schema without error. The APP_USER has SELECT, INSERT, and UDPATE permissions on the table. A second user with only SELECT gets the same error.
I've tried removing and reapplying the permissions with no luck.
Searching for this problem all the examples I find are related to stored procedures (permissions not working the same on dynamic SQL). However, I'm not using a stored procedure. This is plain SQL in a query window in SSMS.
Any help on where else to look for the cause of this would be greatly appreciated.
View 3 Replies
View Related
Apr 24, 2008
While trying to execute a stored procedure I am getting error that 'SELECT permission denied on table .......' The DBA has given execute permission for the sp and still the same error. What needs to be done. When permissions are given through the SP it implies that the objects are given permissions ?
Putting in db_datareader group will give permission to read from all tables across all the databases in the server. We want that the user should be able to read data from only those tables called in the sp. Normally in SQL 2000 we used to give EXECUTE permissions to the sp only. This in turn would be enough for that user to get data while executing the sp.
View 5 Replies
View Related
Aug 21, 2006
I'm having a problem replicating stored procedure permssions accross two sql server 2005 servers, using transactional replication. When I replicate tables, it's easy enough to send the permissions over from the subscriber since there is an option under the articles properties for "Copy permissions." I can't seem to find any such option for SPs, UDFs or views. Am I missing something?
So the other idea I had was to create a script to grant the proper permissions, and run it using sp_addexecscript to my publication. Currently I have two one-way transactional publications. One publication for all my tables, and another for SPs, UDFs and views. I can run sp_addexecscript on the publication containing all my tables with out a problem, but when I run it against the publication containing my SPs, UDFs and views I get the following error:
Msg 21332, Level 16, State 1, Procedure sp_MSrepl_addscriptexec, Line 57
Failed to retrieve information about the publication : SP_UDF_Views_transactional. Check the name again.
I'm not sure why I'm getting this message. If anyone can help me out, I'd greatly appreciate it.
View 11 Replies
View Related
Feb 9, 2007
My Production servers are SQL Server 2005 x64. I would like to allow my developers the ability to look at permissions on production stored procedures but not be able to change those permissions or alter the production code. What has to be set to allow this sort of security.
View 8 Replies
View Related
Feb 24, 2005
I need to come up with a script that when executed it will create a stored procedure and trigger along with permissions. Is there a way to make this into a package. Any ideas?
View 3 Replies
View Related
May 6, 2014
Only to a specific schema? Can this be done?
View 5 Replies
View Related
Apr 28, 2015
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.
View 8 Replies
View Related
Mar 6, 2013
The developers in our shop have a need to explicitly grant view definition permissions to themselves on stored procedures they create in their development databases. They have dbo level permissions in these databases and although they can explicitly grant view definition permissions to other developers in the same database, they are unable to do so for themselves. When they attempt this, it appears that they are successful but when they check the stored procedure afterwards the permission is not there for themselves.
While this does not cause an issue in development, the intention is for these view definition permissions to be carried forward to the test and production databases where they only have datareader permissions.
When these stored procedures are scripted out by the dba to move to Test and Production the view definition permissions are not scripted out for the developer in question.
Is there a way that a developer with dbo rights in a database can explicitly grant themselves view definition permissions on a stored procedure they create as dbo?
View 9 Replies
View Related
