I'm not in our IT section but am responsible for the running of a finance application on SQL 2000.
I want a Read only user set-up on the SQL DB so that we can use Crystal and Access to look at the tables.
IT have some concerns with this as they say it by passes all security.
Are they spinning me a line?? Surely read oonly is read only? Can a user be restricted to a specific database as there are a number running on the same server.
Hi, I wrote a report builder that creates a CSV file but I can't redirect to the file to view it. ERROR Cannot find the Server!. This works fine on the dev machine but deploy it and it does not.
Dim Conn As New SqlConnection(ConfigurationSettings.AppSettings("ConStr")) Dim Cm As SqlCommand Dim dr As SqlDataReader Dim FileName As String = Guid.NewGuid.ToString & ".csv" Dim FilePath As String = Server.MapPath("") & "CSVFiles" & FileName
Dim fs As FileStream = New FileStream(FilePath, FileMode.Create, FileAccess.Write) Dim sw As StreamWriter = New StreamWriter(fs) Dim Line As String Dim lItem As System.Xml.XmlElement Dim i As Int16 If ValidatePage() Then Cm = New SqlCommand(BuildQuery, Conn) Conn.Open() dr = Cm.ExecuteReader()
Dim lXmlDoc As New System.Xml.XmlDocument() lXmlDoc.LoadXml(txtHXml.InnerText)
For Each lItem In lXmlDoc.DocumentElement.SelectSingleNode("SELECT").ChildNodes Select Case lItem.InnerText Case "chkPRId" Line &= "PR Id," Case "chkDateIssued" Line &= "Date Issued," Case "chkOriginator" Line &= "Originator," Case "chkBuyer" Line &= "Buyer," Case "chkVendor" Line &= "Vendor," Case "chkCostCode" Line &= "Cost Code," Case "chkOracleRef" Line &= "Oracle Reference," Case "chkTotal" Line &= "Total," End Select Next Line = Line.Substring(0, Line.Length - 1) sw.WriteLine(Line)
Line = "" While dr.Read For i = 0 To dr.FieldCount - 1 Line = Line & dr(i) & "," Next sw.WriteLine(Line) Line = "" End While
dr.Close() Conn.Close() sw.Close() fs.Close() Response.Redirect(FilePath) End If ResetPage() PopulateListBoxFromXML()
I would like to limit the role of an user In Visual Studio only to assign roles to other users for the cubes. Other than that the user should not be able to create / delete the exisiting cubes or dimensions.
I have read only user for which I need to give permissions so that he can see the view definition from the management studio by right click - script view as - alter view - copy to clipboard.
Is there any access privilege/rights that can accomplish this.
I have a database instance which of course I can connect to and edit data. I would like to set up other users for this database that can just as easily connect but that only read data?
I currently have a sql server 2000 sp4 instance with a read-only db on it. We get tlogs shipped from an outside vendor and keep a copy of the production database in house for reporting purposes. The database is in read-only mode because we apply new tlogs daily. Recently we did a full recover and applied 2 tlogs.
The issue: There is a user YYY in the database and at the server level, but they aren't linked. So I do what I usually do and run the following command:
Server: Msg 3906, Level 16, State 1, Procedure sp_change_users_login, Line 109 Could not run BEGIN TRANSACTION in database 'ZZZ' because the database is read-only.
Now this error makes sense and I understand why, so my question is how do I fix the users not being linked?
I'm sure this is a really easy one but I want to create a user with read only access on a sql 2000 database using SQL authentication.
I'm building a little pilot DB system and I have a trainee developer who I want to built me some queries for crystal reports. But I dont want them to be able to amend/delete data or tables etc.
I've created the user but how to I set it to read only. Also will the user still have read only rights if the user has access to the database via enterprize manager/Query analysier.
I 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'.
Can a user be granted the ability to create stored procedures with read only capabilities? I wouldn't mind be able to UPDATE but simply need to read only.
What role or system privilege do I need to grant to a user if he need to read the data from a table which is in a link server object? where I can find the document about these commands.
I would like to ask if there will be a write and concurrency issue if i would create an online application with just one user connecting to the database, just like most open source php/mysql that can be downloaded
i.e Setup 1. I will grant a single user that will connect to a database and will be set to a config.inc.php file and then create a table users (userid, username, password) and this table will be used for the application authentication and access control.
Setup 2. or is it much better to grant users that will connect to the database and have the table users for access control of the application?
to further illustrate my query:
Setup 1 would be: ###################Table: Users user a@ipadd --> db1 --> userid mary --> myOnlineApp user a@ipadd --> db1 --> userid john --> myOnlineApp user a@ipadd --> db1 --> userid paul --> myOnlineApp
and Setup 2 would be: #####################Table: Users user mary@ipadd --> db1 --> userid mary --> myOnlineApp user john@ipadd --> db1 --> userid john --> myOnlineApp user paul@ipadd --> db1 --> userid paul --> myOnlineApp
will Setup 1 be enough if I will use SQL Server as database?
or Setup 2 is better user/database architecture?
This application will be online enrollment for a school with 16,000 students
very much appreciated for the feedbacks and suggestions
I am trying to create a database setup script that will work on various OS languages. One of the things the set up script has to do is grant permissions to the NETWORK SERVICE account. This is simple when we are running on the English version of Windows with the command:
Code Block CREATE USER [NetworkService] WITH LOGIN [NT AUTHORITYNETWORK SERVICE];
Unfortunately, if you run this on a French language version of Windows, the login is called something like [NT AUTORITESERVICE RUSSEAU] or something similar. This causes the script to fail.
I think I should be able to get around this by using the well known SID for this account (S-1-5-20) and SUSER_SNAME() to resolve to the name for the account in the local language.
Here is the code I've written:
Code Block SET @user = quotename(SUSER_SNAME(0x010100000000000514000000)); SELECT @user; CREATE USER [NetworkService] FOR LOGIN @user;
Everything seems to be working up to the last line. The SELECT @user line returns [NT AUTHORITYNETWORK SERVICE] on my English language server as expected. Unfortunately, the last line results in a syntax error.
This may be a really dumb question, but I'm not really a DB developer, so I'm stumped. Any ideas on how I could make this work?
Here is My requirement, I'm not sure if this is possible. Creating table called master like col1, col2 col3, col4 , col5 ...Where Col1, col2 are updatable - this can be done easily
Col3, col4 are columns in another table but these can be just a read only ?? Is this possible ? this is possible with View but not friendly with share point CRUD...Col 5 is a computed column of col 2 and col5 ? if above step can be done then sure this can be done I guess.
I'm trying to create a user from a powerbuilder application through powerscript. I'm trying to create first the login using sp_addlogin but I'm getting the following error [microsoft][odbc for sql server]the procedure sp_addlogin cannot be executed in transaction.
i am a newbie in SQL Server. i want to add a new user to my database, whom i want to grant only insert, update, delete, select commands and some procedures/triggers. i need to log on to the database using that user from my asp.net application. i might have several users to log no to the system and for each user i need a set of tables in the databse. i want to wirte the procedures /triggers once for all the users in the database where they can access. i can write the procs under dbo schema, but i what about the tables?
currently i am using SQL Express to test my database. i have Management Studio Express installed on my PC.
When I declare a cursor,I use a variable to replace the sql statement:DECLARE rs CURSOR LOCAL FAST_FORWARD FOR@sqlPlanBut it is not true.Who can correct for me.Another question is :How to execute a sql statement state by a variable "@sqlPlan" andinsert the result to a table "@FeatRequestStatus"?I am a new hand of sql programming.Thank you very much for your help
I have noticed that some folks create a user to own a schema, with both having the same name (ex: userowner = pfm schema = pfm) and others make dbo the owner for all their schemas. From what I can tell, it doesn't really matter; what does matter is if your database users are granted access to the schema. Since most users only have public rights and dbo has database owner rights, is there any particular reasoning to use one scenario over the other?
Hello - I am using the generic aspnetdb membership database (aspnet_regsql.exe) which I am currently connecting to as a sysadmin user from my website (via web.config). Before going live with my first website I now want to create a sql user for the website to login with, with the minimum permissions required to run the website - the reason for this is to make the db more secure. I have created a new sql user and am now looking at what acces they should have. I am OK giving them the required access to the NEW views, sprocs and tables I have created to link in with the generic aspnetdb database etc... however how do I know what access they require to all the BUILT-IN tables, sprocs, views etc. Also, when I am viewing Securables it also brings up all the MASTER DB sprocs, etc.. - will the user need access to these? Should I add the new user to a database role - which one? (currently I have not given the new user a database role) This is the first time I will have created a sql user for an asp.net website so any help greatly appreciated. Cheers Marco
I am setting up a database for the first time using SQL Server Express.
I have managed to create a database and create a table with data etc, all straight forward.
The problem I am having is when I come to creating a user for the database. I have the following users in there already, which I am guessing are put in by default:
dbo
guest
INFORMATION_SCHEMA
sys
When I try creating a new user via this screen I get the following error:
TITLE: Microsoft SQL Server Management Studio Express ------------------------------
Create failed for User 'growstudiouser'. (Microsoft.SqlServer.Express.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+User&LinkId=20476
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)
------------------------------
'growstudio' is not a valid login or you do not have permission. (Microsoft SQL Server, Error: 15007)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=15007&LinkId=20476
------------------------------
I was wondering if anybody could advise me as to what I'm doing wrong. I am typing in the name as "growstudiouser" and the login name as "growstudio".
Or am I just using the wrong section to do what I want. I'm trying to create a user so that when the database is built I can connect to it using a username and password that I have created. I'm not sure I'm taking to the correct route as there is no password option anywhere when creating a new user.
I'm running mssql 2005. And any stored procedure I create in the master database gets created as system procedures since recently. I have created procs in the master database as user procs previously. As sp_MS_upd_sysobj_category is not supported in mssql 2005, does anyone know why this is happening.. or how I can rectify it??
Hi all, I'm trying to create a new user and a new db for my website in ms sql server express. I'm creating a new user, but when I try to login, i receive this error:error 18456. I've activated both windows and sql server authenthication. Can anyone help me or give me a link in which is explained how to create a user e configure the access?Thank You!
I am using MS Access project as a frontend and MSDE 2000 as a backend database. I have few users and i want to give every one restricted access to database based on their use.
How can i create user accounts. I checked tools-->Security--> but workgroup admin option is not active. How to solve this puzzle
Hi,I just installed mySQL on MS NT 4.0 workstation and applied Service Pack6a. When trying to add a new user using the Control Center I get aninfo message that say that user was added but this is followed by anerror message that says:There is no such grant defined for user 'username' on host 'hostname'Does anyone know what is going on here?Please respond in email.Thanks,Paul
i login my remote sql server 2005 as 'sa' and then i try the following code:
use master
go
create login [user] with password [user123]
go
yes. that code created me a sql login 'user', but even before i grant any permission to any database, the login manage to acces database: master, tempdb, msdb and one of my application databases' tables and everythings. i like to know what is it i am doin wrong and what are the default access when creating a login, and is it becoz of any configuration in my server ?
any suggestion or answer will be appreciated. thanks
How do I create a user that has full access to a database using only SQL statements?
I've create very basic database called "sampledb".
Then I've done:
CREATE LOGIN sampleuser WITH PASSWORD = '123sampleuser', DEFAULT_DATABASE = sampledb; USE sampledb; CREATE sampleuser FOR LOGIN sampleuser WITH DEFAULT_SCHEMA = 'db_owner';
Then I logon to SQL server express 2005 management studio using this user, first of all I do not see the sampledb database listed under "databases" even though it is selected in the "available databases" toolbar (how do I list it there? Because going through "attach..." tells me I do not have the privileges to browse for the database to attach).
I then open up a new query (sampledb is selected), and type (products is one of the table in sampledb): select * from products;
And I get:
Msg 229, Level 14, State 5, Line 1
The SELECT permission was denied on the object 'Products', database 'sampledb', schema 'dbo'.
Shouldn't the chosen schema (db_owner) give me the right to do this? And how do I fix this from the query window (without going through the GUI, just using SQL statements)? And how do I list sampledb under the databases when I log in as this user?
