Who Can Execute Sp_adduser ?
Aug 13, 2006
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).
View 8 Replies
ADVERTISEMENT
Oct 9, 2001
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.
View 1 Replies
View Related
Jul 20, 2005
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
View 2 Replies
View Related
Aug 9, 2007
I have a product purchased which has it's own sp_adduser stored procedure to add users to a table (seperate from sql server).
When the program tries to run sp_adduser, it executes the SQL 2005 sp_adduser and this is not what I want.
Is there a way to turn off sp_adduser for a certain user, or force it to use the local copy?
This program is compiled asp.net code and I cannot change the name of the procedure it calls.
Thanks,
Jimmy Cartrette
View 4 Replies
View Related
Sep 12, 2006
Hi guys,
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.
Any help or advise would be great!
Thanks,
~Jamie
View 1 Replies
View Related
May 10, 1999
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?
Thanks
Wayne
View 1 Replies
View Related
Dec 6, 2006
Dear all:
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?
Many thanks,
Tomorrow
View 5 Replies
View Related
Apr 19, 2007
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.
View 5 Replies
View Related
Mar 6, 2008
Hi.
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.
Any help would be greatly appreciated.
Thanks
Geoff.
View 7 Replies
View Related
Jun 25, 2007
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?
View 1 Replies
View Related
Jan 25, 2007
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????
View 3 Replies
View Related
Mar 3, 2008
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.
View 1 Replies
View Related
Sep 24, 2002
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.
View 2 Replies
View Related
Oct 28, 2001
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?
View 1 Replies
View Related
Oct 4, 2004
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?
View 4 Replies
View Related
Mar 26, 2004
hi all,
i am having stored procedure like the below one:
create procedure pro_emplname
@name varchar
as
select * from employee where ename=@name
i have tried like this to execute:
exec pro_emplname kamal
but i am not getting the output....
could any one tell.....
thanks
View 1 Replies
View Related
May 29, 2004
HI
i want to rite a sysntax in StoreProcedure that execute sysntaxes in My_file.sql .
How can i do this?
thanks
View 1 Replies
View Related
Apr 15, 2008
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
You can do anything at www.zombo.com
View 4 Replies
View Related
Jun 10, 2006
I want to start of run a job from DOS. I have MS SQL 2000 using Enterprise Manager.
Thank You,
Ernie
View 3 Replies
View Related
Dec 1, 2006
hi,this is my stored procedure:
CREATE PROCEDURE loop As
declare @fromage bigint
begin
select @fromage=max(age) from emp1
end
begin
insert into loop values(@fromage)
end
how to run thi spc in sqlserver,please tell me
View 1 Replies
View Related
Aug 6, 2007
hi,
What is wrong with this please?
I am passing two variables to execute a ssis package.
Thanks
set @cmd = 'dtexec /f ' + @FullPackagePath + ' /set Package.Variables[User::FileName].Properties[Value];"' + @FullFilePath + '"' +
' Package.Variables[User::ConnectionPath].Properties[Value];"' + @ConnectionPath + '"'
print @cmd
error is:
Option "Package.Variables[User::ConnectionPath].Properties[Value];Data Source=server1databasename" is not valid.
please note I just retyped the data source name here.
View 5 Replies
View Related
Feb 27, 2008
Hi Everyone,
I have problem in applying 'execute as'.
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.
Thank you in advance.
View 1 Replies
View Related
Dec 10, 2007
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.
View 1 Replies
View Related
Apr 3, 2007
Hi all,
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]')
------------------------------
ADDITIONAL INFORMATION:
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 ????
Many thanks to all,
John
View 4 Replies
View Related
Mar 8, 2007
I have a job that requires me to call and execute a SSIS package as the first step in a SQL2k5 Stored Procedure.
Can someone please give me a basic DTEXEC example?
Thanks in advance !
View 3 Replies
View Related
Mar 19, 2007
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:
Cnn.Open("...")
Comm.ActiveConnection = Cnn
Comm.CommandType = ADODB.CommandTypeEnum.adCmdStoredProc
Comm.CommandText = "spSklVnos-Prenos" 'that's thename of the sp
RetParam.Direction = ADODB.ParameterDirectionEnum.adParamOutput
RetParam.Type = ADODB.DataTypeEnum.adInteger
Comm.Parameters.Append(RetParam)
P1.Direction = ADODB.ParameterDirectionEnum.adParamInput
P1.Type = ADODB.DataTypeEnum.adVarChar
P1.Size = 1
P1.Value = LocParA
Comm.Parameters.Append(P1)
P2.Direction = ADODB.ParameterDirectionEnum.adParamInput
P2.Type = ADODB.DataTypeEnum.adInteger
P2.Value = LocPar1
Comm.Parameters.Append(P2)
P3.Direction = ADODB.ParameterDirectionEnum.adParamInput
P3.Type = ADODB.DataTypeEnum.adInteger
P3.Value = LocPar2
Comm.Parameters.Append(P3)
P4.Direction = ADODB.ParameterDirectionEnum.adParamInput
P4.Type = ADODB.DataTypeEnum.adInteger
P4.Value = LocPar3
Comm.Parameters.Append(P4)
P5.Direction = ADODB.ParameterDirectionEnum.adParamInput
P5.Type = ADODB.DataTypeEnum.adInteger
P5.Value = PrevzemIDx
Comm.Parameters.Append(P5)
Comm.Execute()
Am I missing something?
Thanks for any suggestion.
Dido
View 1 Replies
View Related
Feb 7, 2007
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
View 7 Replies
View Related
Sep 20, 2007
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;
}
View 6 Replies
View Related
Mar 25, 2008
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.
View 2 Replies
View Related
Apr 18, 2008
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(); } }}
View 2 Replies
View Related
Jun 18, 2008
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!
View 6 Replies
View Related
Jul 13, 2004
Hello,
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?)
Thanks a ton!
View 2 Replies
View Related
Mar 31, 2005
How can I execute am .sql script that was generated from SQL Server?
View 4 Replies
View Related
