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


ADVERTISEMENT

Users And Logins Across Databases

Jun 2, 2007

hi,

In sql2k you used to be able to have a same user in 2 databases under the same login. Just moved to 2005, using sql authentication, and have some users who need to access 2 databases using the same login credentials. But sql2005 will not let me create 2 users under the same login across databases. Any ideas?

Thanks,
Mat

View 1 Replies View Related

How To Restrict Users To Particular Databases

Dec 16, 2006

While web hosting I use Sql Server 2000 as the database. Imagine I have hosted 3 Web Sites. All these 3 users want to modify/update their designs. What I did is I created respective 3 users having access to the respective databases only. So that they can registerd the ip and add to the Sql Server 2000 Enterprise Manager. These users are able to access their own databases only. But all these 3 users are able to access the default databases like master,pubs etc. How to restrict this.

Also suggest which is the optimal way to give control to the respective users while using Sql Server 2000.

====Suresh, P.R, Postal Training Centre, Mysore.

View 2 Replies View Related

How Do I Hide Databases From External Users?

Jan 31, 2003

I am going to give user rights for an external user to connect to my SQL Server via Client Network Utility.

Atlough I have given user permissions to only access one database and not the whole list, how do I make sure that they cannot see all the other databases on my SQL Server?

I have 20 instances of databases on my SQL Server and ideally I would like to give 20 different people access - but each of them when they enter my SQL Server, should not even know that the other databases exist.

Thanks.

View 6 Replies View Related

Transact SQL :: Log What Users Accessed Which Databases

Nov 9, 2015

I'd like to find out who is accessing various databases and log the time they did so.

Is there a way to do this? I just need the account name and the time logged and then to write to a table so I can query it.

View 2 Replies View Related

Transact SQL :: Script To Disconnect All Users Except One From Two Databases

Aug 20, 2015

I need a script to kill existing sessions on [db1] and [db2] except sessions from [user1]. I have a script like this now:

USE master
GO
DECLARE @kill varchar(8000) = '';
SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), spid) + ';'
FROM master..sysprocesses
WHERE dbid = db_id('db1)
EXEC(@kill);

I just need to extend to run on db2 and don't kill sessions from user1

View 3 Replies View Related

Query To Obtain Users And Privileges From Databases

Apr 24, 2007

Hi to all, is my first post, i need a query or script to obtain all users and privileges from all my databases, someone to help me. I'm learning Administration SQL server 2005.



I know that sys.database_principals and sys.server_principals have information about that, but i need users - privileges of every database.



thank you

View 4 Replies View Related

Server Level Role That Can Add / Alter Logins And Corresponding Users On All Databases

Jul 7, 2015

Does securityadmin Server level role can add, alter logins and corresponding users on all databases ? 

If not what is the best role other thn SA to have to manager logins and users.

View 4 Replies View Related

SQL Security :: Can Hide Databases From Users Who Don't Have Access In Management Studio

Apr 21, 2015

I have a client who would like to access his database via SQL Server Management Studio. I created a login for him. This login has access only to his database on the server (I set this up using the user mapping), the Securables has Connect to SQL checked (otherwise he won't see his database).  When I test this login, he is able to see all the other databases on the server, though when he clicks on the ones he doesn't have access to, it will tell him that he can't access that database.  He can access his database alright.  My question is, can I hide all the other databases from him, and only display that only database he has access to?  I have tried many different ways, I can't seem to be able to do so.

View 6 Replies View Related

How To Prevent Domain Admin Users From Accessing SQL 2000 Databases?

Mar 6, 2008

Based on our database infrastructure, we need to secure our SQL databases. The security issue concerns on allowing a limited number of Domain Admin users to access the SQL databases.
We tried certain ways, based on the documents in the Microsoft web site, but we couldn€™t reach to the point of preventing the Domain Admin users accessing the SQL databases.

Thanks in advance.

View 5 Replies View Related

SQL Server Admin 2014 :: Delete Orphans Users From Multiple Databases

Oct 21, 2015

I have a requirement to delete all the orphans users for the databases. The issue I am having is with when database principal owns a schema in the DB, User cannt be dropped.

How do I transfer it to DBO in case I am looping multiple databases. This is what I got so far .

declare @is_read_only nvarchar (200)
Select @is_read_only = is_read_only from master.sys.databases where name='test' /* This should be a parameter value */
IF @IS_READ_ONLY= 0
BEGIN
Declare @SQL as varchar (200)

[Code] .....

View 4 Replies View Related

Dbo User Cannot Add New Users

Jun 17, 2008

In SQL 2005, a user in the db_owner role cannot add new users to his database.
The error message says (generic name inserted by me):
Create failed for User 'ourdomain/someuser'.
'ourdomain/someuser' is not a valid login or you do not have permission. (Microsoft SQL Server, Error: 15007)

The login is valid. I can add it easily since I have sa permissions.

In SQL 2000, a db_owner could add a new user for any existing login, but in SQL 2005, the "browse for objects" dialog box for adding a new user only shows yourself.

How can I allow database owners to add new users whenever they want without also allowing them full control over all logins? In other words, I do not want to give them the securityadmin server role.

Thanks.
-David

View 4 Replies View Related

How To Copy User Permissions To Other Users?

Oct 10, 2001

Hi Everybody,

I am working on SQL 7.0/2000. I have given lot of permissions to the user 'duser1'. The permissions like select,etc..,create... Now I want to give the same permissions(what I have given to 'duser1') to the other user called 'duser2'. Right now I hvn't created any Database Roles or Server Roles.

Do we have any easy method to copy the permissions of one user to the other user?, like script generation or any method. If anybody knows that please guide me.

tks in advance,
Sam

View 1 Replies View Related

How To Grant User Permission To Create Other Users?

Jun 26, 2007

There's something I can't quite figure out about user creating



The application that I'm currently working on is interacting with DB, therefore every time you use application you need to login as user which is fine. The problem is that certain users should be able to create new users and the new user may even have the same level of permissions as the one that's creating it ( like admin creating another admin acount or some like that).



Question is how can I allow users to create these user with giving them as few permissions as possible.



If there's is somewhere a code sample on the net I would appreciate the link.







View 1 Replies View Related

How Can One User View Other Users That Belong To A Database Role?

Nov 15, 2006

After upgrading my database from SQL2000 to SQL2005 I have noticed a change in behavior of sp_helprolemember.

In SQL2000 I could connect as 'user1' and use sp_helprolemember to find all users that belong to a certain role.

In SQL2005 sp_helprolemember seems to only show me the roles that connected user belongs to. For example, if I connect as 'user1' I only see the roles that 'user1' belongs to.

Any advice on how to duplicate the behavior from SQL2000?

View 8 Replies View Related

How Do You Allow Users To View Reports Without Having To Enter User/password?

May 31, 2007

Ideally, I'd like to have the reports use windows authentication, ie grab current user/password so long as user does not have to enter it manually. If that is not possible, then how do I make it so user does not have to enter this info manually? My theory is that I have IIS set up incorrectly for doing this, but I'm not familiar enough with either reporting services or IIS to find out.

View 1 Replies View Related

How To Authenticate Users Using User Login And Password Stored In SQL Database?

Feb 2, 2007

Hi. 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 Related

Can An Authorized User Like SA Create A Table Which The Owner Is Any Users Who Use The Application?

Aug 20, 2007

Hi,

autorized user: sa
any user : nuran
temporary table: birtablo

I need a stored procedure will execute by sa and it will create some required temporary tables for each users. For example table name is birtablo. I mean sa will create table for nuran, and when I checked the owner of the table (birtablo) I want to see nuran not dbo.
sa will execute following command:

create table nuran.birtablo (...........)

Is it possible to cerate a table by sa on behalf of any user? If it is, could you please explain?



Thanks

Nuran

View 5 Replies View Related

SQL 2012 :: Allow Low Privilege User To Execute Stored Procedure To Create DB Users

Jul 22, 2014

I have a requirement to allow a user to restore a database and then create database users and add them to the db_owner database role. The user must not have sysadmin rights on the server.

The database restore works ok by placing the user in the dbcreator role.

There is a stored procedure to create the database user and alter role membership, I want the user to execute the sp as a different, higher privilege account so as not to give the user underlying permission to create users in the database.

USE [master]
GO

/****** Object: StoredProcedure [dbo].[sp_create_db_users] Script Date: 22/07/2014 13:54:46 ******/
SET ANSI_NULLS ON
GO

[Code] ....

The user has execute permission on the stored procedure but keeps getting the error:

Msg 916, Level 14, State 1, Line 2

The server principal "Mydomainadmin1" is not able to access the database "Mydatabase" under the current security context.

Mydomainadmin1 has dbowner to Mydatabase and sysadmin rights for server. If the 'execute as' is changed to 'caller' and run by mydomainadmin1 it works so the issue is between the execute sp and the actual running of the procedure.

View 1 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

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

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

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

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

SQL: How To Display Top 5 Then Sum The Rest

Apr 6, 2006

Hello,I would like to query the top 5 best companies' sales (total sales),then total the rest, what is the quickest and effective SQL to queryit?Thanks in advance

View 8 Replies View Related

Analysis :: TOP N Plus Rest

Nov 17, 2015

I need to create a dynamic top count of customers, plus the rest. I've seen many posts on this, however the difference I want is that the rest needs to be expandable to show what customers actually make up the rest category. For example:

Top

Cust1
Cust5
Cust2
Rest
Cust3
Cust4

I was thinking potentially the top/rest could be a shell dimension and then scope on these members to show the top and rest, however I haven't been able to achieve this.

View 5 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







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