SQL Server 2008 :: EXECUTE SP Permissions For Child Database
Feb 19, 2015
I have a database which contains a stored procedure. The stored procedure contains an update script to a different database. The user group have access to the database that stores the stored procedure but do not have update rights to the database that contains the data that is being updated.
Therefore, every time the user group executes the stored procedure it fails because the security permissions do not propagate to the next database.
Is there anything I can do to get around this without grant dbo permissions to the whole user group.
View 2 Replies
ADVERTISEMENT
Aug 4, 2015
I found this script online..
SELECT prin.[name] [User], sec.state_desc + ' ' + sec.permission_name [Permission]
FROM [sys].[database_permissions] sec
JOIN [sys].[database_principals] prin
ON sec.[grantee_principal_id] = prin.[principal_id]
WHERE sec.class = 0
ORDER BY [User], [Permission];
but the results are this: 2 columns - User and Permission
User Permission
User1 GRANT CONNECT
User2 GRANT CONNECT
IS there a way in SQL Server (2005/2008/2012) to run a script against a Database that will show all users that have permissions to that Database and the type of permissions?
View 7 Replies
View Related
Jul 29, 2015
our clients have the flexibility to detach and attach databases (I know there are a lot of considerations around this but there is now way of changing it), once they attach a database we need to run some code to update a bunch of values in the database.
Other than creating a SQL Agent job are there any other options available to automatically execute a script once the database has been attached?
View 1 Replies
View Related
Jul 17, 2015
I've been struggling with this issue,
1) Test--FolderName (This Test folder name should use as a database name for below sub folders)
a)Create--Sub Foldername
i)create.sql
b)Alter---Sub FolderName
i)Alter.sql
c)Insert---Sub FolderName
i)Insert.sql
[Code] .....
The scripts need to be run in order. So script one needs to run first folder in that sub folders after that next second folders etc..
Is there a way to create a bat file that automatically runs all these scripts, in order against, the databases they need to?
The databases that they need to run against have the name of the database at the beginning of the name of the folder.
View 0 Replies
View Related
May 31, 2007
Hi All,
I'm just upgrading my business database to SQL 2005 and hit a problem when executing a stored procedure in the msdb database. Error message reading "EXECUTE permission denied on object 'sp_delete_job'".
Obviously this message tells me that I do not have permissions to execute stored procedures in the MSDB database.
In SQL 2000 I never had to mess around with permissions on this database as the security was already pre-configured.
I'm still familiarising myself with SQL 2005 so rather than making a finger in the air guess I'd like to understand how SQL 2000 was configured and the best solution for resolving this in SQL2005.
I'm really after some advice/suggestions!
View 5 Replies
View Related
Aug 4, 2015
DECLARE @ParentTable TABLE (ProductID BIGINT,ItemID BIGINT)
DECLARE @ChildParentTable TABLE (ParentID BIGINT,ProductID BIGINT,ItemID BIGINT)
--In Parent table(@ParentTable) there are 4 and 5 items each for product 101 and 102 respectively
INSERT INTO @ParentTable(ProductID,ItemID) VALUES(101,1234),(101,1578),(101,1590),(101,1237)
INSERT INTO @ParentTable(ProductID,ItemID) VALUES(102,5465),(102,5466),(102,5474),(102,5489),(102,6543)
--child products 701 and 901 are derived from parent products(partially) 101 and 102 respectively
INSERT INTO @ChildParentTable(ParentID,ProductID,ItemID) VALUES(101,701,1234),(101,701,1590),(101,701,1578)
INSERT INTO @ChildParentTable(ParentID,ProductID,ItemID) VALUES(901,102,5465),(901,102,5474),(901,102,8976)
--Here what I need is
--For product 701 there is one item missing ie 1237 which exists in its parent 101
--and For product 901 there are 3 items missing ie 5466,5489 and 6543 which exists in its parent 102
--and extra item exists ie 8976 so my result table should lokk like this
/*
ParentProductID ProductIDItemIDIsExtra IsMissing
1017011237 01
1029015466 01
1029015489 01
1029016543 01
1029018976 10
*/
View 1 Replies
View Related
Apr 8, 2015
i was trying to use the XML read functionality using t-SQL for XML attached.The column is coming with the token names and token-values in XML format and we are using the XML nodes() functionality to read the token names and token value.I am able to read only the parent token names and its values(using the sql attached) and could not be able to get the child token names and its values.how can i acheive the tokenNames with its values with the SQL query.i am attaching both SQL script which i am using and the XML entity.
View 9 Replies
View Related
Nov 29, 2006
Ive created a DAL called Artist.xsd. Ive used stored procedures to access the data. The wizard created a stored procedure called 'dbo.ArtistSelectCommand' Ive granted the ASPNET account execute permissions on this stored procedure When I run the application and try to execute the stored proc, I get this error
EXECUTE permission denied on object 'ArtistSelectCommand', database 'EBSNet', owner 'dbo'.
as far as im aware ive givne the ASPNET account the correct permissions
EXEC sp_grantlogin [MachineNameASPNET]
EXEC sp_grantdbaccess [MachineNameASPNET], [ASPNET]
** Ive also done this by selecting the stored proc in sql server 2005 and setting permissions by right clicking and selecting properties.
Is there anything else I need to do ?
View 1 Replies
View Related
Mar 24, 2015
I have existing domain users with SQL logins and having different set of permissions, we are migrating to new domain, and we have new user ID's with new domain. Is there any way through query I can create an user and copy the permissions.
View 0 Replies
View Related
May 4, 2015
Is there anyway to find the permissions assigned to user. What level of access the user has in each db and the tables & is he able to see jobs. Each object and permissions
View 6 Replies
View Related
Nov 3, 2010
We use Netbackup for our SQL servers to backup and restore databases. I would like the service account used by Netbackup to have as limited permissions as possible. The account should be able to backup and restore a db without being able to read any of the content. Right now the account jobs fail if the service account is not in the sysadmin role.
I removed the account from sysadmin and limited it to dbcreator and public but the job fail.
How to setup an account so that people who know the service account password can't log in with that account and read db information?
View 9 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
Apr 8, 2015
I have separate scripts for Creation of Tables, Stored Procedures, Views, Triggers in different .sql files.
I have to execute thses scripts in batch in different servers and different databases without opening SSMS.
All .sql files to be executed in single command.
View 3 Replies
View Related
Sep 7, 2015
declare @table table (
ParentID INT,
ChildID INT,
Value float
)
INSERT INTO @table
SELECT 1,1,1.2
[code]....
This case ParentID - Child 1 ,1 & 2,2 and 3,3 records are called as parent where as null , 1 is child whoose parent is 1 similarly null,2 records are child whoose parent is 2 , .....
Now my requirement is to display parent records with value ascending and display next child records to the corresponding parent and parent records are sorted ascending
--Final output should be
PatentID ChildID VALUE
33 1.12
null3 56.7
null3 43.6
11 1.2
null1 4.8
null1 4.6
22 1.8
null1 1.4
View 2 Replies
View Related
Oct 7, 2013
I am search for coding criteria I need create a stored procedure with execute as and along with encryption. How can I use the same ? My main motive is to create proc with execute as a user also at the same time I need to encrypt the same from other users seeing the code.
The below query is getting errors:
Create procedure testproc
with execute as 'user' and with encryption
as truncate table some table
View 3 Replies
View Related
Mar 3, 2015
I have built the following query in SSMS, when I add it to an Execute SQL Task in SSIS. I get this error -
"[Execute SQL Task] Error: Executing the query "SELECT @columnz = COALESCE(@columnz + ',[' + times..." failed with the following error:
"Must declare the scalar variable "@columnz".". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly."
Query:
use design
drop table tmpNCPCNCDownstreamMaxUtilization3wks
select node, max(utilization) as max_Utilization, DATE
into tmpNCPCNCDownstreamMaxUtilization3wks
from stage_ncpcncdownstream_temporal
WHERE Date BETWEEN DATEADD(day, -20, GETDATE()) AND GETDATE()
[code]....
View 1 Replies
View Related
Mar 5, 2015
I 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
View 2 Replies
View Related
Jun 19, 2015
I have a periodic backup task, and when I need a development copy on which to test code, I restore my most recent backup to a different name, switch the ODBC link to that name on my development machine and have a current copy of the database to play with.
I've been doing this for years, and it works great. Just now I did it, and suddenly my development machine is unable to run any stored procedures that have the 'Execute as owner' clause in their definition. I'm using domain accounts, my personal account is the owner of the database, and everything works on the production copy, which is in the same instance on the same machine.
The test copy is identical to the production copy, which continues to work fine - it was just created using by restoring the backup of the production copy, but I can't run anything with this clause. As soon as I delete the 'Execute as owner' line, the procedure is suddenly available. If I put it back, I'm locked out again.
The error message is: The server principal “sa” is not able to access the database “WhateverDB” under the current security context
View 5 Replies
View Related
Nov 30, 2006
HI, I have some parent parent packages that calls child packages. When I added a bunch of packages, I faced the buffer out of memory error. I then decided to set the child packages property ExecuteOutOfProcess to TRUE. I noticed that the execution time is longer now. Is this a good practice to set the ExecuteOutOfProcess to true? If so, is it normal that the execution time is longer?
Thank you,
Ccote
View 2 Replies
View Related
May 11, 2015
I have create a batch file to execute a stored proc to import data.
When I run it from the server (Remote Desktop) it works fine, but if I share the folder and try to run it from my pc, it doesn't do anything. I don't get an error, it just doesn't do anything. My windows user has admin rights in SQL. Why is it not executing from my PC?
View 9 Replies
View Related
Feb 13, 2007
Hi,
Let's say I have a package taking as parameter "InvoiceID". I want to execute this package as a child in another package. The parent package gets the list of invoices to produce and calls the child package for each entry of the list.
How do I pass the InvoiceID to the child? I know I can use the parent's variables from the child but I don't want the child package to be dependant on the parent package. For example, I might want to execute the "child" package as a stand-alone in development (providing it with a predefined InvoiceID). I might also want to call the same child package from another parent package with other variable names.
What I would like to do is "push" the value instead of "pulling" it. I know it's possible using the command line and the /SET option (ex.: /SET Package.Variables[InvoiceID].Value;' 184084)... Is it possible using the Execute Package Task?
Thanks
View 4 Replies
View Related
May 27, 2008
I've been executing a package and passing a parent variable to the child using package configurations but I'd now like to do this using a script task. The script task would then programmatically load the package and execute it.
How do I do this and still use the parent variable?
I've found examples of how to load a package but I haven't been able to find out how to I load it specifying the parent variables.
I think it's possible. MSDN shows the available methods but the example is for the base method.
http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.package.execute.aspx
Cheers,
Ben
View 1 Replies
View Related
Jun 20, 2013
Problem Summary: Merge Statement takes several times longer to execute than equivalent Update, Insert and Delete as separate statements. Why?
I have a relatively large table (about 35,000,000 records, approximately 13 GB uncompressed and 4 GB with page compression - including indexes). A MERGE statement pretty consistently takes two or three minutes to perform an update, insert and delete. At one extreme, updating 82 (yes 82) records took 1 minute, 45 seconds. At the other extreme, updating 100,000 records took about five minutes.When I changed the MERGE to the equivalent separate UPDATE, INSERT & DELETE statements (embedded in an explicit transaction) the entire update took only 17 seconds. The query plans for the separate UPDATE, INSERT & DELETE statements look very similar to the query plan for the combined MERGE. However, all the row count estimates for the MERGE statement are way off.
Obviously, I am going to use the separate UPDATE, INSERT & DELETE statements. The actual query plans for the four statements ( combined MERGE and the separate UPDATE, INSERT & DELETE ) are attached. SQL Code to create the source and target tables and the actual queries themselves are below. I've also included the statistics created by my test run. Nothing else was running on the server when I ran the test.
Server Configuration:
SQL Server 2008 R2 SP1, Enterprise Edition
3 x Quad-Core Xeon Processor
Max Degree of Parallelism = 8
148 GB RAM
SQL Code:
Target Table:
USE TPS;
IF OBJECT_ID('dbo.ParticipantResponse') IS NOT NULL
DROP TABLE dbo.ParticipantResponse;
[code]....
View 9 Replies
View Related
Jun 17, 2015
Exec Prcoess task with source : ftp
destination :ftpunzip
work directory ftpunzip
executable : c:Program FilesWinZip
i am using expressing.
It is doing the unzip but getting this error
package process on server server1 has failed within the Task 'Unzip Files' with the following errors:
>
> File/Process "WZUNZIP.EXE" does not exist in directory "c:Program FilesWinZip".
This is the error message i am getting it
View 6 Replies
View Related
Jan 15, 2007
Help i need somebody.
I am trying to loop through & set execute permission on some UDFS but I cannot find an option for UDF's.
Private mobjSQL As SQLDMO.SQLServer
Dim objDB As SQLDMO.Database
Private Sub GrantUDF()
Dim objDB As SQLDMO.Database
Dim objUDFS As UserDefinedFunctions
Dim objUDF As SQLDMO.UserDefinedFunction
Dim lngUDFCount As Long
Dim lngProg As Long
For Each objDB In mobjSQL.Databases
If ListItemChecked(Me.lstDatabases, objDB.Name) Then
lngUDFCount = objDB.????????????????????
lngProg = 0
For Each objUDF In objDB.??????????????????
GeneralProgress objUDF.Name, 0, lngUDFCount, lngProg
objUDF.Grant SQLDMOPriv_Execute, "AGDB"
lngProg = lngProg + 1
Next
GeneralProgress "Finished", 0, lngUDFCount, lngProg
End If
Next
End Sub
View 1 Replies
View Related
Mar 12, 2008
In this example ….. I have a sql user id called 'toronto' with the permissions it acquired by being added to the db_datareader (READ) & db_datawriter (ADD, CHANGE, DELETE) database roles in the 'getranet' database.
However, the 'toronto' account is unable to execute or run any of the 240 stored procedures in the 'getranet' database with only these permissions, at least that's the results I'm getting.
Solution #1: If I add the toronto id to the db_dbowners role (PERFORM ANY ACTIVITY) in the 'getranet' database, the problem is resolved. I would rather not use this approach because with these permissions the id can delete the db, or tables etc… and I'm not the only one with the id and password.
Solution #2: I created a new database role (SP_EXECUTE) in the 'getranet' database, and granted the role 'execute' permissions to all 240 stored procedures in the database, added the 'toronto' id to the SP_EXECUTE role, the problem is resolved.
This solution works fine but it will require more maintenance, anytime a stored procedure is added or modified I will have to remember to update the SP_EXECUTE database role in the getranet database. Hey, I will forget once in a while, apply thumb screws here.
So my question is …. Am I approaching this all wrong? Is there a 3rd, 4th or a better solution (White Elephant) that I just can't see?
I just thought I would ask…..
View 6 Replies
View Related
Jul 19, 2006
HI There
I need a little help.
I have created a stored procedure that executes when a service broker message is received, this sp then exec's sp_send_dbmail.
But i keep getting the error that execute permission is denied on sp_send_dbmail.
Fair enough but i have no idea who to grant the execute permissions to ? The sp is called by the endpoint service of the service broker queue, the sp is owned by dbo who is 'sa', i have tried altering the sp to execute as 'dbo' or as caller, but is still get the same error.
I have tried to exec sp_send_dbmail as 'sa' and it works.
I do not know under what user this sp executes when the service calls the sp, i also cannot get it to work with an execute as dbo statement even though dbo is 'sa' ?
Please help ?
Not sure if this should be posted under service broker becuase this post is more related to permissions ?
Thanx
View 7 Replies
View Related
Jul 22, 2015
Not sure where the culprit is for this error propping up. I've added a new measure group and Im trying to generate the schema for the resulting fact table, but something isn't right. However, I don't think its my new measure group that's causing the issue, because I removed it and still get this issue when I select Database > Generate Relational Schema
View 2 Replies
View Related
Jun 19, 2015
Historically I've always written a VB script to copy a file from a sharepoint library. I don't like this method because I have to input a username & password in the script and maintain a config file.
Yesterday I was playing around with using a file system task. The sharepoint file has a UNC path so why not? I created a simple test package with a single file system task that copies the sharepoint file (addressed via UNC) to another network location. Package runs fine locally.
I try running on our utility server but am getting a "The file name [SHAREPOINT UNC PATH] specified in the connection was not valid" error. Package is running with a proxy on the server and the proxy account has the same permissions to the sharepoint site (so far as I can tell) as me.
View 0 Replies
View Related
May 31, 2000
What permissions/roles/etc. are necessary for a SQL login to see and start jobs.
Thanks, I'm having trouble with sp_start_job (it says job does not exists, that another user can run just fine...)
Dano
View 1 Replies
View Related
Mar 6, 2008
Hi, I want to execute BCP in Query Analyser in SQL Server 2005 Express for that i surf on net and find that i should execute BCP under xp_cmdShell, That works good for addministritative account on SQL. But i want the working will be done by a non administrative account or non 'sa' user.How can i assign a non sa User permissions to execute xp_cmdShell? or just tell me any other alternative way to run BCP in Query Analyser or code behined. thanx
View 1 Replies
View Related
Jul 24, 2007
I have a group of users that I have given db_datareader permissions to in an SQL Server 2000 database. I am also creating web pages on an intranet site that pulls data from the table. If I just use a select statement to pull the data from the table, the users dont have a problem. If I use a stored procedure with the exact same sql statement, the users get an error until I grant them execute permissions on the stored procedure.
I have heard that store procedures is the best way to handle data operations but having to make sure I assign execute permissions every time I create a stored procedures can be a pain. The only way I know of to make sure that they had permissions would be to make them a member of db_Owner which is definitely not an option.
Is this just the way it is, or is there some way to automatically grant them execute permissions on stored procedures that are nothing more than select statements and dont violate db_datareader permissions?
View 4 Replies
View Related
Jun 30, 2006
I'm trying to use the SPROC below (courtesy of Erland!) to capture theerror message but it fails owing to insufficient permissions (I can'treproduce it just now, but I think it's because it can't get access tothe DBCC OUTPUTBUFFER).How do I give the SPROC permission to execute?Many thanksEdwardCREATE PROCEDURE stpShowErrorMessage @errmsg nvarchar(500) OUTPUT ASDECLARE @dbccrow nchar(77),@msglen int,@lenstr nchar(2),@sql nvarchar(2000),@s tinyint-- Catch the output buffer.CREATE TABLE #DBCCOUT (col1 nchar(77) NOT NULL)INSERT INTO #DBCCOUTEXEC ('DBCC OUTPUTBUFFER(@@spid)')-- Set up a cursor over the table. We skip the first-- row, because there is nothing of interest.DECLARE error_cursor CURSOR STATIC FORWARD_ONLY FORSELECT col1FROM #DBCCOUTWHERE left(col1, 8) <> replicate('0', 8)ORDER BY col1-- Init variable, and open cursor.SELECT @errmsg = ''OPEN error_cursorFETCH NEXT FROM error_cursor INTO @dbccrow-- On this first row we find the length.SELECT @lenstr = substring(@dbccrow, 15, 2)-- Convert hexstring to intSELECT @sql = 'SELECT @int = convert(int, 0x00' + @lenstr + ')'EXEC sp_executesql @sql, N'@int int OUTPUT', @msglen OUTPUT-- @s is where the text part of the buffer starts.SELECT @s = 62-- Now assemble rest of string.WHILE @@FETCH_STATUS = 0 AND datalength(@errmsg) - 1 < 2 * @msglenBEGINSELECT @errmsg = @errmsg + substring(@dbccrow, @s + 1, 1) +substring(@dbccrow, @s + 3, 1) +substring(@dbccrow, @s + 5, 1) +substring(@dbccrow, @s + 7, 1) +substring(@dbccrow, @s + 9, 1) +substring(@dbccrow, @s + 11, 1) +substring(@dbccrow, @s + 13, 1) +substring(@dbccrow, @s + 15, 1)FETCH NEXT FROM error_cursor INTO @dbccrowENDCLOSE error_cursorDEALLOCATE error_cursor-- Now chop first character which is the length, and cut after end.SELECT @errmsg = substring(@errmsg, 2, @msglen)GO
View 3 Replies
View Related
