SQL 2012 :: Assign Create Stored Procedure Permissions?
May 6, 2014Only to a specific schema? Can this be done?
View 5 Replies
ADVERTISEMENT
Permissions To Create A Stored Procedure
Apr 30, 2008A 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
Stored Procedure Create & Execute Permissions
Aug 27, 2002In 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
SQL 2012 :: Extended Stored Procedure Permissions
Jan 15, 2015I 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?
SQL 2012 :: Granting Permissions To Execute Stored Procedure
Apr 28, 2015I 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.
SQL 2012 :: Create Stored Procedure In SSIS Package
Jun 5, 2014I have a really big stored proc that needs to be rolled out to various databases as part of db installs I run through SSIS.
The Stored proc is too long to run using Execute SQL Task. Is there another way that just running the create script manually.
SQL Server 2012 :: How To Create Password On Stored Procedure
Jun 27, 2014is it possible to create PW on Stored Procedure? No one can execute or Alter any Store Procedure with Password?
View 1 Replies View RelatedSQL Server 2012 :: Using CTAS Create Stored Procedure For Client
May 14, 2014I have table named TEMPLATE_ACTIVITY. This is template table I have 27 this kind of tables.
I want to create stored procedure to change name MICHELIN_US_ instead of TEMPLATE_ all remaining name should be same. For that I am using 'Create Table As Select' to keep same structure as Template tables.
I want to create sp as like execute this way Exec @MICHELIN_US_
So that in future if Client change to MICHELIN_US_ to UNITED_ I can just change Exec @UNITED_
And it will change all table names to UNITED_ACTIVITY
I want to create this SP for different client.
SQL Server 2012 :: Mirror Database - Create Stored Procedure
Nov 5, 2014I have a database which uses "Database Mirroring", and I need to write stored procedure and pull data from "Principal Server".
My Current Logic:
CREATE PROCEDURE abc123
as
BEGIN
IF Server01 = 'ONLINE'
BEGIN
[Code] .....
The problem I am facing is: Stored procedure is not created because "One of the server is not Online"...
How Can I Assign A Stored Procedure As Cursor's Data Source In AStored Procedure?
Oct 8, 2007How can I create a Cursor into a Stored Procedure, with another Stored Procedure as data source?
Something like this:
CREATE PROCEDURE TestHardDisk
AS
BEGIN
DECLARE CURSOR HardDisk_Cursor
FOR Exec xp_FixedDrives
-- The cursor needs a SELECT Statement and no accepts an Stored Procedure as Data Source
OPEN CURSOR HardDisk_Cursor
FETCH NEXT FROM HardDisk_Cursor
INTO @Drive, @Space
WHILE @@FETCH_STATUS = 0
BEGIN
...
END
END
SQL 2012 :: Allow Low Privilege User To Execute Stored Procedure To Create DB Users
Jul 22, 2014I have a requirement to allow a user to restore a database and then create database users and add them to the db_owner database role. The user must not have sysadmin rights on the server.
The database restore works ok by placing the user in the dbcreator role.
There is a stored procedure to create the database user and alter role membership, I want the user to execute the sp as a different, higher privilege account so as not to give the user underlying permission to create users in the database.
USE [master]
GO
/****** Object: StoredProcedure [dbo].[sp_create_db_users] Script Date: 22/07/2014 13:54:46 ******/
SET ANSI_NULLS ON
GO
[Code] ....
The user has execute permission on the stored procedure but keeps getting the error:
Msg 916, Level 14, State 1, Line 2
The server principal "Mydomainadmin1" is not able to access the database "Mydatabase" under the current security context.
Mydomainadmin1 has dbowner to Mydatabase and sysadmin rights for server. If the 'execute as' is changed to 'caller' and run by mydomainadmin1 it works so the issue is between the execute sp and the actual running of the procedure.
SQL Server 2012 :: Create XML File From AS400 Stored Procedure Returning Multiple Datasets
Oct 3, 2014I have a store procedure in MC400 which I can call from SSMS using the below command:
EXEC ('CALL GETENROLLMENT() ')At serverName
Now this command returns two data sets like:
HA HB HC HD HE
1112
112571ABC14
113574ABC16
114577ABC87
DADBDCDD
1115566VG02
1115566VG02
1115566VG02
I want to generate two different XML files from these two datasets.Is there any way this can be achieved in SSIS or t-sql ?
SQL 2012 :: Create Cube Permissions?
Nov 19, 2014I need to grant create cubes to developers, but if I include them into server administration role, then they could delete or modify all the databases.
Each developer require full administration on their own databases, but not other developer's databases.
Please Help To Assign Multiple Results Into Individual Variables, Stored Procedure
Apr 12, 2008I have a MSSQL2000 table called partspec.dbo.locationIn this table are 2 smallint columns: Tester (numbered 1-40) and Line (numbered with various integers)I am trying to create a stored procedure to read the tester number like so:Select Tester from partspec.dbo.location where Line = 2which will return up to 5 tester number results, lets say 11, 12, 24, 29 ,34My question is how do I store these tester numbers into 5 variables so that I may use them later in the sp ? So it would go something like this:CREATE PROCEDURE Table_Line
(@Tester1 integer,@Tester2 integer,@Tester3 integer,@Tester4 integer,@Tester5 integer)ASSELECT Tester FROM partspec.dbo.location where Line = 2Now this is where I'm confused on how to get 1 value into 1 variable and so on for all 5 values returned. This is what I would like to happen:
@Tester1 = 11@Tester2 = 12@Tester3 = 24@Tester4 = 29@Tester5 = 34GOThank you for any and all assistance.
How To Assign String Value To TEXT Output Parameter Of A Stored Procedure?
Jul 23, 2005Hello,I am currently trying to assign some string to a TEXT output parameterof a stored procedure.The basic structure of the stored procedure looks like this:-- 8< --CREATE PROCEDURE owner.StoredProc(@blob_data image,@clob_data text OUTPUT)ASINSERT INTO Table (blob_data, clob_data) VALUES (@blob_data, @clob_data);GO-- 8< --My previous attempts include using the convert function to convert astring into a TEXT data type:SET @clob_data = CONVERT(text, 'This is a test');Unfortunately, this leads to the following error: "Error 409: Theassignment operator operation cannot take a text data type as an argument."Is there any alternative available to make an assignment to a TEXToutput parameter?Regards,Thilo
View 1 Replies View RelatedSQL 2012 :: Revoke Create Schema Permissions Without Removing DDL Permission?
Apr 17, 2015I have a sql server 2012 server and I need to prevent the users from creating new schemas by mistake. Is there any way to revoke that permission alone but still letting the user to create their own objects in dbo (yes I know that shouldn't be in dbo but that is another issue).
View 2 Replies View RelatedIntegration Services :: Assign Variables To Multiple Table Results From Stored Procedure
Sep 21, 2015If I have a stored procedure that returns 15 tables, how do I distinguish the tables to assign variables to each table in c#?
View 6 Replies View RelatedAssign Permissions For Individual Table.
Jan 15, 2008A login named UserLogin1 has db_dataReader and db_dataWriter roles, and three schemas which are db_owner, dbUser1, and dbUser2 in a database named Database1. I wanted to have some tables for read-only, so I right clicked on the table and select properties. In the Permissions tab, I added UserLogin1 and checked Alter, Delete, Insert, Take Ownership, and Update under Deny. It worked for schema dbo only. I did the same steps for the tables that have schema dbUser1 or dbUser2, but UserLogin1 I added didn't stay. Why? How can I make those tables read-only?
Thanks.
DanYeung
How To Assign Permissions Properly- 3rd Parties
Jan 17, 2008I have a question about assigning permissions for SQL Server 05's reporting services.
We have an outside party that needs access to run reports on our reporting services server.
However, I want to make sure they can't see anything else on our database server. (They need logins to connect to the server and then at that point in reporting services I only grant them access to a single folder that has the reports they need in the web interface).
I just want to be sure they can't poke around and see anything else in the server.
What would be the best way to handle that?
Just create their logins and grant them connect / login permissions only?
Let them fall into the PUBLIC role?
Should I create a new role just for them?
SQL Server 2012 :: Permissions For Executing Procedure Which Executes Other Procedures?
May 13, 2015I have a stored procedure which executes about forty other stored procedures in several different databases. All of these other procedures truncate tables and insert new data into those tables selected from still other tables.
I want to run this top-level procedure using an account which can't do anything else.
Is there a simple way to give it all the permissions it needs without empowering it to do anything else?
Stored Procedure Permissions
Oct 6, 1999I 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 RelatedStored Procedure Permissions?
Jun 7, 2002I 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?
Stored Procedure Permissions
Jun 12, 2008What 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.
Permissions On A New Stored Procedure
May 4, 2006Help!
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'.
Permissions With Dynamic SQL Within Stored Procedure
Aug 1, 2006Okay, 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.
Setting Permissions In A Stored Procedure
Mar 14, 2007I 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?
Stored Procedure Permissions Issue
Jan 16, 2002Hi,
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
Stored Procedure Permissions List
Nov 29, 2007Hi 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
Revoke Permissions TO Stored Procedure
Jul 23, 2005I 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 RelatedStored Procedure Permissions With Xp_cmdshell On SQL 6.5
Jul 20, 2005Is 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 RelatedView Stored Procedure Permissions
Jul 20, 2005We 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 RelatedManage The Stored Procedure Permissions....
Nov 27, 2006hello 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 RelatedHow Can I Assign Data Returned From A Stored Procedure Into The Return Table Of A Table Valued Function
Apr 18, 2007Here is the scenario,
I have 2 stored procedures, SP1 and SP2
SP1 has the following code:
declare @tmp as varchar(300)
set @tmp = 'SELECT * FROM
OPENROWSET ( ''SQLOLEDB'', ''SERVER=.;Trusted_Connection=yes'',
''SET FMTONLY OFF EXEC ' + db_name() + '..StoredProcedure'' )'
EXEC (@tmp)
SP2 has the following code:
SELECT *
FROM SP1 (which won't work because SP1 is a stored procedure. A view, a table valued function, or a temporary table must be used for this)
Views - can't use a view because they don't allow dynamic sql and the db_name() in the OPENROWSET function must be used.
Temp Tables - can't use these because it would cause a large hit on system performance due to the frequency SP2 and others like it will be used.
Functions - My last resort is to use a table valued function as shown:
FUNCTION MyFunction
( )
RETURNS @retTable
(
@Field1 int,
@Field2 varchar(50)
)
AS
BEGIN
-- the problem here is that I need to call SP1 and assign it's resulting data into the
-- @retTable variable
-- this statement is incorrect, but it's meaning is my goal
INSERT @retTableSELECT *FROM SP1
RETURN
END