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,
I 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 RelatedHi,
I am using SQL Server Express Edition 2005 as a backend database working with Visual Basic 2005.
I am using Vista and having two users to access to my computer. User-1 and User-2.
I created a database in User-1 and works fine Visual Basic 2005.
Now the problem is when i login to my computer system with User-2. I cannot able to access the database with encountering error like "User-1/SQLExpress". I know that i cannot able to access to database which was created in User-1.
Do you any solution to this problem. when i login with user-1 and user-2 it should able to access database.
Thanks.
Regards
Kashif Chotu
Hi
I wish to setup a database that can be viewed only by a few users. How do I stop other users and the general public from seeing the database and its structures.
Cheers
Al
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
I have users logging into a sql database using sql accounts. I've created below a simplified example of three tables in my 100+ table database. I have a sql table that holds extra information about the sql users and two data tables. I would like to restrict which records the user has access to in the manufacturer and automobile tables. I know I can add the UserID column to these two tables, and then add the appropriate userID into the UserID column of the manufacturer and automobile tables. Hence records in the automobile table with userid X will not be seen by userid Y. My question is, how can I possibly avoid modifying 1000+ sql queries to reference the UserID column? It would save me a lot of time if I can filter the results a user gets based on thier sql login/userid.
+--------------------------+
+ USERS TABLE
+
+ USERID int
+ Name varchar
+ Telephone varchar
+
+--------------------------+
+--------------------------+
+ MANUFACTURER TABLE
+
+ MANID int
+ Name varchar
+
+--------------------------+
+--------------------------+
+ AUTOMOBILE TABLE
+
+ AUTOID int
+ Name varchar
+
+--------------------------+
Hello -
How can I restrict all databases on my system to 25 MB.
Can anyone provide me any script.
Regards,
Rubal
How do I grant a user permissions to only one table in a database.  How would it affect him using our Main App which is NAV with regards to his user's permission in NAV
View 2 Replies View RelatedScenario:
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
Any way to list all databases that a user has access to?
View 6 Replies View RelatedHi, 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
Hi all,
Can someone explain it to me why I am getting the following error when I try to connect SQL server express with .NET 2.0?
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Login failed for user ''. The user is not associated with a trusted SQL Server connection.Here is my code and i am using windows authentication:
<%@ Import Namespace="System.Data" %><%@ Import Namespace="System.Data.SqlClient" %>
<%
Dim connAkaki As SqlConnection Dim cmdSelectAuthers As SqlCommand Dim dtrAuthers As SqlDataReader connAkaki = New SqlConnection("Server=.SQLEXPRESS;database=akaki") connAkaki.Open() cmdSelectAuthers = New SqlCommand("select Firstname from UserTableTest", connAkaki) dtrAuthers= cmdSelectAuthers.ExecuteReader() While dtrAuthers.Read() Response.Write("<li>") Response.Write(dtrAuthers("Firstname")) End While dtrAuthers.Close() connAkaki.Close() %>
I want to give user
read access : to all tables in a database
write access: to tables only created by that user in a database
please help
Many thanks
Is there any way sa user can access folder located in local system.
Server Info:
SQL Server 2008 r2
OS: Windows Server 2008
First some explaination then the question. I have some users that legally or otherwise have gotten copies of SQL 2000 and installed it on thier local PC's. They are now using Enterprise Manager to connect to my database servers via IP and server name. They are using thier regular user id and passwords that they would use to log into the HR and Finance applications. For obvious reasons this is not a good thing. Now the question, can I some how restrict connections via EM to just thoses with an sa role? Or am I doing something else wrong or missed some hidden configuration.
Thanks in advance!
T.C.
Hi all,
I have a user who I only want to provide access to a single folder within
RS2005. I don't seem to be able todo this, they can either see everything or
nothing at all.
What am I missing? Any help much appreciated.
Kind regards
Tarun
I want to provide access to one of my users to a database on the production server. I do not want this user to be able to view anything other than the tables in that database, or the other databases on my production server.
I gave him access to one DB - TestDB- and made him data_reader on that DB. I had set that as his default database. However, when he logs in using SSMS he can see from Object Explorer a listing of all the databases on that server, although he can't access any of those. This is an external user and I don't want him to see any of that stuff, including other objects (SPs, Views, etc.,) even within TestDB.
To summarize, I want to grant access to a windows user to see/select from ONLY tables in TestDB of my production server, and I do not want him to be able to see any objects other than tables of this DB from SSMS.
I am creating a product details page that is passing the string from my products database table. I am just wondering what is the best way to filter out a column depending on the information that is in the Product_Category Column For example my database products table has the following...ProductIDProduct_NameProduct_NumberProduct_CategoryProduct_InstructionsProduct_Costetc...I want to make it so if the Product_Category = BooksThen TABLEROW with the ID of "ProductInstructions" will have a visibility of "False"
View 3 Replies View RelatedI am not able to launch the Kerberos Configuration Manager due to the above error.
View 4 Replies View Relateddisable the following system stored procedure (xp_grantlogin and xp_revokelogin)?
View 5 Replies View RelatedHi all
Certain people in our company want to use Crystal Reports for data-processing. Problem is we want them to NOT be able to access data in databases within the live server.
Given that we use trusted connection to validate all kind of data-access, I am wondering if there is a way so that (via NT administration or via some SQL Server security features)
the live-server can refuse any connection request from the Crystal Reports application. At the moment the same group of people are allowed access to database (and should remain to be so) on this "live" server via some other applications (e.g. Microsoft Access).
Any advice is welcomed.
Simon
I'm going through the SQLSecurity Checklist I found at sqlsecurity.com. One of the points it says to "Restrict to sysadmins-only access to stored procedures and extended stored procedures that you believe could pose a threat." It also lists a bunch of stored procs and extended stored procs that you should consider restricting to sysadmins only. I was wondering if someone could give me some pointers on how to do this? I would like to write a script that I could run on every sql server 2000 install that would do this. How could I ensure that every user does not have access except the sysadmins?
Thanks,
Chris
Hello everyone,
I have a design problem which I am hoping somebody can shed some light on.
I am running SQL Server 2000 using SQL authenticaiton (due to be changed to Windows authentication in the next 6 months). I have a table in my database which we shall call monthly. I want to restrict the ability to insert to the monthly table to 2 stored procedures (proc_abc & proc_xy) which I have written which do various other validation checks before it inserts the data into monthly.
Users with the Foo function assigned are able to execute proc_abc & proc_xy
I have written a VB application which can be used by users who are not familiar with SQL to be able to execute these stored procedures. (Must have Foo function in order to login to this application).
I want to restrict the ability to execute the stored procedures to users using the VB application only, and thus not be able to execute the stored procedure using Query Analyzer or such like for any Foo user.
Is there anyway I can do this?
One suggestion put to me is two split the functions. Have one function lets call it Top which can access the VB application and then have another function called Bottom which is able to execute the stored procedures. Only the VB app would have access to the Bottom credentials. But is this secure? Would I just hard code the credentials for the Bottom function user within the VB app? This doesn's seem a secure way of doing things to me.
Thanks for the help!
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 RelatedHi
I'm building a data warehouse - my end users connect using Access via ODBC Microsoft SQL Server driver (2000.85.1117.00).
However, whenever they connect using Access via ODBC they get a huge list of sys and INFORMATION_SCHEMA views, in addition to the data warehouse tables they need to access.
How can I remove these sys and INFORMATION_SCHEMA views from the list of tables/views presented to the end user?
I've tried denying access by changing permissions to deny in the public role of the master database - I have also changed permissions in the public role in the data warehouse database. When I do this, the ODBC connection fails to retrieve any objects because it doesn't have access to sys.databases (and various other unspecified objects).
I'm stuck - help!
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
I just finished installing SQL Server 2012 with the SQL Server Replication feature component checked. On configuring transaction replication, i notice the distribution database wasn't there. I ran the below query
EXEC sp_get_distributor And the value for distribution_db_installed is 0 as shown below
installed distribution server distribution db installed is distribution publisher has remote distribution publisher
0NULL000
Hi,
I accidentally populate tables into System databases/master database. What should I do? Should I delete all the tables I populate in mster database?
Thank you very much!
This is a slight re-stating from an older thread, which I think warrants some new discussion. The answer has always been that system administrators should have full access to everything on a system, including databases.
Although that is a logical position for internal IT departments it doesn't quite fit the model of systems with outsourced or external system support.
"If you don't trust your DBA, then you need a new DBA. They are in a position of authority for a reason and restricting that authority makes it impossible for them to do the job they are hired to do."
What about scenarios where you have local machine administrators that should NOT be given access to private data in a secured database, even though they need to be able to access and maintain everything else? And unfortunately some regulations are written about access to stored data whether encrypted or not...
In the modern world of Sarbanes-Oxley and PCI-DSS/CISP it is no longer so cut and dried. Especially where companies have software/hardware support contracts with third parties that require administrative access to other aspects of the systems.
So accepting that you might need someone to have administrative level access to the box but they should not be able to view the contents of a database installed on that box, what would you do?
Is there a way to create an adminstrative group that does not allow access to a specific named instance of SQL?
Is there a way to revoke access for one member of the administrators group only?
Thanks,
Ted
While attempting to set up sql replication in MSSQL 2005 one of my user databases is now in the systems database folder. I need to move it back to the user databases folder. Any help would be greatly appreciated.
View 16 Replies View RelatedHi 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
I have created a windows library control that accesses a local sql database
I tried the following strings for connecting
Dim connectionString As String = "Data Source=localhostSQLEXPRESS;Initial Catalog=TimeSheet;Trusted_Connection = true"
Dim connectionString As String = "Data Source=localhostSQLEXPRESS;Initial Catalog=TimeSheet;Integrated Security=SSPI"
I am not running the webpage in a virtual directory but in
C:Inetpubwwwrootusercontrol
and I have a simple index.html that tries to read from an sql db but throws
the error
System.Security.SecurityException: Request for the permission of type 'System.Data.SqlClient.SqlClientPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet)
at System.Security.PermissionSet.Demand()
at System.Data.Common.DbConnectionOptions.DemandPermission()
at System.Data.SqlClient.SqlConnection.PermissionDemand()
at System.Data.SqlClient.SqlConnectionFactory.PermissionDemand(DbConnection outerConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection,
etc etc
The action that failed was:
Demand
The type of the first permission that failed was:
System.Data.SqlClient.SqlClientPermission
The Zone of the assembly that failed was:
Trusted
I looked into the .net config utility but it says unrestricted and I tried adding it to the trusted internet zones in ie options security
I think that a windows form connecting to a sql database running in a webpage should be simple
to configure what am I missing?
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