Query For Db User Creation In MS SQL
Jan 24, 2007
HI everybody...
Can anyone help me to know the
Query to Create SQL database Users in MS SQL
through the query engine of MS SQL Server Management Studio..
I am struggling with the same and I tried the one
create user <user_name> for login <login_name>.
But it seems that it will create the windows user itself and not the sql user.
I want a query similar to create user <user_name> identified by <password> which is used in ORACLE.
Please help me in this situation.
Thanks in advance
dance
View 1 Replies
ADVERTISEMENT
Apr 10, 2008
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 .
View 6 Replies
View Related
Mar 28, 2008
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.
View 3 Replies
View Related
Mar 28, 2008
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.
View 6 Replies
View Related
May 4, 2007
Hi,
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?
Thanks in advance for your help
View 4 Replies
View Related
Mar 27, 2006
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.
View 8 Replies
View Related
Nov 12, 2014
Query to find the date/time when a database schema was created and who created it.
View 2 Replies
View Related
Sep 29, 2006
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)
);
View 1 Replies
View Related
Dec 6, 2013
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?
View 3 Replies
View Related
Oct 8, 2014
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".
View 0 Replies
View Related
Dec 7, 2006
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
View 3 Replies
View Related
Mar 26, 2004
I have a table that keeps a log of al user inquiries. The business wants to a report that shows the last ten inquiries each user has made:
A sample result would look like:
User, Date, InquiryID
George, 10/1/2003, 1000
George, 10/2/2003, 1001
George, 10/3/2003, 1002
George, 10/4/2003, 1003
George, 10/5/2003, 1004
George, 10/6/2003, 1005
George, 10/7/2003, 1006
George, 10/8/2003, 1007
George, 10/9/2003, 1008
George, 10/10/2003, 1009
Harold, 11/1/2003, 1010
Harold, 11/2/2003, 1011
Harold, 11/3/2003, 1012
Harold, 11/4/2003, 1013
Harold, 11/5/2003, 1014
Harold, 11/6/2003, 1015
Harold, 11/7/2003, 1016
Harold, 11/8/2003, 1017
Harold, 11/9/2003, 1018
Harold, 11/10/2003, 1019
DDL:
CREATE TABLE UserInquiries (
InqiuryID INT IDENTITY(1,1),
UserName Varchar(10),
InquiryDate datetime)
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.
Regards,
hmscott
View 1 Replies
View Related
May 22, 2008
Hi,
Please give the T-SQL script for this ? Thanks
Shanth
View 4 Replies
View Related
Nov 24, 2004
Hi,
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 know this is wrong but how else can I do it?
Thanks
View 3 Replies
View Related
Jan 7, 2007
Hey!
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.
So, I have a table like this:
ChangeIDUserIDLoggedInDateTime
7107/30/06 12:30PM
6117/30/0612:20PM
5107/29/06 11:00AM
4117/29/06 10:30AM
3205/03/06 6:30PM
2215/03/065:30PM
1205/01/06 9:30AM
0215/01/06 9:15AM
And I want to make it into this table:
UserIDLoggedInDateLoggedOutDateDuration
17/29/06 10:30AM7/29/06 11:00AM30
17/30/06 12:20PM7/30/06 12:30PM10
25/01/06 9:15AM5/01/06 9:30AM15
25/03/065:30PM5/03/06 6:30PM60
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
View 1 Replies
View Related
Nov 30, 2006
I m new to SQL Server. Before this I was using access. In access one can have user inputs easily .. for example
where receivedate=[enter date]
how I can get userinputs in SQL server Query....
Forgive me if it is a dumb question
View 6 Replies
View Related
Feb 13, 2007
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"
Thanks..
View 8 Replies
View Related
May 13, 2007
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!!
View 1 Replies
View Related
Jan 2, 2004
Is there a way to create a query that will return all user tables inside a sql db
Thanx
View 4 Replies
View Related
Sep 17, 2005
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?
View 1 Replies
View Related
Jun 23, 2000
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?
TIA
Bob Morrow - Tennessee General Assembly
View 2 Replies
View Related
Feb 20, 2000
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.
Thanks,
Karl
View 1 Replies
View Related
Aug 23, 2001
Hi Everybody,
For a particular user, how to find what are all the tables he has been given the grant permission,etc., thru' query analyzer.
Can you please tell me, is there any command or system stored procedure is available to find this problem.
regards,
John
View 1 Replies
View Related
Jul 15, 2004
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.
Is there a way, or any substitute?
Howdy!
View 14 Replies
View Related
Sep 21, 2006
Anyone have a tsql query that will give me a listing of database roles and their users already put together?
View 1 Replies
View Related
Jul 16, 2013
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.
View 6 Replies
View Related
Aug 6, 2013
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.
Not sure how to query it in SQL.
Due Date = NeedByDate
user = AssignedTo
View 1 Replies
View Related
Jan 11, 2006
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.
View 4 Replies
View Related
Jul 20, 2005
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!
View 1 Replies
View Related
Mar 1, 2008
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.
View 1 Replies
View Related
Jul 27, 2007
Hello:
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?
Thanks
View 4 Replies
View Related
May 20, 2008
Hello Everybody,
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.
Thanks in advance for your time and patience,
Rocky Mountain Tech
View 1 Replies
View Related
Sep 22, 2006
I currently have a form that has different options in a list box such as:Status Codes
View 3 Replies
View Related
