Create READ ONLY Login Sql 6.5
Jun 8, 2000I 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.
ADVERTISEMENT
Set READ UNCOMMITTED (dirty Read) At Login.
Jul 23, 2005Is it possible to set READ UNCOMMITTED to a user connecting to an SQL2000 server instance? I understand this can be done via a front endapplication. But what I am looking to do is to assign this to aspecific user when they login to the server via any entry application.Can this be set with a trigger?
View 1 Replies View RelatedUsing Local Variable To Pass Login Name To CREATE LOGIN Script
Mar 19, 2008Dear 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
Create Variable To Store Fetched Name To Use Within BEGIN / END Statements To Create A Login
Mar 3, 2014I 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.
How Can I Read/create A File From T-sql?
Jun 2, 2004I need to create/read files from t-sql? Does anyone know how can I do it?
Thanks
How Can I Create Account With Read Only Access?
Feb 5, 2008
Hello,
We have SQL Server 2005 with 300+ databases in it. It was set up by the conractor. How can I create new account with read-only access to all databases? Is there any "quick" method of doing it (like in Sybase, it is very easy). I've created new login, but I do not see where can I set up the permissions properly. Thanks
Create And Read A Table From A Stored Procedure
Nov 25, 2001In a stored procedure, I am trying to create a table and then read it from within that SP or from another one (nestsed). When I run the below code (simplified version of my real SP), no records are returned even though I know that there is data. The table is created and records are inserted, I just cannot read the records from the SP or one that calls it with an EXEC.
Any ideas? My code is listed below. (When I get this to work, I plan to convert the code to manipulate a temporaty table).
Alter Procedure spWod_rptWoStatusSummary_9
As
BEGIN
CREATE TABLE tblStatusSummary (TckStaffCd_F Char(3), RecType Char(20))
INSERT INTO tblStatusSummary
SELECT TckStaffCd_F, RecType
END
BEGIN
Select * from tblStatusSummary
end
User Permissions - Read Only With Create View
Jan 11, 2008I need to create a new login with SELECT rights so the users can view all tables with no UPDATE, DELETE, OR INSERT rights. But this user needs to be able to CREATE VIEWS. I have assigned the user to the Public role and gone in and modified Securables for the Database to be able to CREATE VIEW. When I connect using my new user and try to create a view, I get the error message: CREATE VIEW permissions denied in database 'test01'.
Help Please
TMDavis
DB Design :: How To Create READ ONLY Database In Server
Oct 6, 2015Is it possible to create a database which design or data is not needed to update and i want to set to it as a READ ONLY. what are the steps to create such type of database?
View 3 Replies View RelatedSSIS Read Excel Rows And Create New Output
Feb 1, 2013I have an excel file with following data:
Agent State Exposure Insured Name
Rogers Inc MA 100,000 John Smith
SAN Group RI 200,000 Jim Morrison
SAN Group RI 100,000 Jimi Hendrix
123 Agency MA 300,000 Mickey Mouse
Rogers Inc MA 50,000 Mike Greenwell
I want to be able to read the file and create new excel files for each Agent listed. So for Example, the above file would create 3 separate files since there are 3 different Agents listed. Each Agent file would contain the same information from the original file. The name of the file would be somethign like AgentName.xls...So the SAN group file would have this:
Agent State Exposure Insured Name
Rogers Inc MA 100,000 John Smith
SAN Group RI 200,000 Jim Morrison
SAN Group RI 100,000 Jimi Hendrix
Is there a way to accomplish this in SSIS?
Create Format File With BCP To Read Fixed Width
Jan 22, 2008I'm using SSIS to do bulk inserts from fixed width files to about 20 tables in my SQL database.
The problem I'm running into is in creating Format Files for the bulk insert task to use. I've gotten the bcp command to create format files that will read csv files, but I can't seem to figure out how to get it to create one for fixed-width.
I know it can be done: http://msdn2.microsoft.com/en-us/library/ms191234.aspx
At the bottom (Section F) it shows an XML format file for reading a fixed-width file. When I manually create one of these to match one of my tables, the bulk insert worked fine.
Closest I've come is with this ( [] bracketed items are correct values, just censored here):
C:Program FilesMicrosoft SQL Server90ToolsBinn>bcp [database].[owner].[table] format
nul -c -f C:TableFMT.xml -x -S[Server] -U[Username] -P[Password]
My question is, what is the bcp command to create this sort of XML format file?
DB Engine :: Create Read-only Database From Production And Restore On Different Server
Jun 4, 2015We have a production 2012 R2 SQL server that is not part of a domain. I have a database called CSSDC. I need to backup this database and make the BAK file available to a user on a different SQL server. Security is a concern. In order for this user to restore the database, they will have to be in the fixed server role sysadmins. I know I am going to have to use the Move with replace for the filegroups for the restore. Once the database is restored it will have to be altered to read-only. I would also like to remove the existing security and only have this new user access the read-only database.
View 4 Replies View RelatedSQL Server 2008 :: Create Authentication Account With Read-write Access To Only 1 Table?
Apr 7, 2015How can I create a SQL authentication account with read-write access to only 1 table in a SQL database.
View 1 Replies View RelatedHow To Create A Login
May 20, 2008hi,
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
Create Login
Jun 2, 2008HI 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
Cannot Create Login
Apr 27, 2007This 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
Create Login...
Jan 21, 2008Hi 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.
How To Create A New Login?
Jan 31, 2008I 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 RelatedCreate New Login
Jan 15, 2008Hi 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!
Create Login From Master
Jan 25, 2008Is 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?
How To Create Login Script
Oct 6, 2007Hii 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
CREATE LOGIN Problem
Mar 14, 2006I 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]
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
How To Create My Validate Login SP IN Sql 2000 ?
Jan 24, 2006hi,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.
Create Login Names Without Spaces
Dec 31, 2004Afternoon,
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
SQL Express At Home - Can't Create Login
Sep 16, 2006Short 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.
T-SQL (SS2K8) :: How To Create Login For Client App
Jan 30, 2015I 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?
How To Create A Windows Authentication Login
Jan 16, 2007Is 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 RelatedFailed To Create User Login
Jul 17, 2006This 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?
Minium Permissions Necessary To Create A Login?
Mar 15, 2006What 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?
How To Create Login With Default Database
Aug 3, 2006Hi,
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,
Prolem With Create Login From Asymmetric Key
Feb 26, 2007Greetings...
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...
Create New Login Non Case Sensitive
Jan 12, 2007I 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