Granting Edit Permission On Stored Procedures
Jan 21, 2005
I have a user on my database that has the following base permissions :
public
db_datareader
I need to give this user permission to edit a single stored procedure. I have tried using the following command :
GRANT ALL ON stored_procedure_name TO username
Which executes successfully, but the user still cannot edit the stored procedure.
If I give the user db_ddladmin permission they can edit all the user stored procedures, but for security reasons I would prefer to be able to this this at procedure level rather than a global permission on all user procs.
Does anybody know how I can do this?
EDIT : This is on SQL 2000
View 1 Replies
ADVERTISEMENT
May 25, 2007
Is there a better way of granting permission to a stored proc for a selected user other than (enterprise manager) select sp then accessing propertys then permissions, then user?
View 4 Replies
View Related
Sep 19, 2007
SQL Server 2005 anomoly?
In SQL Server Management Studio I granted specific permissions to user "A" to do Select, Insert, Update, Delete on Table "B" -
When I logged on as User "A" and attempted the Insert imto table "B" I got the following error:
"Insert Permission Denied on Table B, Database C, Schema dbo"
Is this a problem with the dbo schema?
Then I went back and created a stored proccedure "D" with the exact same Insert statement inside the procedure. I granted User "A" execute permission on the stored procedure "D".
I then logged on as User A and executed Stored Procedure "D". No Problem - stored procedure executed fine with the Insert.
I attempted the Insert statement again - straight SQL - as User "A" and got the same error as above ("Insert Permission Denied.....")
Strange behavior - cannot do a SQL. Insert even though user has permissions but can execute a store procedure with the same Insert statement.
What gives?
View 2 Replies
View Related
Apr 27, 2007
I have two questions:
1) Is it possible to rename a SQL table –or- copy the content of a table into a new table ?
2) How to replace the renamed or replaced table name inside the code of Stored Procedures that references it ?
View 2 Replies
View Related
Jul 24, 1998
Just curious; since the new GUI doesn`t show you the master databse, how can I use it to edit stored procedures that I want to reside in master?
View 1 Replies
View Related
Feb 22, 2005
I'm looking for a way to track usage of SPs. I haven't found any TSQL tool that is already tracking that information. Long ago another DBA added an exec to the end of some SPs to log the usage to a table.
If there is a function already tracking this I'd like to know.
If not, I want to tack an exec line to the end of all the SPs but I don't want to do it by adding it 1600 times manually. Is there any trick in the scripting process I can do to make this easier?
JB
View 2 Replies
View Related
Apr 27, 2007
I have two questions:
1) Is it possible to rename a SQL table –or- copy the content of a table into a new table ?
2) How to replace the renamed or replaced table name inside the code of ALL Stored Procedures that references it ?
View 3 Replies
View Related
Aug 17, 2006
Have sql 2005 express installed.
Running a database for a DotNet Nuke site
Opened Microsoft SQL Server Management Studio Express.
Navigated to CASPORTALDatabasesDotNetNukeProgrammabilityStored Proceduresdbo.AddUser
Right clicked dbo.AddUser and selected "modify"
This allowed me to paste the additional code into the right hand window/pane, however when I try and save this it wants to save it a seperate file /query. Is there something I don't understand?
am I not able to edit the original stored procedure?
View 1 Replies
View Related
Oct 8, 2006
Hi
I need to grant SELECT permission for ALL TABLES for tables which are tag to role (eg. ABC).
How do i grant them "permanently"? as the tables o/p by role (ABC) will be dropped and recreated when another users rerun the tables?
Appreciate any help.
Thanks very much!!
View 4 Replies
View Related
Mar 8, 2012
I have an application, the front end is Access and the back end is sql server 2008 express.
The application does not display the view on a user's machine. Do I have to grant permission for the user, in order for the VIEW to display?
I have tried granting permission, but I am doing something wrong.
View 3 Replies
View Related
Feb 12, 2008
Within my Report Manager, I have several folders, and I would like these folders to be only visible to certain users and groups. In the Active Directory I have DOMAINGroupName1. If I add this group to the folder I want them to have access to with a Browser role, when they open Report Manager they do not see the folder at all. Then I added the same group to the HOME folder also, but again they could not see their folder when they opened the Report Manager. If I add the Users of this group individually (DOMAINUser1, DOMAINUser2 etc) then they can see the folder and run reports just fine. This is not an ideal solution as groups will change over time and I don't want to have to keep adding and deleting members. Anyone got any ideas why they can't see their folder?
Thanks!
View 4 Replies
View Related
Jul 20, 2005
What is the best way to grant a user permission to create a view?I first created a role using enterprise manager but for the role Icreated it doesn't seem to offer that permission. It offers the basicstuff such as insert, select, and update.I could go in and use a grant create view sql statement I suppose butI'd rather do it through enterprise manager where it would be visibleif I need to change it in the future.-David
View 3 Replies
View Related
Jan 14, 2005
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!
View 5 Replies
View Related
Jun 14, 2006
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
View 1 Replies
View Related
May 8, 2015
finding all users that have permission to modify stored procedures in SQL SERVER.
View 0 Replies
View Related
May 21, 2007
On our production SQL 2005 servers I want to give developers readonly access to each user database and also give them the ability to see stored procedures. Readonly is handled through db_datareader, but how do I give them the ability to see stored procedures without granting permission to execute them?
Thanks, Dave
View 4 Replies
View Related
Jul 13, 2007
I want to grant access on the below view for an end user so that he connect to our SQL server and retrieve data. The view looks like the below
CREATE VIEW DB1.[dbo].[View1]
AS
-- For brevity, I made it as simple statement.
SELECT *
From DB2.dbo.table2
GO
For the above view, it looks like I have to grant select and connect permission for the DB1. [dbo].[View1] as well as DB2.dbo.table2.
1. Is my understanding correct?
2. I want the user to access only DB1. [dbo].[View1] and not the underlying tables. Is there a way to grant access only on the view and execute the statement on a different security context so that the user can€™t access DB2.dbo.table2 directly?
3. When the user uses SQL Server Management Studio to connect to SQL server, he is able to connect and select DB2.dbo.table2 directly. Is there any way to restrict user from viewing and executing select statement on DB2 database from SQL Server Management Studio
Thanks in advance for your help
With regards
Ganesh
View 5 Replies
View Related
Jul 26, 2007
I have user XY in SQL 05. I would like to find all stored procedures, where user XY has permission for executing. Is there any way to find it than look in every stored procedure?
Thanks for tips
View 4 Replies
View Related
Aug 7, 2007
Hello All,I tried to set the access permissions for debugging stored procedure by reading the articlehttp://msdn2.microsoft.com/en-us/library/w1bhybwz(VS.80).aspxandhttp://technet.microsoft.com/en-us/library/ms164014.aspxI have tried to add the role to sysaminas follows1)SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'sp_sdidebug'(to find the sp)Error:--The stored procedure not found2)sp_addsrvrolemember 'Developmentswati.jain', 'sysadmin' though this is executed successfuly . Error is still persisting
Cannot debug stored procedures because the SQL Server database is not setup correctly or user does not have permission to execute master.sp_sdidebug.
View 3 Replies
View Related
May 16, 2000
I need to grant select, insert, update, and delete permission to an object residing in Database "X" from a store procedure in database "Y". I have already tried USE database statement. Any suggestion.
Thanks
Maroof Khan
View 1 Replies
View Related
Sep 5, 2006
I want a database user to be able to alter login, database user and database role from my application. so, i assigned that user to sccurityadmin server role, db_accessadmin and db_securityadmin database roles....By now, the user can add or remove login and database user. However, the user cannot add or remove any database role membership. What am I missing here?? What should I do so that the user can create, and alter database roles in the database??
View 1 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
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
Jul 23, 2005
I want to know the differences between SQL Server 2000 storedprocedures and oracle stored procedures? Do they have differentsyntax? The concept should be the same that the stored proceduresexecute in the database server with better performance?Please advise good references for Oracle stored procedures also.thanks!!
View 11 Replies
View Related
Sep 30, 2006
Hi,
This Might be a really simple thing, however we have just installed SQL server 2005 on a new server, and are having difficulties with the set up of the Store Procedures. Every time we try to modify an existing stored procedure it attempts to save it as an SQL file, unlike in 2000 where it saved it as part of the database itself.
Thank you in advance for any help on this matter
View 1 Replies
View Related
Nov 12, 2007
Hi,
I'm from Argentina. I'm not an expert at all in SQL, I know very little about it. I've read in MSDN Library that in order to edit a stored procedure I must right-click the procedure to modify, and then click Design.
I'm using SQL Server Management Studio (not Express) and I don't see any "design" option. I do see a "modify" option. I clicked there and modified just a number I wanted to. Once modified I clicked on the X button to shut the file hoping the system would ask me if I wanted to save the changes made. When I clicked "yes" the file saved into "my documents > sql management studio > proyects".
I checked the original Store Procedure file inside "Programmability > Stored Procedures" and obviously it wasn't altered. I have now a file called "SQLQuery31" in "my documents > sql management studio > proyects" that seems to have the modification made.
Why do I have it there (inside My Documents) instead of modifying the original one? What must I do to get that stored procedure modified?
Thanks from Argentina !!
View 4 Replies
View Related
Nov 16, 2001
I'm using the ADO command object to call a stored procedure in SQL Server from Access 2000.
Set rst = cmd.execute
I receive the correct data back but I can't edit the recordset or addnew.
Is this possible????
Thanks
View 1 Replies
View Related
Mar 23, 2006
Which tool is used to maintain packages stored in the MSDB. Do you have to export them as file based to edit them?
View 3 Replies
View Related
Aug 21, 2005
I am trying to follow an exercise for debugging a stored procedure in the .net IDE. I have had success in adding a connection to the application, until I get the point of where I should set a BreakPoint in the stored procedure "Ten Most Expensive Products" which belongs to the Northwind database. When I open Sever Explorer -->Data Connections- ->WI0001.NorthWind.dbo-->Stored Procedures and Right-click the procedure, the context menu does not give me an edit item to select to open this SP in the editor. Nor can I double-click on the stored procedure to open it either.
View 1 Replies
View Related
Nov 6, 2007
Using SQL 2005, SP2. All of a sudden, whenever I create any stored procedures in the master database, they get created as system stored procedures. Doesn't matter what I name them, and what they do.
For example, even this simple little guy:
CREATE PROCEDURE BOB
AS
PRINT 'BOB'
GO
Gets created as a system stored procedure.
Any ideas what would cause that and/or how to fix it?
Thanks,
Jason
View 16 Replies
View Related
Mar 21, 2007
one of the developer has a stored procedure that selects a data from one table and update to another table.
i moved that stored procedure from development server to production server.
i gave that developer grant execute permission for that stored procedure.
since that stored procedure selects and do update the tables, do i need to give update permission to that developer to that underlying tables also.
thanks
View 1 Replies
View Related
Apr 24, 2001
Hi,
I have a schedule task which call one of my stored procedure,
In this stored procedure, I need to change db owner of one of
database, but I find sp_changedbowner do not allow me to specify
db name,it only change current db,so I have to open a db before
call sp_changedbowner,but it is invalid..
CREATE PROC demo
as
begin
...
use demo_db //it is invalid
exec sp_changedbowner 'scott'
end
Can anyone give me ideas?
Thanks
View 1 Replies
View Related
