Adding A User To A Read-Only Database
Aug 1, 2007
Hey guys,
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:
exec sp_change_users_login 'Update_One', 'YYY','YYY'
Except for I get the following error:
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?
View 1 Replies
ADVERTISEMENT
Jan 23, 2001
Hi everyone,
I try to add a db_role or a user to all my databases with one script. Although parsing doesn't report any problem I get a Syntax error during execution.
I first select the database names into a #temp table which has two columns, ID and dbname.
After that I use the following code:
DECLARE @Count smallint
declare @dbVarchar(20)
SET @Count = 1
WHILE (@Count <=(SELECT MAX(ID) FROM #temp))
BEGIN
Exec ("USE @db")
EXEC sp_adduser 'test'
EXEC sp_addrolemember 'my_role', 'testrole'
SET @COUNT = @COUNT + 1
SET @db = (SELECT dbname FROM #temp WHERE ID = @Count)
END
It seems that the "Use @db" part has no value for the variable @db.
Does anybody knows how to solve this?
View 1 Replies
View Related
May 9, 2012
I'm taking a database(read-only) backup from one server and restoring it on other server. As soon as restore is done it is bringing database into single-user read-only mode.
why it is bringing the database into single user mode ?
View 1 Replies
View Related
Oct 29, 2001
I am trying to add a user in every database. So far I have tried using a cursor - Wont work because you cannot issue Use @database_name within the cursor. I have also tried master.dbo.sp_MsForeachdb "exec sp_grantdbaccess 'user'" this does not work either as it addes the user to the first database then doesnt cycle through. I also tried fully qualifying the sp_grantdbaccess with master.dbo.sp_grantdbaccess.
If you have a way of doing this i would appreciate some help.
Thanks
Bill
View 4 Replies
View Related
Aug 3, 2006
Hi to all DBAs,
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
^_^x
View 3 Replies
View Related
Oct 5, 2015
I would like to add database to single user mode to enable broker. So, i have tried this:
ALTER DATABASE myDb SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE myDb SET ENABLE_BROKER
ALTER DATABASE myDb SET MULTI_USER
But the query works more than half an hour then i have stopped it. i guess it has infinite loop, I get also many of this messages:
Nonqualified transactions are being rolled back. Estimated rollback completion: 0%.
There is no other transaction also there is no other user connections. So, what happens?
View 5 Replies
View Related
Dec 27, 2006
We have been working on an application that will be using a forms-authenticated report server (RS2005) as a reporting back-end. Using the reporting services web service I have been able to assign permissions to objects in reporting services no problem. The issue is that each user needs to be added to the System User role to be able to use the report builder properly. I can't seem to find a way to do this programmatically. Any idea?
View 1 Replies
View Related
Jan 12, 2012
i attached adventure works in sql server 2008 and it showing as read only ,make it read write or remove read only tag from database.
View 11 Replies
View Related
Jan 18, 2008
I have two database files, one .mdf and one .ndf. The creator of these files has marked them readonly. I want to "attach" these files to a new database, but cannot do so because they are read-only. I get this message:
Server: Msg 3415, Level 16, State 2, Line 1
Database 'TestSprintLD2' is read-only or has read-only files and must be made writable before it can be upgraded.
What command(s) are needed to make these files read_write?
thanks
View 7 Replies
View Related
Jun 27, 2014
i have a database which get refreshed every day from client's data . and we need to pull heavy data from them every day as reports . so only selects happens on that database.
we do daily population of some table in some other databases from this daily refreshed DB.
will read uncommitted or NOLOCK with select queries to retrieve data faster.
there will be no dirty read as there are NO DML operation in that database so for SELECT which happens concurrently on these tables , will NOLOCK work?
View 2 Replies
View Related
Sep 6, 2000
SQL7 -
Had a user created from a local NT account called joe. Used mixed mode athentication. Things are fine.
Later, we needed to add all users from primary domain account (domain2joe), and not have users on all the local servers (joe). Instead central athentication. So I removed the SQL user bill(that was there from local NT account), then went ahead and added the same username, but from a different domain (domain2joe). (same SQL username!) Now when I go into database access to grant user joe access to database1, SQL complains saying that the user already exists. I check the database1 and the user isn't there. BUT, the user bill exists on the SQL role "public" of that database.
I can I add this user and grant access to database1 when SQL thinks the user already exists?
How do I remove user from public role?
thanks in advance,
View 1 Replies
View Related
Nov 26, 2007
Guys
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.
Thanks
View 2 Replies
View Related
Jan 18, 2012
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?
View 6 Replies
View Related
Oct 10, 2005
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.
Many thanks for any advice comments.
Tony
View 2 Replies
View Related
Jan 29, 2004
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.
thanks,
View 5 Replies
View Related
Aug 12, 1999
Does anyone know how to programmatically do the functions that SQL Security manager is doing when it moves an NT login into SQL?
I want to automate the task of granting NT users the ability to do a trusted SQL connection.
thanks Jeff
View 1 Replies
View Related
Mar 28, 2006
hey guys,
i'm trying to add a new user to a certain database directly through the users section in this database, but i was given an error that i can't and nothin was added, so i tried adding this user from the Logins section, a pop up message showed saying that this user already exists knowing that it doesn't when i try to create a user from the Logins in a different username it worked but the problem is i can't change the user i must stick with the username that is not working.
how can i fix it??
Thanks,
Oliver
View 1 Replies
View Related
Jan 11, 2008
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'.
Help Please
TMDavis
View 1 Replies
View Related
Mar 16, 2008
Hi, I'm able to connect to my db using ASP.NET, but now I need to connect with ASP. I need to add a user like <computer name>ASP( I already have <computer name>ASPNET) using windows authentication to the db. I cannot seem find the way to do this in SQL Server management Studio, because it tells me that it doesn't find that group. Where can I add this user?Thanks a lot.
View 3 Replies
View Related
Aug 23, 2005
Hi All,I am having a serious problem of removing and adding again an user in adatabase.Microsoft SQL Server 2000 - 8.00.760 (Intel X86)Dec 17 2002 14:22:05Copyright (c) 1988-2003 Microsoft CorporationEnterprise Edition on Windows NT 5.2 (Build 3790: Service PackSomehow, the user was created earlier but was not able to run queryassigned to him. As a result I wanted to drop and recreate the user.I have done many possible things to create the users again with thesame user id but failed.1. I tried to delete this user from the enterprsie manager security-->logins-->user1It deletes but when I try to add again, it gives me error message(Error 15023: user or role u'user1' already exist).2. Then I tried in the db:delete sysusers where name='user1'it deletes the user1.3. Again tried adding, got the message in 1.4. Then I trieduse db1EXEC sp_change_users_login 'Update_One', 'user1', 'user1'Server: Msg 15291, Level 16, State 1, Procedure sp_change_users_login,Line 88Terminating this procedure. The User name 'user1' is absent or invalid.I also tried master database and ran the following.EXEC sp_droplogin 'user1'The login 'user1' does not exist.But If I try to add the login user1, get the error message.Error 15023: user or role u'user1' already existI also ran the following when I got Ad hoc error messageexecute sp_configure "allow updates",1goreconfigure with overridegoCould you please tell me how I can solve this problem.I do highly appreciate your help.Thanks a million in advance.best regards,mamun
View 1 Replies
View Related
Jul 20, 2005
In Ms SQL 2000, I want to be able to share my database with another user butI only want them to be able to look at the database and not be able tochange anything.Does anyone know how I go about doing this?
View 1 Replies
View Related
Apr 26, 2004
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.
ddave
View 13 Replies
View Related
May 22, 2008
Hi,
Please give the T-SQL script for this ? Thanks
Shanth
View 4 Replies
View Related
Aug 17, 1999
Dear All,
I have inserted a new NT user via the Security Manager applet into a group associated with two databases. Now when I select 'Logins' from the Server Manager window and then double-click the newly added user, I receive this message 'Error 21770: The name '<NT username>' was not found in the Users collection.'
Can anyone please explain the above error message.
Thanks
View 1 Replies
View Related
Apr 5, 2004
This is driving me crazy! The SQL Statement refenced is shown at the end of this email.
When I try and run the statement, an error is raised saying that Undrawn_GT5MIL_LE365Days is invalid (likewise for Undrawn_LE5MIL_LE365Days). From what I can gather, it is saying that I cannot include a User Defined variable in another argument. This is unlike Access. Any suggestions?
SQL View.......
SELECT TOP 100 PERCENT QRY_FacNew_Term.Category, QRY_FacNew_Term.Fac_No, QRY_FacNew_Term.Client_Number, QRY_FacNew_Term.Client_Name,
Undrawn_GT5MIL_LE365Days = CASE WHEN Undrawn_CDN >= 5000000 AND Term <= 365 THEN Undrawn_CDN ELSE 0 END,
Undrawn_GT5MIL_GT365Days = CASE WHEN (Undrawn_CDN >= 5000000 AND Term > 365) OR
(Cr_Limit_CDN IN (0, 1)) THEN Undrawn_CDN ELSE 0 END, [Undrawn_GT5MIL_LE365Days]+[Undrawn_GT5MIL_GT365Days] AS Total
FROM dbo.QRY_FacNew_Term
WHERE (Exclude <> 'Y')
ORDER BY Category, Client_Name
View 1 Replies
View Related
Apr 23, 2007
I have an ASP.NET 2 external application that is using and mix of the web services and tables/sps available from my 2005 SSRS.
I want to add an authenticated user from the external application. I don't see a web service that will do this. I have been through the SP's and can create roles, authenticated users, policies etc. But I think I need to update the XmlDescription field in the SecData table.
Do I need to build this XML string from scratch or is there a function / sp / web service that will do this. XML is a long ways from my strong suit.
Or am I just crazy and I should redirect my users to the Security tab!
Any help or advise would be appreciated.
View 6 Replies
View Related
Mar 11, 2008
All,
The current setup is as follows:
SQL 2005 64bit v9.0.3054
Windows 2003 SP2
Problem: When trying to add an AD user to SQL, I receive error 15401
Background: The user's last name was recently changed and so all relevant AD fields were appropriately changed:
Lastname, Displayname, E-mail, UserLogonname, UserLogonname (pre-Windows 2000). The user's previous email account has not been changed except for additional email addresses to account for the new name and old name. Also note, the uses Exchange alias is still the previous account name.
When I enter the user's new account name into the Login Name field in SQL I recieve the 15401 error. When I do a search for the user's new account name SQL finds the previous account name and not the new one. I have reviewed KN 324321 and that article does not resolve this error. The user account can be found be only using the original account name and not the new one.
On the same SQL server, I opened the security settings for a folder and added the new user's account name and it was found. I then had it search for the user's old login name and it could not find it.
Any help would be appreciated...
Regards,
Ian
View 8 Replies
View Related
Oct 5, 2006
Hi,
I've created an rdl report in reporting services 2005. Report is working fine. I've deployed this report on SQL Server 2005. The problem is this that to access the reports from client, I need to add client's login ID in Administrator user's group os server. If I dont add them in that group, it shows following error:
"The permissions granted to user 'loginname' are insufficient for performing this operation. (rsAccessDenied)".
This solution works fine in development but in actual environment, I can't add users in that group. Can anyone tell me how to view reports without adding user in administrator group. Its urgent.
Looking forward for help.
View 3 Replies
View Related
May 31, 2007
hello,
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.
Thanks
View 1 Replies
View Related
Feb 25, 2008
Hi, Apologies in advance if you get confused by reading this.... I am trying to create an additional step in the Create Wizard User Control that is provided by ASP.net. The additional step that I want to add will come after a user will create their username, password, email etc. The information which I want to save in the extra step are details such as firstname, lastname, address, height, weight etc. (I am creating an online weight management system for dieticians).When I run through the application, the username, password etc save perfectly to the database, but nothing happens with the other "personal information". There are no errors thrown so I don't know where the problem is coming from.I have included the code below as I have it:The code behind the Register.aspx file is as follows: <asp:SqlDataSource ID="InsertExtraInfo" runat="server" ConnectionString="<%$ ConnectionStrings:ASPNETDBConnectionString1 %>" InsertCommand="INSERT INTO [aspnet_UserInformation] ([first_name], [surname], [address1], [address2], [city], [country], [number], [height], [weight]) VALUES (@txtFirstName, @txtSurname, @txtAddress1, @txtAddress2, @txtCity, @txtCountry, @txtNumber, @txtHeight, @txtWeight)" ProviderName="<%$ ConnectionStrings:ASPNETDBConnectionString1.ProviderName %>"> <InsertParameters> <asp:ControlParameter Name="txtFirstName" Type="String" ControlID="txtFirstName" PropertyName="Text" /> <asp:ControlParameter Name="txtSurname" Type="String" ControlID="txtSurname" PropertyName="Text" /> <asp:ControlParameter Name="txtAddress1" Type="String" ControlID="txtAddress1" PropertyName="Text" /> <asp:ControlParameter Name="txtAddress2" Type="String" ControlID="txtAddress2" PropertyName="Text" /> <asp:ControlParameter Name="txtCity" Type="String" ControlID="txtCity" PropertyName="Text" /> <asp:ControlParameter Name="txtCountry" Type="String" ControlID="txtCountry" PropertyName="Text" /> <asp:ControlParameter Name="txtNumber" Type="String" ControlID="txtNumber" PropertyName="Text" /> <asp:ControlParameter Name="txtHeight" Type="String" ControlID="txtHeight" PropertyName="Text" /> <asp:ControlParameter Name="txtWeight" Type="String" ControlID="txtWeight" PropertyName="Text" /> </InsertParameters> </asp:SqlDataSource>
Then the code I have behind the Register.aspx.cs page is: protected void CreateUserWizard1_CreatedUser(object sender, EventArgs e) { TextBox UserName = (TextBox)CreateUserWizard1.FindControl("UserName"); SqlDataSource DataSource = (SqlDataSource)CreateUserWizard1.FindControl("InsertExtraInfo"); MembershipUser User = Membership.GetUser(UserName.Text); //object UserGUID = User.ProviderUserKey; DataSource.InsertParameters.Add("UserId", UserGUID.ToString()); DataSource.Insert(); } I know there is a problem with the code on the aspx.cs page but I cant figure it out. I need the username / password information to relate to the personal details information. I know I have to create a foreign key in the asp_UserInformation table that will link to the username in the asp_Membership (where all the username / password info is stored) Any help will do, I'm almost in tears here!!!The Spud
View 1 Replies
View Related
Oct 6, 2005
This is a pretty simple query that tracks 'yesterdays' apply clicks on our website.
SELECT MIN(A.hitDate) AS minDate, D.AffiliateName, B.ProdType, COUNT(DISTINCT A.ipAddress) AS TOTAL
FROM ArchiveApplyTracking A LEFT OUTER JOIN
Configuration.dbo.Affiliates D ON A.affiliateID = D.AffiliateID LEFT OUTER JOIN
Incoming C ON A.campaignID = C.ID LEFT OUTER JOIN
Configuration.dbo.ProductType B ON A.productTypeID = B.ProdNo
WHERE (A.hitDate >= DATEADD(d, DATEDIFF(d, 0, GETDATE() - 1), 0)) AND (A.hitDate < DATEADD(d, DATEDIFF(d, 0, GETDATE()), 0)) AND (A.deal = 1) AND
(A.ipAddress NOT IN
(SELECT ipAddress
FROM [Tracking].[dbo].ipTracking)) AND (B.ProdType NOT LIKE 'refused%') AND (D.AffiliateName LIKE 'ARROW%') AND
(B.ProdType NOT LIKE '%prequalify%')
GROUP BY D.AffiliateName, B.ProdType
ORDER BY D.AffiliateName
No problem there, that's fine.
But how can i put a simple date range interface on my published report so users can say "get me the stats from 04/09/2005 to 20/09/2005, which then runs the query?
Thanks for any help
View 1 Replies
View Related
Sep 15, 2015
I have 3 servers taking part in an AlwaysOn AG.
I want to grant a user READ access to only one replica, and certainly not to the principle.
How do I go about doing this?
View 4 Replies
View Related
Aug 11, 2005
I am receiving an error based on my query that is giving me the following error:
Code:
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'Config'.
I'm assuming that this is because the table was not made by dbo, but my domain account.
The query is currently defined as:
Code:
SQL = "SELECT * FROM Config"
but I'm assuming it needs to be defined with the syntax of ownername.objectname. My problem is when I change the string to this:
Code:
SQL = "SELECT * FROM sanm.APHinzeE.Config"
I get this error:
Code:
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near ''.
I tried to escape the slash using / but then I get the error on that. How do I specify my account name with the slash?
View 1 Replies
View Related
