Managing User Access To Databases

Aug 21, 2002

Scenario:
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

View 2 Replies


ADVERTISEMENT

List All Databases That A User Has Access To

Nov 17, 2014

Any way to list all databases that a user has access to?

View 6 Replies View Related

How To Find Out Which Databases A User Have Access To?

Apr 3, 2007

Hi, 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

View 1 Replies View Related

Managing Development & Production Databases

Jun 6, 2000

Hello everyone,

I have a question about how to efficiently develop on a development version of a database and get those changes over to the production database without loosing live data, etc.?

Here is how we are trying it:

1 Export production database to temp database(exact copy)
2 Delete production database
3 Export development database (definition only, no data) to new database with production database's name
4 Export production (temp) database (data only) over to new production shell

We are having truncate errors due to FK constraints.

Is there a better way?

Thanks for all help

Kevin

View 1 Replies View Related

Managing SQL 2000 Databases From SQL 2005 EM

Nov 7, 2007

Hi,

I have just installed SQL 2005 client tools and management studio to register SQL 2000 databases. Although it registers the servers successfully , I can't see any activity monitor on the SQL 2000 registered server . Is it something more that I have to install to see what's happening ?

Thanks in advance.

View 4 Replies View Related

Managing Multiple SQL Server Databases

Oct 9, 2006

Hi,

I have to manage many SQL Server databases on several servers. How can I manage the jobs, backups and the space on the disc without going to each and every server and database and job? Is there any script to run this? It will be very helpful if you can provide me the sample script or point me to any web site where I can get the info/script for this. Thanks in advance...

View 6 Replies View Related

Managing SQL 2000 Databases On Vista...

Feb 23, 2007

I have one database at an ISP running on a SQL 2000 server. Under Windows XP Pro, I installed SQL 2000 administrative tools then I could use the database manager as well as the query tool to manage my database.

I have upgraded my machine to Windows Vista Ultimate. SQL 2000 client services won't install under Windows Vista.

Under Windows Vista, I was able to create an ODBC component that connected successfully to the SQL 2000 remote database, but I no longer have a transaction tool or a gui to manage the tables.

My goals are simple: I want to be able to view/add/drop tables and data using some sort of GUI. I would also like to have some sort of SQL transaction client. I don't need to do any high level database management, just view/change/add/delete tables in the one database that I own on that server.

My isp is NOT going to upgrade their SQL. I do not own Microsoft Access or Microsoft Excel.  What FREE software options do I have, given my goals?

View 7 Replies View Related

Restricting Data Access To System Database And Visibility To All Other User Databases...!

Jan 31, 2008

Hi,

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,

View 6 Replies View Related

SQL Security :: Adding A Windows Login Did Not Permit Access To End User Databases

Oct 24, 2015

Deleting 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 Related

SQL 2012 :: Report GUEST User Access Status In All Databases On Instance Level

Apr 17, 2015

Below 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

View 1 Replies View Related

Managing SQL Express Databases With Standard Version Of SQL Server Management Studio

Mar 28, 2008

I have a windows 2003 server which has SQL 2005 Express with advanced services installed on it. Then a few weeks back the company purchased SQL server Standard Edition which comes with SQL Server Management Studio (which has more features than SQL Server Management Studio Express currently installed on the server where sql express is running)I have been trying to schedule a maintanance plan on the SQLExpress Instance database from the SQL Server Management Studio that came with the standard version of sql but i have not been able to have all SQL Server Management Studio functionality available when connected to the SQLExpress instance. So is there a way i can connect to the Express Instance from management studio(that comes with the standard edition of sql) and have all its functions available when working with a SQL express database. Or i must upgrade the express database?  

View 5 Replies View Related

SQL Server 2012 :: Query To Find User Who Last Modified User Roles / Access?

Dec 6, 2013

I would like to know if there is a way to find out who changed a users roles/access WITHOUT using the audit function. For example, if a user account was created and given SA access then changed to read only, how can I find out who made that change? I tried searching for an answer, but kept getting no results. I'm thinking this may tie into the sys.sysusers view?

View 3 Replies View Related

How To Setup The User Access Right To A BackupOperator User In SQL2005.. Thanks

Nov 21, 2007

I would like the Backup SQL user A can backup and restore DATABASE_a, what access right I need to grant to him. Although he can backup the database_a to the c:Program Files.....Database_a.BAK but he cannot restore it with some message said ''not enough security privieges..

Please quote some example right assigment so that I can replicate to our environment

Many Thanks...

View 6 Replies View Related

Multi-user Access Through A Data-access Layer/remoting Server

Oct 30, 2007

Hi guys,

I've been developing desktop client-server and web apps and have used Access and SQL Server Standard most of the time.
I'm looking into using SQL CE, and had a few questions that I can't seem to get a clear picture on:

- The documentation for CE says that it supports 256 simultaneous connections and offers the Isolation levels, Transactions, Locking, etc with a 4GB DB. But most people say that CE is strictly a single-user DB and should not be used as a DB Server.
Could CE be extended for use as a multi-user DB Server by creating a custom server such as a .NET Remoting Server hosted through a Windows Service (or any other custom host) on a machine whereby the CE DB would run in-process with this server on the machine which would then be accessed by multiple users from multiple machines??
Clients PCs -> Server PC hosting Remoting Service -> ADO.NET -> SQL CE

- and further more can we use Enterprise Services (Serviced Components) to connect to SQL CE and further extend this model to offer a pure high-quality DB Server?
Clients PCs -> Server PC hosting Remoting Service -> Enterprise Services -> ADO.NET -> SQL CE

Seems quite doable to me, but I may be wrong..please let me know either ways

Thanks,
CP

View 3 Replies View Related

How To: Determine If Current Windows User Has Login Access, Database Access And If They Are A Member Of A Specific DB Role.

Mar 25, 2008


I need to determine the following about the current authenticated Windows domain user who is trying to access a SQL Server via a trusted connection.

1 Has the current user been granted login access to the trusted SQL Server?

2 Has the current user been granted access to a specific database?

3 Is the current user a member of a specific database role such as (DB_ROLE_ADMINISTRATORS)?

Thanks,
Sean

View 6 Replies View Related

Reporting Services :: Check Access Fails To Grant Access To Report Item For Current User

Sep 10, 2015

Is there any way to get more information for when IAuthorizationExtension::CheckAccess fails to grant access to a report item for the current user? Specifically, it would be useful to know:

1. URL of attempted report
2. IP address of user agent
3. Identity of current user
4. Date/Time of the failed attempt

ssrs2014

View 7 Replies View Related

How Can I Rename One Of My User Databases In SQL?

Apr 20, 2001

How can I rename one of my user databases in SQL?

View 2 Replies View Related

User Can See All Databases SQL 2005

Mar 20, 2008

I 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

View 9 Replies View Related

Grant Permissions In All User Databases

Jan 19, 2001

Hello 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

View 2 Replies View Related

Identifying System/User Databases

Nov 12, 2002

Hi,

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

View 4 Replies View Related

Selecting Only Databases In Which User Has A Role

May 31, 2006

I 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 Related

List All Databases That A User Has A Login

Nov 26, 2007

Hi 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

View 5 Replies View Related

Moving Some User Databases To New Server

Apr 10, 2008

Hi!

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?

View 5 Replies View Related

With User Sa I Can See Mot Databases How Can The Rest Of The Users See Them All?

Jun 26, 2007

I 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

View 3 Replies View Related

Access Or SQL Databases

Jun 2, 2004

I've read afew articles about .Net and they mentioned not using Access when developing with VS.Net, but the articles didn't go into why. Between the two, is one better/easier to use than the other? Is there a cost difference? Or does it depend on what you are doing?

Any input would be appreciated. Thanks.

View 26 Replies View Related

Access Databases Using ADO.Net

Sep 20, 2005

Hi I am using Visual Studio.Net 2003 and SQL 2000 Web applications run using the ASPNET user account.
I have to set up this account and grant it permissions before my Web application will have access to a SQL database. How do I grant permissions ?.

View 1 Replies View Related

Need To Find And Document Permissions In User Databases

Feb 11, 2005

Hi 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!

View 3 Replies View Related

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

View 5 Replies View Related

Listing Size Of All User Databases In SQL2005

Aug 9, 2007

Can anyone please tell me of a way of generating a list of all user databases and their size with one command or view?

Thanks,

View 5 Replies View Related

Converting Access Databases To SQL

Apr 18, 2001

what is the proper method of taking a database made in MS Access and converting it so that it runs in SQL Server 7?

View 1 Replies View Related

Single ID Access 2 Databases

Mar 27, 2006

Is it possible to have an ID that can access 2 databases? If it's possible, I have to create the same ID and password under each database?

View 6 Replies View Related

Access To Different Databases In Different Domains

Apr 16, 2007

Hi everybody,there are several SQL-Server 2000 databases within a company locatedon different servers in different domains. On every database you canfind the same table X.I want to merge these tables X (UNION query) and print the result witha Crystal Report.Unfortunately I only have little knowledge on security, domains,distributed applications.Thanks for help.

View 2 Replies View Related

Missing Mdf Won't Allow Access To Other Databases

Mar 13, 2008

The project I work with consists of about a dozen databases. One of the .mdf files was deleted by one of the team members. Since we are still exploring with the data structures to some degree, we didn't have a backup for this file, but we do have the .sql file that created the database. Now, however, we cannot access any of the databases because the OS reports that it can't find the one file. The log shows that it connects to the others fine, but gives us a login failure because of one missing file [Error: 18456, Severity: 14, State: 11]. Losing the file is of no importance to the team, but it sure is causing a problem for SQL Server 2005. How do we get the rest of the databases to connect and just drop the damaged one to recreate it later?

View 8 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved