I made a backup of a production database and copied that backup over to a development server and restored the database. Now I have users saying that the application is failing on development. I have users that have NT authentication and some with SQL authentication.
What is the best way to get everything to sync up again?????
I have a server that has 20 databases . I have tested with few users with different level of access and all of them were able to connect to the server and also see, select, update , delete from a particular database which is kind of weird because they do not have a user login associated or mapped to that database. I checked and no user is part of any group in AD that would give them permission to connect . I need a query that would find the permission path of a user. I already queried with xp_logininfo but I am not getting any thing.
View 9 Replies View RelatedI need to restore test DB from production backup but once it is restored I would need all the permissions of sql logins and windows AD account intact in test Db as it was before.
View 4 Replies View Relatedhello there... i'm new here... i just want to ask for help.. i have created new sql server 7 in a new domain server named sqlserver and i have already restored my backup from the old sql server. but after restoration, i checked the users and found only bdo. i want to restore also all the users from the old sql server to the new sql server because basically they are the same. please help!
View 1 Replies View Relatedi need to change the object owner from actuate to dbo
i wrote this script which will give me an execute process which i just need to run on query analyser it works fine for object owner change from dbo to new user but gives an error saying the object does not exist when i want to change it from user actuate to dbo...can some one correct me in this regard..here is the script.
Select "Exec sp_changeobjectowner", + name + ",dbo"
From sysobjects
Where type = "P" and
uid = 6 order by name
uid i found from sysusers table
it gives me some thing like this
Result : Exec sp_changeobjectowner MonthlyPerformanceSummary_Occupancy,dbo
This works the other way around
Exec sp_changeobjectowner MonthlyPerformanceSummary_Occupancy,kavitha
error: object does not exist
And the other issue is i have certain stored procedures for which i need to give permission exec for a specific user, i did create the user...what shold i do to grant exec privilage to this user..for all the stored procedures at a time finding the stored procedure name.
its like this the name always starts with sp_actuate...does this help in finding the stored procedure and then granting the exec privilage.
Thanks a lot in advance.
Kavitha
Hi ,
I have a bunch of Users about 150 of them. I think they can't even run a SELECT .
When they try to run a select they get error messages :
"SELECT permission denied on object 'Object name', database 'Db_name', owner 'dbo'."
I have check out every thing !! All these Users are the members of public role on the database !!
They work fine on other Db's with the same settings !!
Not Sure what to do
PLEASE HELPP !!!
I need to know how many concurrent users are allowed to access the database at any point of time.Will it depend on the type of licence?Where do we set this parameter.Some users are complaining 'time out' problem.Thanks for any help!
View 1 Replies View RelatedI restored a database from one full backup.The users are missing now in the new databse.When I try to create the same users again it says that they are already existing!Please help what shall I do to restore/recreate the users?Do I need to do anything with the Master?
View 4 Replies View RelatedHello,
Im running SQL 7.0 in production with no SQL service pack applied. I am trying to copy the database over to a test box running SQL 7.0 SP3. Both servers are NT 4.0 with SP6.1a. When I restore the database on the test box, I do not get the Users restored but I do get the Tables and other items. The SQL backup Im restoring from is a complete backup. Could the fact that the server Im restoring on is SQL 7.0 SP3 and it came from a pure SQL 7.0 server?
Thanks
Hi All,
Greetings,
Sql server 7
I am trying to restore a database of sql server 7 to another. Restoring is happening fine with respect to tables, stored procedures etc, but i am not able to restore the users that are present in the source database.
Please help in this.
TIA
i restored a database from a .bak file.
from production to development.
But i dropped the database from development before restoring. Now all the users are gone from this database. Anyone know how to recover them?
the users from development are different from production's
SQL Server 7 or 2000.
I restore a database on a different server from a device file. This database has a user defined for it. In this case, 'privuser' is the name. No login has been defined.
I can add the privuser login but cannot grant db access because privuser already exists in the db. I cannot drop 'privuser' because the user owns all the user objects in the db.
How can I make the privuser login I create access the db using the privuser username in the db? Would having the login created before restoring the db as a new db work?
can there be multiple users on one single sql login?
I keep getting errors from my users randomly about the server timing out and i am wondering if they are trying to submit append queries at the same time.
I am new to this, so, I will be greatful to anyone who can clarify the following:
qn 1:
In login properties under Logins of Security, what does creating mapping to the different databases mean ?
For a given login, I notice that I can login / execute DDL statements alike for the databases which are checked and also for the ones that are not checked.
Then, what does
Security -> Logins - > Login Properties -> User mapping -> Users mapped to this login -> checking the database - accomplish?
qn 2:
For a perticular login, say 'Bob' which has server admin rights, I wish to include two more databases. For this I 'checked' the databases, which I wish to include, in the
Security -> Logins - > Login Properties -> User mapping -> Users mapped to this login
When I clicked 'OK', I get the following error and the two databases are left unchecked.
User, group, or role 'SQL1AGENT' already exists in the current database. (.Net SqlClient Data Provider)
How can I include the databases ?
Hi all,
Is there any way to get the @@trancount for a connection from outside the connection?
The reason i'm asking is that a customer just lost a days work, and there is nothing in any table from a certain time onward. One theoy is that a backup was restored, but we checked and that is not the case.
So another theory is that a certain sproc began a transaction, but never finished because of an error. (We had some strange timeouts as well, so this is quite plausible.)
So the question: Can i get a list of current connections with their trancount? I could just run this to see if a certain connection would never get back to zero to check the transaction theory.
Thanks in advance,
Gert-Jan
Hi,
After doing a database restore on my development server, It only shows DBO As the db user..When I query SYSUSER all other users are still there..But doesn't show up in enterprise manager...
Anybody came acrosss the same situation ?
Thanks for you help in advance.
HI guys,
I just restored a database on a new server with a backup(complete backup stored in backup device) from another database on another server using the "with move" option. In fact here was my process:
Alter database ngauge SET SINGLE_USER WITH ROLLBACK IMMEDIATE
restore database ngauge
from disk = 'C:Program FilesMicrosoft SQL ServerMSSQLBACKUPgauge1.BAK'
with move 'NGAUGE' to 'C:Program FilesMicrosoft SQL ServerMSSQLDatagauge_Data.MDF',
move 'NGAUGE_Log' to 'C:Program FilesMicrosoft SQL ServerMSSQLDatagauge_Log.LDF'
Alter database ngauge SET READ_WRITE
it worked.
But it did not move the 98 or so users/logins...from the source database
what is going on??? what am I doing wrong??!!
Please help!
'W
Third question:
I'm backing up a database which has some users.
When I try to restore it to another machine which has the same users and already has an old version of the database the database cannot be accessed. I must drop the users from the database by using the stored procedures sp_dropuser <username> and then add it again to the database from the Enterprise Manager.
Why this happens??
Regards,
Manolis
Hi,
I have a few db's that I am backing up from an old sql2000 system and moving them to the new sql2005 server. The first db's restored without any problems but now I am getting some problems with users and logins.
I am restoring in the following order
1.) Creating empty db "123"
2.) Restoring database from file to database "123"
3.) Creating Login to this database (error happens when linking it to the database, "user exists")
When connecting thru QA I get "Cannot open user default database login failed"
I only have 1 login per database and I don't mind manually deleting and recreating them all but it doesnt seem to be working :S
Any suggestions much appreciated!! :)
Thanks!
mike123
Hello All,
View 3 Replies View RelatedI'm trying to set up my reports with parameters, to select the paramater by the users login. For example, if someone from the Northwest Region accesses Report mangers and then a report. Instead of them clicking the drop down, for the report parameter. The report automatically generates from the login that gave them access to report manager. So it diplays the information for the Northwest Region.
I know how to do this for Data Driven subscriptions.Such as creating a table , with Region, login, email, and all the information relevant. But how do i do this for users accessing the site theirselves? Is this possible?
I am in a situation where I would like to use a SQL login instead of adding individuals windows login to the server. Is there a way to force a login instead of having the report server not give rights at all??
I hope that makes sense...
Thanks
I have to give three users read access to a database.  My manager told me to map them to a User called "zxDatabase.Read.ug". I scripted the User and got this...
CREATE
USER[zxDatabase.Read.ug]
FORLOGIN[zxDatabase.Read.ug]
I don't see a Login at the server level named zxDatabase.Read.ug, so this is confusing. Where is this login in SSMS?
How do I map my three users to user zxDatabase.Read.ug. Or am I not understanding correctly? Do I just add there windows login to the database as Users and configure them the same as zxDatabase.Read.ug
Is there any easy way to move users between SQL Servers for a particular database?
We have tests and development servers I would like to make sure the user lists are in sync for that db.
Thanks
How would I save a users password to a table, change it to a default password and restore it if necessary?
View 3 Replies View RelatedWe build up a new website in .net mvc 4.0 on a w2012 server with MSSQL 2012 database.We use windows autentification and normal anonymous access through NT AUTHORITYIUSR who is also a login in the database.But we make extra login possibility for users who are registered and they can insert and modify data in some tables in the database. And I'am afraid of giving NT AUTHORITYIUSR insert and update for some column in tables.I think of set up a new login for the database. Run a new connection string on login and set up special permission for that extra user on the database, and let the user become a member of the ordinary user on the database.
View 0 Replies View RelatedIs SQL Server sensitive to Domain group name? Like "Domain Admin"?
I have user that belong to "myDomainDomain Admin" group. Group is in SQL as sysadmin but user cannot login using domain credentials. When I move that user to a different domain group which that group is in SQL again as sysadmin my user is able to login.Â
Environment: SQL 2008 Standard Edition.Â
I am migrating a database TESTDB from SQL 2008R2 to a new server running SQL Server 2012.
Management has decided the current sql users should have "better" user names. So the login and username "BadUsername" on the old server should be called "GoodUserName". Goodusername should have the same permissions as Badusername.
I have now restored a backup from the old server to the new server.
I used the following script for creating the login:
CREATE LOGIN [GoodUserName] WITH PASSWORD=N'difficultpassword', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
Then I run the following script:
ALTER USER Badusername WITH LOGIN = GoodUserName, NAME = GoodUserName
But the results are not what I wanted. I now have two database users: Badusername and GoodUserName. I would have preferred if BadUserName was "replaced" by GoodUserName, but it won't be a problem if I have to delete badusername manually. Worser is that GoodUserName have NOT "inherited" any permissions from Badusername.
Is there an easy way to transfer permissions or do I need to loop through the permissions of badusername and apply those to badusername?
we have an application which lets users connect to production database with windows credentials, They are able access the sql tables too with windows login. I want to restrict them from accessing the sql tables. How do I do that.?I tried a db_deny but that prevented them from accessing the application too.
View 10 Replies View RelatedHi. I have a DetailsView with Bound Fields "Login" and "Password". This informations are stored in SQL database. How to solve such authorization? How to compare password stored in database against passowrd typed by user? Is this a good idea to use CustomValidator control to write some checking procedure?. Regards. Pawel.
View 1 Replies View RelatedI have jsut started using SQL server 7 and am having problems with accounts permissions, users,roles, groups, owners etc what are the differences?
View 1 Replies View RelatedHello Everyone
How do you generate a script for all the current sql server logins and generate a script for database users for each database.? You can script operaoers, tables, databases, and a lot of other objects by using the “All Tasks” shortcut menu option, but I haven’t figured out to script logins and database users. Any help would be greatly appreciated.
I’m using sql server 2000 and Enterprise Manager.
Thanks
GEM
When I have tried to restore a Backup Database from Tape, I get
this message :
The DataBase your are attemping to restore was backed up under a
different sort order ID (146) than the one currently running on
this server (52), and at least one of them is non-binary sort order.
Someone can Help me
Thanks in advance
SAG