Hi there!
I'm trying to create an account like this
CREATE LOGIN name WITH PASSWORD = 'password';
so, here is what i'm trying to do. I want to give it the server role of 'sysadmin'
and user mapping of some databases.
Can someone help me out on this one?!?
Thank you!! it is very much appreciated!
Dear all;
I'm trying to use a local variable @NEW_LOGIN_CODE to pass LOGIN NAME to CREATE LOGIN script as follows:
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
declare
@NEW_LOGIN_CODE varchar(255),
@NEW_LOGIN_PASSWORD varchar(255);
begin
SET @NEW_LOGIN_CODE = 'any_login';
SET @NEW_LOGIN_PASSWORD = 'AnyPassword';
CREATE LOGIN @NEW_LOGIN_CODE WITH PASSWORD @NEW_LOGIN_PASSWORD;
end
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
the script will not work unliss I provided a hard coded login code and password as follows:
CREATE LOGIN ANY_LOGIN WITH PASSWORD 'AnyPassword'
what should I do to make the CREATE LOGIN script accept local variables as parameters?
Thanks
I created a cursor that moves through a table to retrieve a user's name.When I open this cursor, I create a variable to store the fetched name to use within the BEGIN/END statements to create a login, user, and role.
I'm getting an 'incorrect syntax' error at the variable. For example ..
CREATE LOGIN @NAME WITH PASSWORD 'password'
I've done a bit of research online and found that you cannot use variables to create logins and the like. One person suggested a stored procedure or dynamic SQL, whereas another pointed out that you shouldn't use a stored procedure and dynamic SQL is best.
hi,
i tried to create a sql authenticated login by right click security,new login and follow the steps needed but once i finish creating, i could not login using the account. Why is it so? Can help me? Let my user name = " pe " and my password= " 123456 ". Help me. Thanks
HI Gurus,
When ia m trying to create
CREATE LOGIN [ADVWORKSfogisu] FROM WINDOWS;
thrus error as
Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'LOGIN'.
can some one pls help me in this..
Thanks,
ServerTeam
This error message :
Cannot find the certificate 'AccountingCorp', because it does not exist or you do not have permission.
This Code
USE master
CREATE LOGIN Dave
FROM certificate AccountingCorp;
GO
******************
AccountingCorp was created wiht this code:
USE Frontier_equipment;
CREATE CERTIFICATE AccountingCorp
WITH SUBJECT = 'Frontier Accounting Records',
EXPIRY_DATE = '10/31/2009';
GO
rkj
Hi all
As you know in [Enterprise Manager] , in the left pane and in SecurityLogin section we can create Logings for users by just
Right click in the Right pane and select [New Login...]
But i couldn't understand one thing !!! when i create a new login with specifying ONLY:
1) Login Name
2) Sql Server Authentication Mode
and without specifying Server Roles and Database Access, the created Login has Default Access to System Databases such as
Master,MSDB and TempDB databases !!! is there an way to revoke this Access permission from Created Login? if so how?
By the way i have anotehr Question. when you are assigning Database Role to a login there are some Fixed Database Roles such as db_owner
but we have also db_DataReader and db_DenyDataReader i want to know
why do we have both db_DataReader and db_DenyDataReader roles at the same time ??? nothing only curious !!!
Thanks in advance.
Kind Regards.
I need to add a user to the server. I have heard that there is a wizard. But I can't find how tolaunch it. How do I add/remove users?
View 5 Replies View RelatedI use Sql 6.5 and I want to create a login for a user with
READ ONLY privileges on the tables in the databases. How do I do this?
Thanks in advance.
Is there any way I can create a login for a database while the database context is master or not the particular database.
I am trying to do something like this.......
USE [master]
GO
DECLARE @STRSQL VARCHAR(2000),
@DATABASE_NAME VARCHAR(50),
@DB_PATH VARCHAR(200),
@DB_USER VARCHAR(25)
SET @DATABASE_NAME='TDB4x'
SET @DB_PATH='D:Database'
SET @DB_USER='TDB4Test'
DECLARE @SQL_DB varchar(20),
@STRSQL1 varchar(500),
@STRSQL2 varchar(500),
@STRSQL3 varchar(500),
@STRSQL4 varchar(500)
SET @SQL_DB='USE '+@DATABASE_NAME
SET @STRSQL1=(' IF NOT EXISTS (SELECT *
FROM sys.server_principals
WHERE name='''+@DB_USER+''')')
SET @STRSQL2=(' BEGIN
CREATE LOGIN '+@DB_USER+'
WITH PASSWORD=N'''+@DB_USER+''',
DEFAULT_DATABASE=['+@DATABASE_NAME+'],
DEFAULT_LANGUAGE=[us_english],
CHECK_EXPIRATION=OFF,
CHECK_POLICY=OFF
END')
SET @STRSQL3=(' EXEC sys.sp_addsrvrolemember @loginame = N'''+@DB_USER+''', @rolename = N''sysadmin''')
SET @STRSQL4=(' ALTER LOGIN ['+@DB_USER+'] DISABLE')
EXEC (@SQL_DB+@STRSQL1+@STRSQL2+@STRSQL3+@STRSQL4)
The login is successfully created, but I cannot find the user in the specific database. Is there any other way this can be accomplished?
Hii experts,
Im very new to sqlserver world.pls excuse me if my question is very silly.
Im using sqlserver 2000. can anyone let me know how to script all the available server logins and their permissions.I want to run this script file on another server to recreate the same logins on that server.
Also tell me if i can create a DTS package for transferring logins and if yes,tell me how to do it.
Thanks in advance
Regards
Arvind L
I know this is a really stupid question but I can't figure out how to make this work. It just doesn't seem to want variables. What is the proper syntax?
CREATE LOGIN [@GUserName] FROM WINDOWS WITH DEFAULT_DATABASE=@DBName, DEFAULT_LANGUAGE=[@LoginLanguage]
Hi,
I have SQL Server Express 2005 version 9.0.3042 (SP2), but it does not seem to recognize the "create login" statement:
create login sampleuser
GO
Gives me:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'sampleuser'.
After searching online for this error, the solution I've seen with other people running into the same problem is that they were trying to access a SQL server 2000 instance, instead of an SQL server 2005 instance, but in my case I've installed SQL Express 2005 SP2, shouldn't it work on my version? And how do I make work or how do I connect to an SQL server 2005 instance?
Thank you!
-Yannick
hi,all:
I'm new to Sql 2000,now I have a login asp.net page and I used the sql 2000 database.
my login page included a user id and password need user inputed. if the user input the correct userid
and password ,IE will transfer to main page,or there will show eorror message in login page.
my SP like this:
CREATE PROCEDURE dbo.Usp_Accounts_ValidateLogin @userid char(4) , @EncPassword binary AS if (select count(*) from hhmxUserData where Userid=@userid and UserPWD=@EncPassword) >0
return 1
else
return 0GOmy asp.net code like this:
dim result As Integer
dim rowsAffected as integer
myConnection.Open() Dim command As SqlCommand = BuildIntCommand(storedProcName, parameters) rowsAffected = command.ExecuteNonQuery result = CInt(command.Parameters("ReturnValue").Value) myConnection.Close()
Return result
I test it in sql 2000,it's ok.but when I performed it and retrieve the "returnValue", it still return 0 .
so how can I create my correct SP ?
thanks so much.
Afternoon,
I've got this security database that needs to create a login name using first initial and 7 characters of the lastname. No problem.
I wrote this:
select
CASE WHEN LastName like '%.%' then
LEFT(FirstName, 1)+left(replace(LastName,'.',''),7)
else
LEFT(FirstName, 1)+ LEFT(LastName,7)
end as UserID
from Security.dbo.tblUserInformation
The replace and case functions I used to get rid of the '.' Like the name St. Clair now I get "GSt Clai" I need it to also get rid of the space but am stuck. Any thoughts would be appreciated.
Thanks
Laura
Short version - how can I create a server login for a remotely connected machine on a simple 2 computer XP home network connected via router?
Long version - I'm doing an Access-SQL Migration. I used the migration assistant (which creates DSN-less linked tables) to convert the data to SQL Server, and now I'm at the point of making all of the modifications to my Access front end so that the two play nice.
I have SQL Express running on my laptop. I would like to do the Access work on my desktop because a) I can work faster with the fullsize keyboard and b) the install at my client's location will include users hitting the database over their network and I'd like to simulate that while I'm making the modifications.
My Access front-end works 100% fine on my laptop. The Access front end will NOT connect when I run it on my desktop.
My guess is that this is due to not having a server login or DB user associated with the account on my desktop machine. In view of that, I'm trying to create those items on my server.
So, I open SSMS on the laptop and try to create a Server login. Under "Login Name:" I enter "DesktopAccountName" and it fails saying "Windows NT user or group 'DesktopAccountName' not found. Check the name again."
If I click the "Search" button next to "Login Name:" The only "Location" that I'm given is "LAPTOP" and that's greyed out preventing me from changing it.
Both machines are running XP home. My network is simply called "Home" if that means anything.
I have a client application written from C#/winforms app that needs access SQL DB on SQL server 2008 R2 developer. So I need to create a Login for this app with a user/password. But when i created it and put it on the client's connection string, the DB could not be accessed by the client windows forms app and the error was "Login failed for User App1".
Then I tried to login to SQL server from SSMS using the credentials of App1 but it also gave same error "Login failed for user App1". I have following the following steps of creation of the login for App1. i could login from "sa" account and other Admin accounts but not from "App1" account
1. created a server level Login
2. created a DB level user
3. created a Role (a DB role), i even tried creating an App Role
4. given permission to execute stored procedures that handle login
But didn't work. Maybe i made some mistakes in those step. So, how to create an account for user "App1" so it can be accessed by the client windows app and also from SSMS? i would prefer to use account creation from SSMS GUI.
How do i install SQL server 2008R2 in local machine with network accessibility features?
Is it possible to create a windows authentication login for each of the computers on my network that will connect to sql server. I am developing a program that will be installed on multiple computers and we do not want to actually have to log in to sql server since we will have to log in to the server before opening the program and we dont want to have 2 logins. I would like to use windows authentication so that I do not have to program multiple connection strings in my program so that if more than 1 person accesses the database at the same time it will not cause a problem with using the same login. Is this possible and if so how would I go about setting up the logins. I am using SQL Server 2005 and XP SP2
View 8 Replies View RelatedThis is the error message i keep getting when following the SQL Server tutorial on how to make a new user;
TITLE: Microsoft SQL Server Management Studio Express
------------------------------
Create failed for Login 'employee'. (Microsoft.SqlServer.Express.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+Login&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)
------------------------------
The MUST_CHANGE option is not supported by this version of Microsoft Windows. (Microsoft SQL Server, Error: 15195)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.2047&EvtSrc=MSSQLServer&EvtID=15195&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
Why is that? Im running XP, do i need to be on 2000 Server or something?
What are the minimum permissions necessary to use CREATE LOGIN?
I'm using db_accessAdmin and db_securityAdmin and db_owner. The user as access to the database.
It still says I can't create the login. What am I missing?
Hi,
I can't performe this:
use databaseX
CREATE LOGIN [abc]
WITH PASSWORD ='xxxxxxxxxxxxx',
DEFAULT_DATABASE = db_name()
or
DEFAULT_DATABASE = [ db_name() ] as well
I know that
DEFAULT_DATABASE = databaseX
works, but how can I create login with default database with db_name() ?
thanks,
Greetings...
I'm trying to create assembly with "PERMISSION_SET = UNSAFE".
For that I've signed assembly's .dll and installed root certificate to €œTrusted Root Certificate Authority.€?: http://www.sqljunkies.com/WebLog/ktegels/articles/SigningSQLCLRAssemblies.aspx
now I'm trying to create login from asymmetric key:
USE master
GO
CREATE ASYMMETRIC KEY SQLCLRTestKey
FROM EXECUTABLE FILE = 'C:Documents and SettingsAll UsersDocumentshunterStoredProcedures.dll'
CREATE LOGIN SQLCLRTestLogin
FROM ASYMMETRIC KEY SQLCLRTestKey
but I'm receiving error: "Cannot find the asymmetric key 'SQLCLRTestKey', because it does not exist or you do not have permission."
What's wrong?
Best regards...
I am trying to create a new login on a database, and I want that username and password NOT to be case sensitive. Do I have to set this at the server level or can I set this in the SQL script?
Here is the script I am currently using...
IF EXISTS (SELECT * FROM dbo.sysusers WHERE [name] = 'UName')
BEGIN
EXEC sp_dropuser 'UserName'
EXEC sp_droplogin 'UserName'
END
EXEC sp_addlogin 'UserName', 'Password', 'Database'
GO
EXEC sp_adduser 'UserName', 'Password', 'db_datareader'
GO
Any help would be appreciated...
David
Hi all.
M trying to create a logon form
I had something in mind but i can work it out yet
When the user wants to login into the application the value of textbox must be compared with the datafield in the sql server.
And then i want a messagebox to show up.
I created a table in sql server with 2 fields in it , User and Password.
I hope anyone could help me tnx already
Hi all,
I made a Windows forms app using vb.net (VS2005) and SQL server 2005 Express Edition (to which I'm new). During the creation of the app I used windows authentication in the connection string. Now I want to secure the app, meaning I want the SQL Server database not to be accessed without a username/password. I guess the logical thing is to create a login form, providing the username and password so it can be used for connecting to the database. I thought this would be a standard (thus easy) thing to do, but no forum or article yet showed me a way to accomplish this in an understandable way. I also want to deploy this app using click once technology. I need to distribute this app on a cd/dvd.
If possible, can anyone give me a step by step scenario for this? Many thanks in advance
(ps: please let me know if this should be in another thread)
Hello,
Could anybody explain: is following supposed to work:
CREATE LOGIN test
WITH PASSWORD=0xF1E9E5CA9A79F7B5D883FA4D9680ED1D4D9AAB12 HASHED,
CHECK_POLICY = OFF;
GO
where
0xF1E9E5CA9A79F7B5D883FA4D9680ED1D4D9AAB12
result of
SELECT HASHBYTES ('SHA1','$Test123#')
execution of 'create login' results in following error
Msg 15021, Level 16, State 2, Line 1
Invalid value given for parameter PASSWORD. Specify a valid parameter value.
Interesting enough is that if I use MD5 the hash is 8 bytes shorter and create login works but actual login procedure does not. And if I just use plain text password without HASHED everything works.
This is on sqlexpress 2005 sp2. Is this specific to express version?
I've noticed that realatively old SQL BO 2005 states that hashed password should be passed as literal in single quotations ' *** ' (and it does not work) and msdn online states that in case of HASHED hexadecimal value should be passed without single quotations, anyway does not work either ...
Thanks,
--Alex
Hey guys,
I'm having a problem making a new login inside the sql management studio, the problem is, when i create a new login, i selected SQL Authentication, then type a password, then uncheck Enforce password policy.
i then select the database i want the login to be associated with, but once i click ok i get this exception:
Create failed for Login ''. (Microsoft.SqlServer.Smo)
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
"An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Add a name or single space as the alias name. (Microsoft SQL Server, Error: 1038)
I even tried with Northwind and a brand new database with a table and 2 columns but it's the same story every time.
Any ideas?
Thanks a bunch
I am using a SQL server 2008 database.I am working on a windows user.I noticed when I create a user in a database for a login, even that login does not exist in the database, the user is still successful created.I suppose it should error out.
Or I miss something?
I am using this script:
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'mydomainmyuser')
CREATE USER [mydomainmyuser] FOR LOGIN [mydomainmyuser] WITH DEFAULT_SCHEMA=[dbo]
GO
Need to create same login on 100s of servers using local admin login create on the OS. (for xp_cmdshell proxy setup)
Normal command works fine but I meed to make <svr_name> a variable.
CREATE LOGIN [<svr_name>DBALocalUser] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
GO
When I try various efforts to pass in the <svr_name> portion it fails every time.
declare @cmd varchar(200),
declare @username varchar(50)
set @username = 'DBALocalUser'
set @cmd = ' CREATE LOGIN ['+@@servername+''+@username'] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]'
[Code] .....
Hi gurus i am new to sql server.... so plz help me out ... i need script to create new database user and login.i know how to create by enterprise manager but have no idea to create it by QA.
please help me out
thanks in advance
shekhar
I'm trying to create a login in the default domain. I know I can pullthis information from xp_loginconfig, but don't see how I can use it inthe context of sp_grantlogin.For example, pull the domain the user is currently logged in on andinsert it into the sp_grantlogin script. Has anyone ever done this inthe past?
View 2 Replies View RelatedHi,
I am using SQL Server 2000. But i am not able to see the BuiltinAdministrator account in the logins list in Enterprise Manager but by default the owner of the system gets access to all databases and objects in the system. I would like to restrict that by restricting the access of BuiltInAdministrator account but it is not visible. Is there any option to be enabled to make that visible?
Could you please help me in this issue..
Thanks.
Hi,
Please direct me to the right forum if this is not the right one.
Upon installation of SQL Server 2005, I opened the SQL Server Management Studio, connected using Windows Authentication and went to the security node in the tree. I created a log in called "TestLogin". I restarted my server just to make sure everything took and when I tried to log in using TestLogin via SQL Server Authentication, I get the below error. I have mixed mode enabled as well as named pipes. I am not sure what the issue is or what to try next. I even get the same error when trying to use "sa".
Thanks,
- Joel
TITLE: Connect to Server
------------------------------
Cannot connect to TestServer.
------------------------------
ADDITIONAL INFORMATION:
A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.) (Microsoft SQL Server, Error: 233)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=233&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------