How Can I Rename One Of My User Databases In SQL?
Apr 20, 2001How can I rename one of my user databases in SQL?
View 2 Replies
ADVERTISEMENT
Rename NT Server And SQL Databases
Dec 14, 1998We have an NT 4.0 member server in a production domain which has SQL 6.5 loaded with a number of databases. There is a requirement to rename the NT server to another name. Will we have to reinstall SQL and reload any databases after the server is renamed? If not - are there any other considerations/registry changes necessary to ensure SQL will run correctly?
Thank you for assistance.
How Can I Rename My ReportServer Databases?
Oct 12, 2007Hi,
I am trying to move my Reporting Services databases from one server to another one. On the old server there was only a single instance so my databases were named ReportServer & ReportServerTempDB. On my new server I have multiple instances and, after installation (which I didn't perform), the databases on the new server are ReportServer$SQL_VS11 & ReportServer$SQL_VS11TempDB.
I was able to successfully restore both databases and rename them at the time of restore and also restore my encryption key but now when I try to view one of the reports I'm given the error:
Invalid object name 'ReportServerTempDB.dbo.PersistedStream'.
I found this article:
http://technet.microsoft.com/en-us/library/ms159093.aspx
It states that "This error occurs because the ReportServerTempdb name is stored internally and used by stored procedures to perform internal operations. Renaming the temporary database will prevent the stored procedures from working properly."
This leads me to believe that what I'm attempting to do is not possible. I asked the developer in charge of the reports if we could simply republish but he informed me that we would lose history in this case. I may have to simply leave the old database names alone which would be inconsistent with our corporate naming schemes. Can anyone offer a suggestion?
Thanks,
Pat Brickson
Rename Login And User
May 31, 2000Is it possible to rename Logins and users in SQL Server 6.5. I can find no provided way of doing this although the only references to the name I can find is the login name in syslogins table and the user name in sysusers (in each database). The system catalog diagram implies that all joins are done via the SUID field within each table. Can I simply update each entry to the new name and proceed? I have tried it on a test server and have seen no ill effects thus far. Anyone else tried this?
View 1 Replies View RelatedRename Login/User Accounts
Dec 21, 2000Is it possible to rename a SQL Login or User account?
View 1 Replies View RelatedPower Pivot :: Rename Fieldname From Model To Different Presented Name For End User?
Jun 22, 2015Is it possible to rename a field-name from ( tabular powerpivot ) model to a different Presented-name for the end user ?
usualy the names in the source model are Understandable to the developer , and its Custom to his understanding and nees , but for the end user its not - its not understandable , to long and so far form the regular terms in his Business , and also the language Might be non-English..
User Can See All Databases SQL 2005
Mar 20, 2008I have a database user with rights on one database. If he connects with management studio 2005 he can see all databases.
How to change that he can only see database where he has rights on viewing and/or changing?
Henri
~~~~
There's no place like 127.0.0.1
Grant Permissions In All User Databases
Jan 19, 2001Hello together,
can anybody help me. I'm looking for an easy way to grant permissions to a user in all user databases. I already have a script which grants permission to all views and userdefined tables within one database, but since I have to run it in about 100 databases it's still quite timeconsuming.
Is there a way to execute that script in all user databases at once ???
Markus
Identifying System/User Databases
Nov 12, 2002Hi,
i´m looking for TSQL-Code (7.0/2000) to identify, if a SQL Server Database ist a SystemDatabase or a UserDatabase. In the sysdatabases there is no information abount that.
Skol,
Peter
Managing User Access To Databases
Aug 21, 2002Scenario:
1. Access to database objects is based on database roles.
2. Application administrators handle the assignment of users to database roles. Application administrators are first line managers, typically; they are not DBA's and not Help Desk staff.
Question:
What products are available to simplify the management of user access to databases in the aforementioned scenario?
TIA to all respondents.
--
Peter
List All Databases That A User Has Access To
Nov 17, 2014Any way to list all databases that a user has access to?
View 6 Replies View RelatedSelecting Only Databases In Which User Has A Role
May 31, 2006I have a user in SQL Server 2000 with public, datareader and datawriter roles on several databases. I need to select all those databases, how can I do that. I have tried sp_databases but I get ALL databases. I also tried sp_MShasdbaccess but I still get all databases.
View 4 Replies View RelatedList All Databases That A User Has A Login
Nov 26, 2007Hi everyone,
I have an instance with many databases in it. I am looking for the easiest way to see which of those databases a user has a login on. What is the most efficient way of doing this?
Thanks,
Anil
Moving Some User Databases To New Server
Apr 10, 2008Hi!
I have a SQL 2000 server with a lot of databases (about 100) and I would like to move a great deal of them (+/- 50) to a new SQL 2005 server. All the databases I would move has the same word in the database name. Let's say the all include "Customer" in the Database name.
It would be a huge job to detach and attach every db manually. Is there a method that can detach db's based on a query againts the master db with a select on the name of the db?
If so, I also need to attach the db's on the new server with a script after moving the files to a new location.
Any good ideas?
With User Sa I Can See Mot Databases How Can The Rest Of The Users See Them All?
Jun 26, 2007I have a third party application that creates several databases on my sql server. This party uses the same user for accessing all this databases. However after moving to a new server this databases the user is no longer able to query them whith the exception of the "sa" user. Could anyone explain me how to add permissions to this databases manually so the user can query them again.
Thanks in advance
brgds
Miguel
How To Find Out Which Databases A User Have Access To?
Apr 3, 2007Hi, my first question, tried to use search first but couldnät find what I need.
Hopefully someone has asuggestion.
I work for a ISV producing HR applications.
When user starts the applications the program shows all databases that the user have access to. This is done by select all names from master..sysdatabases and then trying to "USE dbname" to see if user have access,. Users doesn't have sa rights.
This procedure takes approximately 90 secs for a server with 500 databases and that has become a problem.
Does anyone know off a faster method, any suggestions?
/Regards Anders
Need To Find And Document Permissions In User Databases
Feb 11, 2005Hi folks, i've an instance with many userdatabases. i want to use SP_HELPUSER to output all database users and roles defined to em. how come i do this through a script to view permissions in all databases. I couldn't use (USE database ) in a loop.
Howdy!
Creating User And Giving Permission To All Databases
Feb 27, 2008
Hi,
Any body please give me some TSQL Stored procedure for
creating an SQL user and to give db access permission to all the databases available on a the server.
Mujeeb
Listing Size Of All User Databases In SQL2005
Aug 9, 2007Can anyone please tell me of a way of generating a list of all user databases and their size with one command or view?
Thanks,
Automating Growth Pattern Analysis Of User Databases
May 20, 1999I like to schedule a task that provides with database usage size, current users online, memusage and CPU usage and store it into a database table once in a week. I tried looking at different system tables but not successful.
We are using MS SQL 6.5.
Can someone help me on how to achieve this?
Thanks,
Mohan
Creating Databases Under MS SQL 2005 Server (SP-2): Dbo User Role
Jun 9, 2007Good Day,
I am having a problem with creating databases in MS SQL 2005 Server. When I attempt to assign an User Mapping for my SQL user account to the newly created database, the "dbo" user and schema is already assigned. When I try to drop this user in favor of my own, I receive an error message: Cannot alter the user "dbo" (Microsoft SQL Server, Error: 15150). I am connected to my database engine through the "sa" account.
Regards,
Keith
How Can I See My Databases In Management Studio Express, As A Limited User?
Apr 28, 2008I installed SQL Server 2005 Express, and Management Studio Express, while logged on as an Administrator of my XP Pro machine. When I switch to my limited user account, Management Studio Express seems not to be aware that SQL Server is even running on the machine (although Configuration Manager can see it and reports that it is running). I'm sure I've missed something simple, but I can't find the answer anywhere... how can I get access to my local SQL Server from my limited user account?
View 2 Replies View RelatedRestricting Data Access To System Database And Visibility To All Other User Databases...!
Jan 31, 2008Hi,
How would I set permission for SQL Server 2005 "User A" to prevent access to System and other user databases, also How to hide the databases that "User A" has no rights to. I mean, when User A logs in, All other user databases are not visible to him/her.
Thanks,
URGENT: Problems With Guest User In SQL 2005 Restoring Databases From Sql 2000
Sep 18, 2006Hi all.
I don't know if this is the right forum to post this question, but here it goes.
We have restored into sql 2005 the database backups made in sql 2000. We connect with trusted connection and application roles, and when trying to execute a transaction to another database (with the guest user), we get a permission error.
Does anyone know if, apart from restoring the databases, we should do something else to get the guest user working the same way as with sql 2000 in the restored databases?
Thanks in advance for your help.
SQL Security :: Adding A Windows Login Did Not Permit Access To End User Databases
Oct 24, 2015Deleting a Login from a server instance and adding it back did not show that the login was still mapped to databases. In SQL Server 2008, adding a Windows Login did not permit access to end user databases until the Windows Login was mapped to various databases. In SQL Server 2012, once a Windows Login is added to SQL Server Security, it may access ANY end user databaseWe use the following to circumvent this problem, Windows Login by Windows Login: DENY VIEW ANY DATABASE TO [TESTTest1]
View 6 Replies View RelatedSQL 2012 :: Report GUEST User Access Status In All Databases On Instance Level
Apr 17, 2015Below query tells us if guest user is enabled or disabled in a particular database
SELECT dp.name, CASE perms.class WHEN 0 THEN 'Yes' ELSE 'No' END AS 'Enabled'
FROM sys.database_principals dp
LEFT JOIN (SELECT grantee_principal_id, class FROM sys.database_permissions
WHERE class = 0 AND type = 'CO' AND state = 'G') AS perms
ON dp.principal_id = perms.grantee_principal_id
WHERE dp.name = 'guest';
Do we have a query which can also add the database name to above query output? The output must have columns with data against Name,Enabled,Database name
Recovery :: How To Find Last Login Date / Time For User DML Activity On Databases On Instance
Jun 22, 2015How to find last login date/time for user DML acitivity on databases on Instance?
Is there any way we can find our the last login date/time for databases?
Note: 1. We can find if the SQL Trace is running and store.This is not good solution
        2. Audit logins off/on is also not good solution.
       3. Using DMV's also not good option, if reboot sql server instance then historical values can not see.
SQL Server Admin 2014 :: Changing Drive Letter For System Database And User Databases
Oct 18, 2013I have system database and user database file are present in G,H and W drive.The process is going to be - copy data from G to S, H to T, W to U. Rename G to X, H to Y and W to Z. Rename S to G, T to H and U to W. Reboot the servers. The original G, H and W will then be X, Y and Z. The old S will be the new G, old T will be H and old U will be W. My question is that after doing this whether my SQL server will start or not
View 8 Replies View RelatedFailed To Generate A User Instance Of SQL Server Due To Failure In Retrieving The User's Local Application Data Path. Please Make Sure The User Has A Local User Profile On The Computer. The Connection Will Be Closed
Dec 7, 2006This is my first time to deploy an asp.net2 web site. Everything is working fine on my local computer but when i published the web site on a remote computer i get the error "Failed to generate a user instance of SQL Server due to failure in retrieving the user's local application data path. Please make sure the user has a local user profile on the computer. The connection will be closed" (only in pages that try to access the database)
Help pleaseee
Linking Tables From Different Databases Or Querying From Multiple Databases
Dec 10, 2007Dear Readers,Is it possible, like in Access, to link to tables in other SQL databases that are on the same server? I have a query that I originally had in Access that queered from multiply databases. It did this by having those other tables in the other databases linked to the database that had the query.
How Do We Determine Which User Database Tables Are Mostly Retrieved By User Or Modified By User?
May 22, 2008
Hi,
Please give the T-SQL script for this ? Thanks
Shanth
Rename
Jun 6, 2008i am trying to set up a way of renaming a table in a sp.
ive been using
exec sp_rename 'old table', 'new table'
however if i try and specify to database and owner i end up with a weired db.owm.db.own.table name in the outcome. ive tried putting using it without specifying a db name in a sp and then sched job to exec the sp but it doesnt rename, probably because it doesn know which db to look for the ld table name in. however the sched job doesnt fail?
any ideas?
Rename SP
Nov 5, 2007Hello All,
Does anyone know how to rename SP using Transact SQL ?
Thanks in advance
--kneel