We normally map a single SQL user to all application users . From performance standpoint and from best practices perspective , what is the preferred method of mapping OS users to SQL users viz one to one OR many to one .
I m using SQL server 2005 express edition and management studio express. I m still confused with some terms. What is the difference between a new server registration, a new login and a named instance? I installed sql express using default configuration. It shows that my server name is- TANVIRSQLEXPRESS (where Tanvir is my hostname) and it uses windows authentication. I created a new log in from Security>logins in the object explorer of express manager. In the property of this new login I just put the login name and gave password under sql server authentication and leaving everything else as default. I have enabled tcp/ip for both sqlexpress and client protocol in Sql server configuration manger. YET, I cant log in using my new log in name and password. I got the following error- Cannot connect to TANVIRSQLEXPRESS. ADDITIONAL INFORMATION: Login failed for user 'Tanvir'. The user is not associated with a trusted SQL Server connection. (Microsoft SQL Server, Error: 18452) ------------------------------ I just want to know HOW to create a user with password in sql express edition.
I m using SQL server 2005 express edition and management studio express. I m still confused with some terms. What is the difference between a new server registration, a new login and a named instance? I installed sql express using default configuration. It shows that my server name is- TANVIRSQLEXPRESS (where Tanvir is my hostname) and it uses windows authentication. I created a new log in from Security>logins in the object explorer of express manager. In the property of this new login I just put the login name and gave password under sql server authentication and leaving everything else as default. I have enabled tcp/ip for both sqlexpress and client protocol in Sql server configuration manger. YET, I cant log in using my new log in name and password. I got the following error- Cannot connect to TANVIRSQLEXPRESS. ADDITIONAL INFORMATION: Login failed for user 'Tanvir'. The user is not associated with a trusted SQL Server connection. (Microsoft SQL Server, Error: 18452) ------------------------------ I just want to know HOW to create a user with password in sql express edition.
Strange question here. When I script a user to be created for a database deployment, I get the following script:
USE [DatabaseX]
GO
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'newuser')
CREATE USER [newuser] WITHOUT LOGIN WITH DEFAULT_SCHEMA=[dbo]
Now, this creates the user at the 'DatabaseX' db level, but not at the 'master' db level. The user "newuser" is then not allowed access to do anything in 'DatabaseX', such as execute queries, update tables, etc.
I had to modify our script to be like this:
USE [master]
GO
CREATE LOGIN [newuser]
WITH PASSWORD=N'somepassword',
DEFAULT_DATABASE=[DatabaseX]
GO
USE [DatabaseX]
GO
CREATE USER [newuser] FOR LOGIN [newuser]
GO
USE [DatabaseX]
GO
EXEC sp_addrolemember N'db_owner', N'newuser'
GO
This allows the "newuser" to execute queries, update tables, etc. in 'DatabaseX',
Is there some option in SSMSE that I'm missing when I'm having it generate my user scripts that it's leaving the rest of the script out?
Hello. I'm trying to create a new login and username inside a trigger using variables.
This code works:
create login testUserName with password = 'testPassword'
The problem comes when I try to use variables for the username and password like this.
create login @username with password = @password
Does anybody know how to get around this problem?
BTW, the error message it gives is this, but I really doubt that semicolons have anything to do with it. If I literally type my data in the create login call it works fine, but when I use variables it doesn't.
Msg 102, Level 15, State 1, Line 14
Incorrect syntax near '@username'.
Msg 319, Level 15, State 1, Line 14
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
Hello, I cannot find out to create enum data type in SQL Server 2005 Express. Can I easily create the enum type just like the MySQL does.(please the MySQL example below)
CREATE TABLE myTable ( myid INT UNSIGNED NOT NULL, myclass ENUM('FIRST','SECOND','THIRD') DEFAULT 'FIRST' PRIMARY KEY(myid) );
I would like to know if there is a way to find out who changed a users roles/access WITHOUT using the audit function. For example, if a user account was created and given SA access then changed to read only, how can I find out who made that change? I tried searching for an answer, but kept getting no results. I'm thinking this may tie into the sys.sysusers view?
We have an SSAS instance where when we run the query "select * from $system.discover_traces" the creation time in the resultset shows a different time from when we actually started the trace.
for example if we have create the trace at 3.30pm it shows 7.35 pm in the Sql server management studio resultset when we run the query "select * from $system.discover_traces".
This is my first time to deploy an asp.net2 web site. Everything is working fine on my local computer but when i published the web site on a remote computer i get the error "Failed to generate a user instance of SQL Server due to failure in retrieving the user's local application data path. Please make sure the user has a local user profile on the computer. The connection will be closed" (only in pages that try to access the database) Help pleaseee
INSERT INTO UserInquiries (UserName, InquiryDate) SELECT 'George', '10/1/2003' UNION SELECT 'George', '10/2/2003' UNION SELECT 'George', '10/3/2003' UNION SELECT 'George', '10/4/2003' UNION SELECT 'George', '10/5/2003' UNION SELECT 'George', '10/6/2003' UNION SELECT 'George', '10/7/2003' UNION SELECT 'George', '10/8/2003' UNION SELECT 'George', '10/9/2003' UNION SELECT 'George', '10/10/2003' UNION SELECT 'George', '10/11/2003' UNION SELECT 'George', '10/12/2003' UNION SELECT 'Harold', '11/1/2003' UNION SELECT 'Harold', '11/2/2003' UNION SELECT 'Harold', '11/3/2003' UNION SELECT 'Harold', '11/4/2003' UNION SELECT 'Harold', '11/5/2003' UNION SELECT 'Harold', '11/6/2003' UNION SELECT 'Harold', '11/7/2003' UNION SELECT 'Harold', '11/8/2003' UNION SELECT 'Harold', '11/9/2003' UNION SELECT 'Harold', '11/10/2003' UNION SELECT 'Harold', '11/11/2003' UNION SELECT 'Harold', '11/12/2003'
I'm brain dead on other things right now. Any help would be gratefully accepted.
I am new to ASP.NET so pordon me if my questions seems to be stupid.
I was given an assign to develop a ASP that would return results from a SQL server.
The problem is that part of my query to the SQL server comes from the webform
select a.invnum, a.invdate, a.duedate, a.invamt,a.payamt from vpshead a inner join vendors b on (a.vendnum = b.vendnum) where (a.vendnum = " & user.text & " ) and (b.vendpass = " & Pass.text & " ) and (a.payflag <> 'V') and ( (a.invamt <> 0) or (a.payamt <> 0) )and (a.chkno = '') order by a.invdate"
As your can tell I am using the user.text and Pass.text in my query which will come from the web form.
I'm trying to produce a user activity table out of a table that contains rows on when users log in or out. However, I can't seem to get the query to 'pick' the right date time in the join.
Here is what I came up with, but it doesn't seem to be working right :eek:
select distinct (userin.changeid), userin.userid, userin.createdate as LoggedInDate, userout.createdate as LoggedOutDate, DateDiff(mm,userin.createdate,userout.createdate) as Duration from userChange userin inner join ( select * from userChange where loggedin = 0 ) userout on userin.userid = userout.userid where and userin.loggedin = 1 and userin.createdate < userout.createdate order by userin.createdate desc
Dear Masters; I have a Messages table; I use this table to post System Messages to my users. But what I don't know is how can I add data for multiple users. I mean I want to add same message for multiple users (ex: Please update your infos). In the belove table I have some messages for users 100 and 200; How can I add same (please update your infos) messages to both users? Ex: Messages Table ID UserID Msg 1 100 "Hello" 2 100 "Hi" 3 200 "Hello"
just getting started with my first db driven web project... I am using a MySql database with membership and roles and got that working fine. I have tables containing details of courses that users are enrolled on etc. and want to display a list of courses for the user that is signed in, so he can continue lessons on the one of his choice. How do I pass the users name to the database query for a DataList control. So far I have tried lots of variations of the following: <asp:SqlDataSource ID="dsCourses" runat="server" ConnectionString="<%$ ConnectionStrings:xxx %>" ProviderName="<%$ ConnectionStrings:xxx.ProviderName %>" SelectCommand="SELECT c.CourseName FROM courses c, enrolments e WHERE c.CourseID=e.CourseID AND e.Username='<% =User.Identity.Name %>'"></asp:SqlDataSource> <asp:DataList ID="DataList1" runat="server" DataSourceID="dsCourses"> <ItemTemplate> <asp:HyperLink ID="HyperLink1" runat="server" NavigateUrl="Lesson.aspx">'<%# Eval("CourseName") %>'</asp:HyperLink><br /> </ItemTemplate></asp:DataList> </p> However, the <% =User.Identity.Name %> doesn't work here (but it does elsewhere on the page) - not sure why?? The code works fine if I hard code a user name into the select statement. Suggestions pleeeeeese!!
I need to be able to have users run a query under the 'sa' account that will create another account that will just be used for reading and writing to a specific database. Is there a way to do this with just a sql script or can you only set up logins and accounts from the enterprise manager?
I have a need to identify the name of a user and would like to access the NT userlist. Or failing a realtime access, can I achieve a periodic update of a SQL table with userid, username from the NT userlist?
I am trying to retrieve the user names of all users within a database. The problem is, that I have only created a login within SQL 7.0 for the NT group that the users belong to.
As such, when I try to query the syslogins table I only get the NT group.
Is there any way of retrieving the users that belong to this NT group.
Any help or suggestions on this would be greatly appreciated.
Hi folks, i am lookin for an option in the QA to take user input while executing a query. just like we do in SQLPLUS: SELECT * FROM dept WHERE deptno=$abc here it would ask me for the deptno. I guess it's a very handy feature and we could test our variables and calculations withing the procedures etc.
There is a large data table called emp_history on an SQL Server, which contains the employment history of each employee. The significant columns are as follows:
entry_id employee_id start_date position
where entry_id is the primary key and auto-increments.What I need, is to create a query to extract the employee_id and position, based on the most recent record (most recent start_date) for each employee.
I need to trigger an email to user when a work order due date is approaching 1 day prior to the due date. Also I need to trigger when a user adds, delete, or modify work order to check on work order due.
I would like to setup a query where the user is firstly prompted to input the information e.g. if they are trying to locate a particular person from a telephone database they would be prompted to enter the surname for that person.
Hi,I tried to create a simple view as followsCREATE VIEW V_ALL_USERTABLE_COLUMNSAS(SELECTOBJ.NAME as TableName,COL.NAME as ColName,TYP.NAME AS TYPEFROMSYSOBJECTS OBJ,SYSCOLUMNS COL,SYSTYPES TYPWHEREOBJ.TYPE = 'U'AND OBJ.ID = COL.IDAND COL.TYPE = TYP.TYPE)Combined with consistent naming conventions I will use this view toeasily find foreign keys; a laSELECT *FROM V_ALL_USERTABLE_COLUMNSWHERE ColName LIKE ('%user_id')There is something wrong with my view definition that I don't getthough; it doesn't return all the columns. I have a table with thefollowing definitionCREATE TABLE [dbo].[c_messages]([cid] [int] IDENTITY (1, 1) NOT NULL ,[touser_id] [int] NULL ,[tosession_id] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[fromuser_id] [int] NOT NULL ,[message] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOTNULL ,[message_read] [bit] NOT NULL ,[logout] [bit] NULL) ON [PRIMARY]GOThe problem is that the select I used to define the view doesn'treturn the touser_id column. I have sort of a sneaking suspicion thatthe problem has to do with joining syscolumns.type to systypes.type,but I don't know what to do instead (I'd really like to include thetype; it's useful if I ever changed the type of a primary key and wantto check that I also changed all the foreign keys).Any help would be appreciated!
what's the best way to set user vars in ssis from a query. I have some components that set a from and to date in a table. I'd like to select those values right into some user vars in the pkg.
Is there a way through T-SQL to run a store procedure and inside the store procedure, change the user, execute a select, and get back the original user that run the store procedure?
I am sure this has been asked a thousand times before in a thousand different ways, but here goes...
I am in need of the ability to prompt users for a From Date and To Date to insert into a WHERE clause in SQL queries. What is the easiest least painful way of accomplishing this? A great many of our customers utilized SQL Server Express or MSDE, so they do not have the full gambit of SQL tools handy. Believe me when I say that I have searched high and low across the internet for this solution. I was hoping I might be able to accomplish this through VB Script or J Script. Or, anything reasonable.
