Assign Role Of SQLAgentOperatorRole
Jun 13, 2007Hi there,
How can I assign a user role of SQLAgentOperatorRole.
I tried with sp_addsrvrolemember but failed.
Thanks
Rahul
ADVERTISEMENT
SP Error When Trying To Create User And Assign A Role
Aug 23, 2002I get the following errors associated with trying to create an SP.
Server: Msg 170, Level 15, State 1, Procedure AddFortuneUser, Line 8
Line 8: Incorrect syntax near '@newuser'.
Server: Msg 137, Level 15, State 2, Line 1
Must declare the variable '@newuser'.
Can anyone explain why I have to do a declare.
I suspect I have to issue "declare @newuser sysname" somewhere but I'm not sure why.
The following is the code I'm trying to run.
My intent would be to create a form for the Admin Clerk that would call this SP. That way they can create a generic login. They have an application that allows them to change the password after the fact.
/*
Created for Admin person to allow them to add a basic SQL Login Account
forcing the user to be a member of a specific role 'helmsman'
in a specific database 'Fortune'
*/
CREATE PROCEDURE AddFortuneUser
@newuser char(128)
AS
EXEC master..sp_addlogin @loginame=@newuser, @passwd =substring(@newuser,1,8), @defdb =Fortune
GO
if not exists (select * from dbo.sysusers where name = @newuser and uid < 16382)
EXEC sp_grantdbaccess @loginame=@newuser, @name_in_db=@newuser
GO
exec master..sp_addrolemember @rolename ='helmsman', @membername =@newuser
GO
How To Assign Execute Permission To Batch Of Stored Procs To A User/role
Mar 25, 2008Just wondering if anyone knows of a useful command to assign execute permissions to a batch of stored procs to a user/role. I've got too many stored procs to manually go thru the steps of browsing for them and scrolling thru each one and clicking "execute" for each one.
Also, would like to know if its possible to update a batch of stored procs that begin with a prefix like "spSomething_".
Any info would be helpful! TIA.
SQLAgentOperatorRole Flaw ?
Feb 7, 2007When a windowsuser being member of the SQLAgentOperatorRole (and not being sysadmin) creates a job (the owner = the domain account), and he wants takes a look at it afterward, he's not able to modify it !?!?!?
This would mean that when I want a user to be "jobmanager" for a server I need to make him sysadmin !!! ???
Is this a flaw in the system ?
Sql 2005 Database Role Vs Application Role ?
May 18, 2007Ive been reading over the documentation and some stuff online, but I still dont really understand what the difference is and when you would use one vs the other. Can someone put it in simple terms for this dummy (me) ?
thanks
Can't Make Database Role A Member Of Another Database Role In 2005.
Jan 9, 2006In sql server 2000, I created some custom database roles called ProjectLeader and Developer. I would make these roles a member in the fixed database roles so that I would only have to add the user to the ProjectLeader or Developer role once and they would presto-magico have the security I wanted them to have with no unecessary mouse clicking. I'm not sure how to repeat this process in 2005? Management Studio doesn't seem to allow you to add a role as a member in another role. Is there a work around or solution for this?
View 1 Replies View RelatedApplication Role, App Role
Jan 23, 2008 Hi all, I am trying to connect to the database using application role. But gives an error An error has occurred while establishing a connection to the
server. When connecting to SQL Server 2005, this failure may be caused by
the fact that under the default settings SQL Server does not allow remote
connections. (provider: SQL Network Interfaces, error: 26 - Error Locating
Server/Instance Specified)
for the given connection string Dim connstring
As String = "Data Source=Northwind;Initial
Catalog=OrderProcessing;Persist Security Info=True;UserID=application_login;Password=wewewe;"
Dim cmd
As SqlCommand
Dim
param As SqlParameter
Dim
cookie As Byte()
Dim cn As New
SqlConnection(connstring)
If
(cn.State = ConnectionState.Closed) Then
cn.Open()
End If Please help..
Thanking you, Nirmala
Adding A Database Role To A Database Role
Feb 29, 2008Hi,
I have SQL server 2005 (Developer edition) installed and I want to add a database role to a database role. It is working on my SQL 2000 server,however, when I tried to do the same thing on SQL2005, It didn't allowed me.
When I go to the Add role and then adding members to the role, the browse screen does not allow me the choice of object type Roles. It only shows me "Users".
Can someone please help me with this and provide me some information of how / what should I set to get the Roles in object types list so I can add a role to a role.
Thanks
How To Assign A Value To A Parameter?
Nov 17, 2006Hello everyone,
i have the parameter in my stored procedure that i am using as a sqldatasource.
Now in one of the events, i need to assign a value to the parameter. How can i do that?
Microsoft is changing the syntax so often, all solutions i found on this forum just don't work anymore, like:
SqlDataSource1.SelectParameters["@CompareInteger"].value= "1"
OR
SqlDataSource1.SelectParameters["@CompareInteger"].DefaultValue= "1"
I guess the SelectParameter - became 'ReadOnly'..But how to assign value to a parameter now?!?
Thanks for any help
Assign Variable Within SP
Nov 23, 2006I use SQL Server 2005 and in a Stored Procedure I want to execute a sql statement and assign the result to a variable. How can I do that?The name of the column I want to retreive the value from is "UserID"Here's my SP so far:
ALTER PROCEDURE [dbo].[spUnregisterUser]
@UserCode int
AS
BEGIN
SET NOCOUNT ON;
declare @uid uniqueidentifier
--get userid
SELECT @uid=UserID FROM tblUserData WHERE UserCode=@UserCode
-- Delete user
UPDATE tblUserData SET IsDeleted='True' WHERE UserCode=@UserCode
END
Assign A Resutset
Nov 11, 2000Hi
How can I assigne a value that return from a stored procedure into a variable?
Example:
if I execute this:
declare @szquery as nvarchar(256)
declare @icount as int
--I must pass a string query because the name of the field and the table are ---the variable that i passed
set @szquery='Select count(reckey) as dummy from table1 '
exec sp_executesql @szquery
the result is:
dummy
-----------
15770
(1 row(s) affected)
declare @b as int
I want assigned 'dummy' into a variable
set @icount= ???
if I ecexute this:
print @icount
the result :
15770
please help me out in this issue and i'd appreciate that.
tanks
Emiliano
HELP!!! How To Assign An Int Value Using A String??
May 23, 2002hi, can somebody help me to solve this problem?
first, declare 2 variables
declare @num int
declare @str varchar(255)
then set the varchar variable to '100/10'
set @str = '100/10'
is there anyway to assign a value to @num using @str, so that @num has a value 10??
set @num = @str ????????
Assign Value To Variable
Oct 6, 2015How to make it workable code..
declare @i int=1
declare @numweek int=2
declare @a int=35
declare @b int=29
declare @Wkstr1 date,@Wkstr2 date
[Code] .....
How To Assign 0 To A Null
Apr 22, 2008I have this query and I tried this but am getting the error for the case statement when I assign the nulls to a 0:
The error is they data types of the result expression of a CASE expression are not compatible.
SELECT
'C' as account,
FUND_dim.fund_cde as FUND,
case when sum(BRKGRPTT.daily_brkg_fact.accum_unit_cnt) is null then '0' else sum(BRKGRPTT.daily_brkg_fact.accum_unit_cnt) end as Units_Purchased
FROMFUND_DIM
left outer join BRKGRPTT.daily_brkg_fact
on FUND_DIM.FUND_ID_NUM = BRKGRPTT.daily_brkg_fact.FUND_ID_NUM
and BRKGRPTT.daily_brkg_fact.SEP_ACCT_ID_NUM <> 1
left outer join SEP_ACCOUNT_DIM
on BRKGRPTT.daily_brkg_fact.sep_acct_id_num = SEP_ACCOUNT_DIM.sep_acct_id_num
group by BRKGRPTT.sep_account_dim.sep_acct_cde, BRKGRPTT.fund_dim.fund_cde
order by FUND_dim.fund_cde
Assign XML To Variable
Jul 12, 2006How do I assign XmlDocument results to a variable (so that I can pass it to a sp)?
I know that the following code works....
select * from tblUsers where userId = 1225 for XML raw
It returns "<row UserId="1225" LastName="Evans" FirstName="Stephanie" MiddleInitial=...."), which is what I want. But when I try to assign it, I get an error "Incorrect syntax near 'XML'."
declare @strXml nvarchar(1000)
set @strXml = (select * from tblUsers where userId = 1225 for XML raw)
Ideas?
Assign Name To The DB Users.
Oct 20, 2006I'm developing a vb 2005 application and I€™m creating the users directly to the database. I want to assign them names.
I want to do something like this:
CREATE TABLE admin.db_users (
id INT CONSTRAINT db_user_pk PRIMARY KEY,
[name] VARCHAR(50) CONSTRAINT db_user_name_nn NOT NULL,
authentication VARCHAR(25) CONSTRAINT db_user_authentication_nn NOT NULL,
CONSTRAINT db_user_fk FOREIGN KEY(id)
REFERENCES sys.database_principals (principal_id)
ON UPDATE CASCADE
ON DELETE CASCADE
);
GO
This is the error that i'm getting:
Msg 1767, Level 16, State 0, Line 1
Foreign key 'db_user_fk' references invalid table 'sys.database_principals'.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
How do I solve this problem or how can I do something similar.
How To Assign SQL Query To Button In Asp.net 2.0
May 24, 2007Hi
My webpage contain three dropdown box and one button. One dropdown to choose hospital_id, second dropdown to choose project_id and third dropdown to choose version_no .After choosing these three and when the button is clicked i want to run this sql query
INSERT INTO version(project_id,hospital_id,date_created,comments) SELECT project_id,hospital_id,date_created,comments FROM version where version_no=@version_no and project_id=@project_id and hospital_id=@hospital_id.
Just i need the code in asp.net 2.0 , VB , when the button is clicked the above query should run and the parameters (values) should take from dropdown box.
Could anyone please send the solution for the above problem.
Assign A Foreign Key As A Parameter
Oct 15, 2007hi everybody
I have the following asp.net2.0 codeSelectCommand = "Select IDEmp, FirstName,MiddleName,LastName, Date,HoursNumber, Description
From Employee, WorkOnCategory , CatDesignItemReference
where IDEmp =IDEmplWork AND FirstName = @FirstName AND Category = @AnyCategory AND ">
<SelectParameters>
<asp:ControlParameter ControlID="txtEmployeeName" DefaultValue="" Name="FirstName" PropertyName="Text" Type="String" /> <asp:ControlParameter ControlID="DropDownList1" DefaultValue="" Name="AnyCategory" PropertyName = "SelectedValue" Type="String" />
</SelectParameters>
</asp:SqlDataSource>
The problem is that the AnyCategory in my DropDownList1 refers to a key but unfortunalely my code understand it as string so it assigns
Category = @AnyCategory as if Category ='AnyCategory ' not as Category = AnyCategory
So what should I do
Thanks
What Property Should I Assign To My Sqlcommand ?
Apr 2, 2008what property should i assign to myPuzzleCmd2 ?
myConnection.Open()
Dim myPuzzleCmd2 As New SqlCommand("GetRandomCode", myConnection)
myPuzzleCmd2.CommandType = CommandType.StoredProcedure
Dim retLengthParam As New SqlParameter("@Length", SqlDbType.TinyInt, 6)
retLengthParam.Direction = ParameterDirection.Input
myPuzzleCmd2.Parameters.Add(retLengthParam)
Dim retRandomCode As New SqlParameter("@RandomCode", SqlDbType.VarChar, 30)
retRandomCode.Direction = ParameterDirection.Output
myPuzzleCmd2.Parameters.Add(retRandomCode)
Try
Dim reader As SqlDataReader = myPuzzleCmd2.ExecuteReader()
myPuzzleCmd.ExecuteNonQuery()Catch ex As Exception
myPuzzleCmd2 = Nothing
Session.Remove("RandomCode")
HttpContext.Current.Session("RandomCode") = myPuzzleCmd2("@RandomCode") < --------- Over here
Finally
myConnection.Close()
End Try
How To Assign Password And User Id
May 8, 2008Hi
I have add one new database mdf file in my project by ---> add new Item in to project.
But This database.mdf file is in windows authetication mode.
So there is no password assign to this.
I want to assign use id and password to this database or I want to give sql server authetication mode.
I have tried Modfy Connection property of database but that is not working. what is the default username of this conncetion?????
REply.....
How To Assign The @@IDENTITY To A Variable
Feb 24, 2005Hi,
HOW can I assign the value of @@IDENTITY to the any variable in SQL SERVER .
Thanks,
How To Assign Logins To A Group
Oct 18, 2013I have created a program that allow the user to register account as new user to sql. I do not want everyone use the same account (default sa).Now i want to group those users into one group which easy for management.But I cant find way to create new login group.I search over net, they said need to done under computer management--local user and group, but my computer do not have local user and group..
View 3 Replies View RelatedAssign Sequential Numbers
Mar 17, 2004I am trying to automatically insert records into my existing customer table. Is there a way when I insert these new records and assign the customer number that it can sequentially pick the next available unique customer number for each record that is inserted? for example the first record would be customer number 100, the next 101, and so on? Please advise.
View 4 Replies View RelatedRandomly Assign To Group
Oct 23, 2014My issue is that I have 10 accounts that were assigned to 5 agents with each agent receiving 2 accounts. I would know like to randomly reassign the accounts with the only criteria being that the random allocation not reassign to the same agency and each agent gets 2 accounts again.
Data looks like below and I want to populate the randomly assigned agent in the "Second_Agent" column.
Account,First_Agent,Second_Agent
B1,1,
B2,1,
B3,2,
B4,2,
B5,3,
B6,3,
B7,4,
B8,4,
B9,5,
B10,5,
How to most effectively achieve this using SQL?
ASSIGN Record Equal
Mar 31, 2006Good day!
I have the qry, which is suppose to assigned records to active user (almost 15 users) equal, but it doesn’t- sometime it assigned more to some users and less to others. How could I modify my qry to make sure it assigns the records equals to each user? Please, see the qry below. I will appreciate any help
Thk
UPDATE
TBLFRAUDFINDER
SET
DATE_ASSIGNED=GETDATE(),
FRAUDANALYSTASSIGNED=@FRAUDANALYST
WHERE
FRAUDID=@FRAUDID OR
(FRAUDANALYSTASSIGNED IS NULL AND
((INQUIRY_GOVT_NUMBER=@INQUIRY_GOVT_NUMBER) OR
(CUST_NM=@CUST_NM) OR
(ALERT_IDENTIFIER=@ALERT_IDENTIFIER) OR
(ACCT IN (SELECT
ACCT
FROM
TBLFRAUDFINDER
WHERE
INQUIRY_GOVT_NUMBER=@INQUIRY_GOVT_NUMBER OR
CUST_NM=@CUST_NM OR
ALERT_IDENTIFIER=@ALERT_IDENTIFIER))
Assign Function To Table Var??
Sep 19, 2007I keep getting a declare error on this:
DECLARE @SplitTable TABLE(Idx BIGINT IDENTITY(1,1), SplitData VARCHAR(20), Size INT)
SET @SplitTable = dbo.SplitToTable('|', '1|One|0:26:20|541|abc|xxx|6:26:33|21705')
All I want to do is assign the table that my function returns to a variable. Should be easy, but I tried the above and assigning with 'as':
select * from dbo.SplitToTable('|','1|One|0:26:20|541|abc|xxx|6:26:33|21705') as Split
that didn't work either.
Also tried:
DECLARE @SplitTable TABLE(Idx BIGINT IDENTITY(1,1), SplitData VARCHAR(20), Size INT)
select @SplitTable= from dbo.SplitToTable('|','1|One|0:26:20|541|abc|xxx|6:26:33|21705')
what am I missing?
--PhB
Assign Records To Analysts
Jul 20, 2005Here is my issue. I have a list of merchants (generated daily) and Ineed to assign them eually to a set of analysts. Both the merchantlist and analyst list can change daily. I want to assign each merchantwith an analyst daily and need help to write out a stored procedurefor this (do not want to use VB).Here is relevant code for the tables:CREATE TABLE MerchList (MerchListID int IDENTITY (1, 1) NOT NULL ,FileDate datetime NOT NULL ,MerchID int NOT NULL ,AnalystID int NOT NULL ,)CREATE TABLE tblAnalyst (AnalystID int IDENTITY (1, 1) NOT NULL ,AnalystName varchar(100) NOT NULL)there will be about 10000 records in table MerchList and around 25records in table tblAnalyst.This will be used to assign work to analysts on a daily basis.Thanks for all your help!Vishal
View 5 Replies View RelatedAssign A Value To A Variable Without Using SQL Task
Nov 30, 2006Greetings once again,
I am trying to achieve a seemingly simple task of assigning datetime value to a user variable at the point my package starts running. How can I do this without using a SQL Script Task? Should I be using a script task for this or is there a simpler way to achieving the same thing?
Thanks in advance.
How To Assign The View To A User?
Oct 29, 2006im using the Northwind database, i use T-SQL commands to create a view that restricts users from seeing the address, city for all employees.
i dont know how to assign the view to a user in the database using Enterprise manager.
pls help me, im really needing a answer.
thanks!
Assign Cluster # To Customers
Dec 31, 2007
I deployed a clustering project. As a result, I got 10 clusters. Is there an automatic way to create a table with the customer ID and the cluster # that each customer was assigned to?
Thanks!
Ezes
Conditional Split - Assign To Value
Jun 13, 2007Hello,
When you´re comparing values in the Condition of the Conditional split, can you assign a value to a variable?
If so, how can you accomplish this?
Thank you.
Assign Null Value To Variable
Sep 5, 2007 I have a column with int data type.. i am trying to assign this column to a variable ( int) in For Each Loop..but it keeps giving me an erro
The type of the value being assigned to variable "User:ubcontractor_Key" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
Am i getting this error becasue the column has NULL value?. how can I resove this probelm?
Assign Text To Variables
Aug 1, 2006I have a simple Execute DTS task which uses ADO.NET connection to update variable........and get the following error.
Variable:
@msg string
Query:
Select @msg = 'Invalid Process Dates'
[Execute SQL Task] Error: Executing the query "Select @msg = 'Invalid Process Dates'" failed with the following error: "The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 3 ("@msg"): Data type 0xE7 has an invalid data length or metadata length.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Any one has clue how to fix it? I could use the OLEDB connection to get it work but I need to use ADO.NET. Any help will be appreciated.