I tried to execute the sp_adduser system stored procedure, but I always get an error saying that I don't have permission to perform this action:
Msg 15247, Level 16, State 1, Procedure sp_adduser, Line 35. User does not have permission to perform this action.
I've tried to execute sp_adduser with different users: one that is a member of the db_accessadmin role, the database owner, and even a member of the sysadmin server role. In all cases, I received the error showed above.
Is there some problem with this stored procedure ? I know that SQL Server 2005 recommends using CREATE USER, to add users to a database, but I couldn't get it to work with a user name and a password as parameters (combined with CREATE LOGIN).
I am trying to setup a user that will have access to execute the sp_adduser system stored procedure but cannot get it to work. I would assume that putting it in the db_accessadmin role would do this but it will not work. It tells me "Only members of the sysadmin role or the database owner can execute this stored procedure.". Just to pacify it, I gave it access to dbo in the database we are wanting to add the user but it still throws out the same error. Any ideas would be greatly appreciated.
Hello GroupOk, I've read the BOL topic on sp_adduser and have done some research herein the groups but the 'why' question remains...A simple thing:I would like to empower 'User A' to be able to add logins and users todatabase 'ABC' so I don't have to do it.Let's assume I'm far away on a tropical island with no remote access forseveral months ...ahhhhNow, I've added the Login for 'User A' to the server roles 'SecurityAdministrators' and 'System Administrators' and given access to thedatabases 'ABC' and 'master' - it all works, 'User A' can create logins andusers for 'ABC'What I find a little bit disturbing is, that this gives 'User A' thepossibility to do anything with any database and I just wondered whetherthere's a way.. to avoid this perhaps?So just createing logins and creating users permissions for database 'ABC'and as little as needed from everything else...Please describe in detail on how to do this as I'm new to this and don'thave much of a clue..as you see!Later on, I might want to create a 'UserB' with the same capabilities butfor now I'm just happy to get going.Thanks for your help & efforts!!Martin
I'm having an issue with using the sp_adduser stored procedure. What i'm doing is creating a new database through coldfusion. After the database is created i need to creatd a Login. So i used the sp_addlogin and all of that worked great. Next in order to make this work i need to add a user to a database role for the new database....
The problem i am having is when i do this, every time i do this the user is automatically put into the "master" database, instead of the database i need it in which is what was just created. I'm guessing this is because "master" is the database that contains the stored procedure for creating a new user. Is there a way in which i could use this stored procidure to create a user in the database that i just created? I was thinking maybe copy that stored procedure into the new database and use it that way but i'm not sure how to do this either. Everything needs to be done programmatically as well. Its all being built in one coldfusion function so i don't have the option to go into enterprise manager and manual do this.
I am trying to add a new user (SQL Server 6.5) from an application program (Delphi 4.0). I was hoping I could do this in a stored procedure that basically acts a a wrapper around the SQL Server stored procedures.
It partially works - here is my procedure:
create procedure AddUserToSQLServer(@loginame varchar(30), @Password varchar(30) ) as begin
-- Add User to SQL Server Database exec mydb.dbo.sp_addlogin @loginame,@Password,'mydb'
exec mydb.dbo.sp_adduser @loginame
end
The sp_addlogin adds the login and sets the default database as mydb. This is OK, except the user still can not login to the application. So I added the sp_adduser. This gives the user access to the application, but I still have a problem. When I go into Executive Manager, and go to the Manage Logins screen by double-clicking on the newly added user, I get the message "Error 21770: The name 'loginame' was not found in the users collection" where loginame is the users login. I click OK to this message, and the user is displayed on the manage logins screen. Their default database is mydb, a check is in the Permit box and their username is in the User column. Alias and Group are blank. When I try to drop the user, I get the same error again (2 times), then error "Error 15175: [SQL Server] login 'loginame' is aliased or mapped to a user in one or more database(s), drop user or alias before dropping login".
The only thing I found that works is to go to SQL and issue sp_dropuser loginame, then I can drop the user.
Anybody see what's wrong? Or know of a FAQ that addresses this situation?
I had got the below error when I execute a DELETE SQL query in SSIS Execute SQL Task :
Error: 0xC002F210 at DelAFKO, Execute SQL Task: Executing the query "DELETE FROM [CQMS_SAP].[dbo].[AFKO]" failed with the following error: "The transaction log for database 'CQMS_SAP' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
But my disk has large as more than 6 GB space, and I query the log_reuse_wait_desc column in sys.databases which return value as "NOTHING".
So this confused me, any one has any experience on this?
I'm looking for a way to refer to a package variable within any Transact-SQL code included in either an Execute SQL or Execute T-SQL task. If this can be done, I need to know the technique to use - whether it's something similar to a parameter placeholder question mark or something else.
FYI - I've been able to successfully execute Transact-SQL statements within the Execute SQL task, so I don't think the Execute T-SQL task is even necessary for this purpose.
I have a master package, which executes child packages that are located on a SQL Server. The Child packages execute other child packages which are also located on the SQL server.
Everything works fine when I execute in process. But when I set the parameter in the mater package ExecutePackageTask to ExecuteOutOfProcess = True, I get the following error
Error: 0xC00470FE at DFT Load Data, DTS.Pipeline: SSIS Error Code DTS_E_PRODUCTLEVELTOLOW. The product level is insufficient for component "Row Count" (5349).
Error: 0xC00470FE at DFT Load Data, DTS.Pipeline: SSIS Error Code DTS_E_PRODUCTLEVELTOLOW. The product level is insufficient for component "SCR Custom Split" (6399).
Error: 0xC00470FE at DFT Load Data, DTS.Pipeline: SSIS Error Code DTS_E_PRODUCTLEVELTOLOW. The product level is insufficient for component "SCR Data Source" (5100).
Error: 0xC00470FE at DFT Load Data, DTS.Pipeline: SSIS Error Code DTS_E_PRODUCTLEVELTOLOW. The product level is insufficient for component "DST_SCR Load Data" (6149).
The child packages all run fine when executed directly, and the master package runs fine if Execute Out of Process is False.
I have a SSIS package contains an "Execute SQL Task". The SQL will raise error or succeed. However, it sounds the package won't pick up the raised error?
Or is it possible to conditional run other control flow items according the the status of SQL task execution?
I am trying to execute a SP in the execute SQL task in SSIS 2005..
but I keep getting an error:
SSIS package "Package.dtsx" starting. Error: 0xC002F210 at Load_Gs_Modifier_1, Execute SQL Task: Executing the query "exec Load_GS_Modifier_1 ?, ?" failed with the following error: "Could not find stored procedure 'exec Load_GS_Modifier_1 ?, ?'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. Task failed: Load_Gs_Modifier_1 SSIS package "Package.dtsx" finis
I have set up two user parameters: startdate and enddate.. I am not sure what I am doing wrong????
I have trigger, but not execute somedata because insert few row in every second. I use java to insert data to SQL server 2005. Data inserted to a table but not executing trigger for some data. For example 100 data every second inserted to a table.
If insert data one by one to a table trigger fires success. Please Help me.
I am trying to return a recordset to an ASP page from a stored procedure.
The stored procedure creates a temporary table and then builds an SQL statement into a string declared with the SP. The string contains an INSERT INTO statement to insert into the temporary table and followed by a select of the rows I wish to insert into the table.
I then use the EXECUTE method to execute the SQL string. After this I have a SELECT which should select from the temporary table and return the rows i am interested in from the stored procedure.
For some reason this does not seem to work. If I execute the SP from query analyzer I get the resultset returned. However when I try to call the stored procedure from my ASP no recordset is returned. I have done this type of thing before and it has worked perfectly. Does anyone have any ideas why this may not be working? Many Thanks.
Newbie Question: I'm in EM and I want to execute a simple SQL statement (e.g. exec a sproc). How do I do it? Do I have to fire up QA? or is there a simpler way?
I don't remember where I saw it, but it looked something like this: ...exec (0x0A738... The result was identical to executing: select getdate() Anybody seen it anywhere?
I have two instances running on a machine... The following code will run on the named instance but not on the default instance.
On the default instance I get the following error: Cannot execute as the user 'SA', because it does not exist or you do not have permission.
However if I specify another account it does work.
use dbReport go DROP procedure dbo.clarktest GO create procedure dbo.clarktest WITH EXECUTE AS 'SA' -- 'DomainSqlCmdShellDev' as EXEC xp_cmdshell 'dir' go grant exec on clarktest to [spexec] exec clarktest use master go
Any assistance is appreciated as the developers code needs to be coded generically i.e. "sa" this is to prevent code manipulation during migrations... Thank you
Here is an exsample SP Create procedure test with execute as 'user1' as select * from people.dbo.profile
I have 2 accounts: user1 and user2. user1 has permission to select data from people.dbo.profile but user2 doesn't. due to some reasons I don't want to grant select permission to user2.
I use user2 to execute this SP and get error message
The server principal "user1" is not able to access the database "people" under the current security context.
How can I solve it? I have been looking for solution for the whole morning.
Hi, I have some problems with the execute as command.
I have a proc in a database, lets call it 'source'. The proc looks like this:
create proc backup_proc
with execute as 'account2'
as
select * into destination.dbo.loaddata from source.dbo.loaddata
exec dbo.backup_proc
I also have two accounts: account1 - limited rights account2 - extended rights
I wan't to be able to run the proc which uses account2(extended rights) to copy a table to another database. I get this error when i try to execute the proc! The server principal "destination" is not able to access the database "source" under the current security context.
I have an SQL stored procedure that executes the following MDX query over an OLAP cube:
SELECT * FROM OpenRowset(''MSOLAP.3'',''DATASOURCE=localhost;Initial Catalog=Analysis Services Project1;'','select {[Measures].[Qty]} on columns from [DB ATEST]')
If I call the stored procedure from Management Studio, it executes perfectly well and returns the result I am expecting: the result of the MDX query.
However, when I call this same stored procedure from Reporting Services, as I try to build up a new report, I get the following message:
TITLE: Microsoft Report Designer ------------------------------
An error occurred while executing the query. Cannot initialize the data source object of OLE DB provider "MSOLAP.3" for linked server "(null)". The OLE DB provider "MSOLAP.3" for linked server "(null)" reported an error. The provider did not give any information about the error. SELECT * FROM OpenRowset('MSOLAP.3','Initial Catalog=DB ATEST;','select {[Measures].[Qty]} on columns from [DB ATEST]')
Cannot initialize the data source object of OLE DB provider "MSOLAP.3" for linked server "(null)". The OLE DB provider "MSOLAP.3" for linked server "(null)" reported an error. The provider did not give any information about the error. SELECT * FROM OpenRowset('MSOLAP.3','Initial Catalog=DB ATEST;','select {[Measures].[Qty]} on columns from [DB ATEST]') (Microsoft SQL Native Client)
I've tried it with both an SQL Server and a OLE DB datasource.
I'm connecting locally on my PC (localhost).
Any clues as to why this works via Management Studio but not when called from RS ????
Hellou I'm trying to execute simple sp with 5 input parmeters from vb.net. This sp works great when it is run from SQL Server management studio, but when I want to execute it from vb.net, I allways get this error: "Syntax error, permission violation, or other nonspecific error" Here's the code:
Hello,I have an SQL procedure as follows: ... INSERT dbo.Levels (LevelName) VALUES (@LevelName) ... LevelName is an input parameter of nvarchar type. What should be the best way to execute this procedure from my C# / VB.Net code? And what would it return?Thanks,Miguel
I'm trying to do something like the code below, but it's saying "specified cast is not valid" If i change the value returned to an "int", it works fine. My issue is, i'd like to get the value returned with more accuracy than an int as there will be 2 decimal places.protected float getProjectHours(string project) {string selectCmd = "SELECT SUM(hours) FROM tasks WHERE project=@project"; string strConnection = ConfigurationManager.ConnectionStrings["TimeAccountingConnectionString"].ConnectionString;SqlConnection myConnection = new SqlConnection(strConnection); SqlCommand myCommand = new SqlCommand(selectCmd, myConnection);myCommand.Parameters.Add(new SqlParameter("@project", SqlDbType.VarChar));myCommand.Parameters["@project"].Value = project; myConnection.Open();float total = (float)myCommand.ExecuteScalar(); myConnection.Close(); return total; }
I hope this is a right form for ADO .net type of question. My question is, can you call SQL function the way you call stored procedure from ADO .net. I coded it this way and does not seems to be getting result set back. The DataReader is seems to be coming back with nothing. Can someone post an example. I know you can write "SELECT udf_function()" but I really mean the way the stored procedure is called. Thanks.
I found a example of using a button inside of a gridview at http://msdn2.microsoft.com/en-us/library/system.web.ui.webcontrols.buttonfield.aspx.I modified the code-behind and added: // Insert the producer into the database SqlDataSource sds = selectedRow.FindControl("sqlItemInsertIntoListing") as SqlDataSource; sds.Insert(); but the page throws an error.Can someone look at my code-behind and show me how to execute the line 56 in the code-behind? Thanks. Object reference not set to an instance of an object.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.NullReferenceException: Object reference not set to an instance of an object.
Source Error:
Line 55: // Insert the producer into the databaseLine 56: SqlDataSource sds = selectedRow.FindControl("sqlItemInsertIntoListing") as SqlDataSource;Line 57: sds.Insert();Line 58:Line 59: } Below is my code: ASPX PAGE: <asp:Label ID="Message" ForeColor="Red" runat="server" AssociatedControlID="CustomersGridView" /> <!-- Populate the Columns collection declaratively. --> <asp:GridView ID="CustomersGridView" DataSourceID="CustomersSqlDataSource" DataKeyNames="ItemID" AutoGenerateColumns="False" OnRowCommand="CustomersGridView_RowCommand" runat="server" AllowPaging="True" AllowSorting="True" PageSize="50"> <Columns> <asp:BoundField DataField="ProducerName" HeaderText="Producer" SortExpression="ProducerName" /> <asp:BoundField DataField="ItemName" HeaderText="Item" SortExpression="ItemName" /> <asp:BoundField DataField="Year" HeaderText="Year" SortExpression="Year" /> <asp:BoundField DataField="RegionMasterName" HeaderText="Region" SortExpression="RegionMasterName" /> <asp:BoundField DataField="CountryName" HeaderText="Country" SortExpression="CountryName" /> <asp:BoundField DataField="StateName" HeaderText="State" SortExpression="StateName" /> <asp:TemplateField HeaderText="ItemID" InsertVisible="False" Visible="false" SortExpression="ItemID"> <ItemTemplate> <asp:Label ID="ItemID" runat="server" Text='<%# Bind("ItemID") %>' Visible="false"></asp:Label> <asp:SqlDataSource ID="sqlItemInsertIntoPart" runat="server" ConnectionString="<%$ ConnectionStrings:VBJimboConn %>" InsertCommand="INSERT INTO [ZCPart] ([PartUserId], [PartItemID]) VALUES (@PartUserId, @PartItemID)" OnInserting="sqlItemInsertIntoPart_Inserting" > <InsertParameters> <asp:Parameter Name="PartUserId" /> <asp:ControlParameter Name="PartItemID" ControlID="ItemID" PropertyName="Text" Type="Int32" /> </InsertParameters> </asp:SqlDataSource> </ItemTemplate> </asp:TemplateField> <asp:ButtonField ButtonType="Button" CommandName="Select" Text="Add to Part" Visible="True" /> <asp:HyperLinkField DataNavigateUrlFields="ItemID" DataNavigateUrlFormatString="ItemDetails.aspx?ItemID={0}" Text="Details" /> </Columns> </asp:GridView> <asp:SqlDataSource ID="CustomersSqlDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:CONNSTG %>"....> </asp:SqlDataSource> CODE-BEHIND:using System;using System.Collections;using System.Configuration;using System.Data;using System.Linq;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.HtmlControls;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.Xml.Linq;partial class Inventory_InventoryList : System.Web.UI.Page{ protected void Page_Load(object sender, System.EventArgs e) { if (!Page.IsPostBack) { if (!User.Identity.IsAuthenticated) { CustomersGridView.Columns[CustomersGridView.Columns.Count - 2].Visible = false; } } } protected void sqlItemInsertIntoPart_Inserting(object sender, System.Web.UI.WebControls.SqlDataSourceCommandEventArgs e) { e.Command.Parameters["@PartUserId"].Value = Membership.GetUser().ProviderUserKey; }// Source for gvInventoryList to display message: http://msdn2.microsoft.com/en-us/library/system.web.ui.webcontrols.buttonfield.aspx protected void CustomersGridView_RowCommand(Object sender, GridViewCommandEventArgs e) { // If multiple ButtonField column fields are used, use the // CommandName property to determine which button was clicked. if(e.CommandName=="Select") { // Convert the row index stored in the CommandArgument // property to an Integer. int index = Convert.ToInt32(e.CommandArgument); // Get the last name of the selected author from the appropriate // cell in the GridView control. GridViewRow selectedRow = CustomersGridView.Rows[index]; TableCell contactName = selectedRow.Cells[1]; string contact = contactName.Text; // Display the selected author. Message.Text = "You selected " + contact + "."; // Insert the producer into the database SqlDataSource sds = selectedRow.FindControl("sqlItemInsertIntoListing") as SqlDataSource; sds.Insert(); } }}
Hi all, i want to write a stored procedure to get the user details from userprofile table based on the time schedule from another table called reminders. the structure of reminder table is called jobtype - type of job that is sending mails to user or sending mails to employee like that frequency - how frequenctly sending mails that is daily or weekly or monthly weekday - if the above option is weekly then we have to take which day on weekly to send mail dayofmonth - if the frequency option is monthly then we have to take this field for which day on month jobtime - At what time to send mails it may be daily or weekly or monthly so i want to write the stored procedue based on above values to execute some task( that is getting userdetails and sending mails). please help me. Thanks!
I am trying to figure out the best way to make VB6 execute an auto-generated .SQL file from SQL Server 2000.
SQL Server 2000 has created a script (about 1000 lines long), creating views and SPs, and I need to execute said script from a VB client application. Similar to the effect of pasting it into the Query Analyser, only automated.
Anyone have a suggestion (besides reformatting evey like and hard-coding it?)
