Create Login From Master

Jan 25, 2008

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?

View 3 Replies


ADVERTISEMENT

Using Local Variable To Pass Login Name To CREATE LOGIN Script

Mar 19, 2008

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


View 3 Replies View Related

Restoring Master To Get SQL Login Back

Jan 17, 2005

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 ?

Cheers

View 3 Replies View Related

Create Variable To Store Fetched Name To Use Within BEGIN / END Statements To Create A Login

Mar 3, 2014

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.

View 3 Replies View Related

Detach,attach Db And Login,user And Master Database

Jan 30, 2000

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?

Hope somebody can answer this.


Wislon

View 2 Replies View Related

Workaround CREATE MASTER KEY??

Apr 10, 2008

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?

Thanks

View 5 Replies View Related

How To Create Master - Child Packages?

Apr 11, 2008



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?


Thanks
Sam K

View 3 Replies View Related

CREATE TABLE In Wrong Database (Master)

Apr 19, 2006

I am using ASP.NET 1.1 and  MS SQL 2005 the folowing ODBC stringconnection
Driver={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 MyBase

I am using windows Authentication

what can be wrong ?

thank you

View 6 Replies View Related

CREATE TABLE In Wrong Database (Master)

Apr 19, 2006

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

View 13 Replies View Related

T-SQL (SS2K8) :: Combining Keys To Create A Master List?

Mar 5, 2014

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

Should look like:

22222600014
22222285437
22222490640
22222066128
22221111111
22222222222

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.

View 2 Replies View Related

SQL Server Admin 2014 :: Create A Master Key In The Database

Jun 15, 2015

in my environment I am running the SQL Server agent job.i am getting below message.

create a master key in the database or open a master key in the session before performing this operation” error 

View 5 Replies View Related

How To Create A Minimal User In DB With No Access To View Master DB

Sep 11, 2007

Hi,
We are using SQL Server 2005 Management Studio.

I created a Minimal User in an application DB. The user will access tables through stored procedures.

I do not want this user to view any other objects including objects in the Master DB.

I can prevent the minimal user from viewing objects from our application DB.

How do you prevent the minimal user from viewing objects in the Master DB?

Thanks.

Tim.

View 7 Replies View Related

Cant Create New Database / CREATE DATABASE Permission Denied In Database Master (error 262)

Oct 2, 2007

 
 I am using SQL express and Visual web developer on windows Vista.
When I try to create a new database the following message appears.
 
CREATE DATABASE permission denied in database master (error 262)
I log on to my computer as an administrator.
Help appreciated
 Prontonet
 
 
 

View 4 Replies View Related

How To Create A Login

May 20, 2008

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

View 7 Replies View Related

Create Login

Jun 2, 2008

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

View 7 Replies View Related

Cannot Create Login

Apr 27, 2007

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

View 4 Replies View Related

Create Login...

Jan 21, 2008

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.

View 2 Replies View Related

How To Create A New Login?

Jan 31, 2008

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 Related

Create New Login

Jan 15, 2008

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!

View 7 Replies View Related

Create READ ONLY Login Sql 6.5

Jun 8, 2000

I 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.

View 3 Replies View Related

How To Create Login Script

Oct 6, 2007

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


View 6 Replies View Related

CREATE LOGIN Problem

Mar 14, 2006

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]

View 6 Replies View Related

Create Login Issues

Mar 17, 2008



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

View 1 Replies View Related

How To Create My Validate Login SP IN Sql 2000 ?

Jan 24, 2006

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.

View 3 Replies View Related

Create Login Names Without Spaces

Dec 31, 2004

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

View 2 Replies View Related

SQL Express At Home - Can't Create Login

Sep 16, 2006

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.

View 5 Replies View Related

T-SQL (SS2K8) :: How To Create Login For Client App

Jan 30, 2015

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?

View 1 Replies View Related

How To Create A Windows Authentication Login

Jan 16, 2007

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 Related

Failed To Create User Login

Jul 17, 2006

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

------------------------------
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?

View 7 Replies View Related

Minium Permissions Necessary To Create A Login?

Mar 15, 2006

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?

View 1 Replies View Related

How To Create Login With Default Database

Aug 3, 2006

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,

View 6 Replies View Related

Prolem With Create Login From Asymmetric Key

Feb 26, 2007

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...

View 6 Replies View Related

Create New Login Non Case Sensitive

Jan 12, 2007

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

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved