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'
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?
Hi Everyone, I'm hoping someone can help me. We've had a SQL login deleted (special application one) and I understand the best way I can get it back, is to restore the Master database. Is this true ? All the manuals are working on the fact that Master has become corrupt & that it should be rebuilt before restoring. Ours is not corrupt, so am I ok to skip rebuilding it first ?
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.
I have similar question like Tim asked but from a different angle. I wish I pose my question here too anybody can help answer this.
I tried to use detach and attach method to do backup of the user database (not master, maybe we can do master too) on another server, but it looks the login and permission credential would not go together. I come to see different kind of message I tried to login as non-sa user after I attatch the database on different machine. I would be denied for SQL timeout. It looks like loginand user dose not exist in new server because I did not move master database too. But even if I recreate the login when I tried to associate user with login I will see a message saying user or role already exist in database. If I go back to check the user it did not show up on enterprize manager on that database.
So my question would be 1). how to move login and user together with user database. 2) do we have to move master database where the login infor. reside 3). if so how to move master database from one server to another when other server already have master database exist?
At some point in time, when I release my code from developemnt to production, somebody will onvoke the SQL Script containg my certificates and symettric keys based on my master key. Unfortunately this seems a bit of a weekness as my SQL SCRIPT contains the CREATE MASTER KEY ENCRYPTION BY PASSWORD stement which has the password itself in clear. (script gets invoked from a command line in a batch script which is all under documeny mangement control). Obviously I would not like my password to be be in clear anywhere - i.e. not in document control nor viewable from whoever invokes the script. What is best pracrice to adopt on this? - encrypt the script file?
I have around 10 packages for dim and fact Table load. Using deployment Utility i create setup and put the packages in to test environment. Now my requirement is call all the packages in certain order from single package. Using Execute Package Task i could call only one package. how to achive this in deployment?
I am using ASP.NET and a normal ODBC stringconnectionDriver={SQL Server};Server=(local);MyBase;Uid=;Pwd=;Trusted_Co nnection=;when trying to CREATE a TABLE (with vb.net code) I get an error because the TABLE are written in Master !! and not in MyBasei am using windows authenticationwhat can be wrong ?thank you
I am struggling trying to clean some data and identify duplicate records. I used fuzzy grouping in SSIS and provided back a series of groups. The issue is some of the individual records can appear in multiple groups (so in reality the groups should be combined). This is best explained with an example:
Original Data key1 key2 647942600014 647942285437 2324662490640 2324662285437 2324662066128 2222 2285437 2222 1111111 9999 1111111 9999 2222222
I only choose 2222 as the surviving key because it was the smallest number. I really do not care which number remains as long as it is the same across.
I tried playing with self joins between the tables but have had no success.
I am using Sql Server 2008 and the number of records could 500K to 1MM.
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
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
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 !!!
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.
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]
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?
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 ?
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.
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
This 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
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."
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')