Hello,
We have SQL Server 2005 with 300+ databases in it. It was set up by the conractor. How can I create new account with read-only access to all databases? Is there any "quick" method of doing it (like in Sybase, it is very easy). I've created new login, but I do not see where can I set up the permissions properly. Thanks
How can I create a SQL authentication account with read-write access to only 1 table in a SQL database.
View 1 Replies View RelatedHello , I am using sql svr 2005 ,I cannot deploy my db because local account does not have read rights , so how can I change it or how can I create a sql server account which has every rights (read,write,execute) to the database ??
F16 LÃ?GHTÃ?NÃ?NNNG
On the latest SQL 6.5 with the latest SQL 6.5 service pack, what's thetrick to creating an account besides sa that has the power to createdatabases? I was thinking of calling this account 'sa2'. Why do this?Well, my boss asked me to do it because of a SOX audit + SAS-70 auditkind of thing.I tried using the GUI to do it, but then when using the GUI as sa2, Ifind the "Create Database" has been greyed out.I have a funny feeling that the only way to get this is to hack thesys* tables, which is a little risky.Oh, and trust me, if I had the political weight in my company to getoff SQL 6.5, I would, but I don't. And mgmt already knows that SQL 6.5is past support timeframe and is a ticking timebomb.
View 2 Replies View RelatedDuring install of SQL Server 2005, we can of course use a domain account or the built-in system account for running the services. I lean toward domain for obvious reaons but would like to know a +/- to each option and why I'd choose one over the other and what consequences or limitations one may encounter if I choose one over the other.
View 6 Replies View RelatedI need to be able to have users run a query under the 'sa' account that will create another account that will just be used for reading and writing to a specific database. Is there a way to do this with just a sql script or can you only set up logins and accounts from the enterprise manager?
View 1 Replies View RelatedHello all,
I get a strange error message when I try to create a Database Mail account. I know the script is OK because it works fine on another SQL 2005 Server. This script is as follows:
exec msdb.dbo.sysmail_add_account_sp
@account_name = 'SMTPAccount01' ,
@email_address = 'someone@somecompany.com' ,
@display_name = 'Sales System - Company Name' ,
@replyto_address = ''someone@somecompany.com' ,
@description = 'SMTPAccount01' ,
@mailserver_name = 'somecompany.com' ,
@port = 25 ,
@username = 'someone+somecompany.com' ,
@password = '1234'
The error message I get is:
Msg 15466, Level 16, State 2, Line 1
An error occurred during decryption.
I tried installing SP2 and that didn't help.
Someone help please!
Thanks in advance.
Jerome Smith
What is the best way of accessing a sql server on a live server? shall I use Integrated windows or use a special user account? If I use a user account, what are the needed priviledges to give it?
thank you
is there a way to limit the access of an sa account?
ex. preventing the sa acct. to view a table defenition or drop a table
Hello,
I am seeing a couple of domain/username accounts trying to access SQL 2k5 SP2 and get the error above. The concern I have is these accounts shouldn't be trying to access SQL at all and do not exist is SQL hence the error The question I have is how can I track down what is trying to use this account and connect to sql? Thanks in advance.
John
SQL Server Log:
Message
Login failed for user 'DOMAIN ampbell'. [CLIENT: <named pipe>]
Message
Error: 18456, Severity: 14, State: 27.
Hi All,
I have a SQL server, as I use a domain account to log on to SQL server and Sql server agent, all maintanence plans work good, since I changed from a domain into Localsystem account to log on to SQL server, and Sql server agent, all maintanence plans didn't work any more, then I tried only keep Localsystem account at SQl server , using a domain log on to Sql server agent, but it's still failed to maintanence plans. The error in job history is"Executed as user: candyl. sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed.". And the message at Sql server log is :"BackupDiskFile::CreateMedia: Backup device 'D:Database BackupsNoon BackupESMDEV_db_200406141548.BAK' failed to create. Operating system error = 5(Access is denied.)". It looks like permission problem, but for Localsystem account which should has full permission, right? I tried may ways and searched from knowledge base , still couldn't find the related solution.
Anyone can give me some advices?
Thanks.
Hi All hope you can help.I have a SQL 2k Standard Ed. SP3 server that is in mixed securitymode.I have logged into EM with the sa account.Then added a Active Directory group (i.e. DomainDBAdmins) to theSystem Administrators for that server.When I try and modify the SQL server registration in EM to use trustedauthentication instead of sa I get a connection failed. Any ideas?Thanks,
View 3 Replies View RelatedWe have an issue with accessing SQL Server 2000 where the access of data from the database is slow unless the user is logged in as an administrator to their computer.
The system is as follows: SQL Server 2000 on a W2K server. Users logging into a Win 2003 domain server. Users using W2K on their workstations. Application is VB.NET using the Enterprise Library Data Block, connection pooling ON, and windows authentication.
We are assuming that the issue is down to one of authentication and that when a user is set as an administrator then they have instant access. We have been able to replicate the issue using just SQL server on a W2K workstation and accessing from another W2K workstation. Again data access is way slow unless the account is an administrator.
Glad of any ideas folks!
Kind Regards
Ian Logan
Hi,
I keep getting a connection failed error message... CANNOT the DATABASE " " requested by the Login. The Login failed for User 'NT Authority/ Network Service' error and I figure I should create an account for the site to access the Database with but I do not know how to create an account in SQL server using the Management studio?? Anyone willing to give me the exact information I need to do this so I do not do something else and ruin things please??? I am not much of an SQL DBA. thanks in Advance.
i have the following:
DECLARE @Table TABLE (
 OrgRoleNumTxt VARCHAR(10)
, AccountNm varchar(100)
, EffectDate DATETIME
, OperationNm varchar(100)
, Premium decimal(18,2)
)
Insert into @Table(OrgRoleNumTxt, AccountNm, EffectDate, OperationNm, Premium) VALUES
('00236', 'R.R. Donnelley', '2010-01-01', 'Chicago', 1000),
('00236', 'R.R. Donnelley', '2010-01-01', 'Boston', 3000)
select *
from @Table
but want this
Is it possible using basic T-SQL?
I'm creating one of my first asp.net pages, and it accesses an MS SQL database.It runs fine locally, but if you go to it remotely through a web browser, you get an exception saying that the database login failed for user ASPNET.My brother told me to do the following in SQL Server Managment studio. It seems I don't have that program installed, so I did it from the SQL command prompt.CREATE LOGIN [bigblueASPNET] FROM WINDOWSuse AdventureWorkscreate user [bigblueASPNET](Where "bigblue" is the computer name and "AdventureWorks" is the database my asp.net web page reads from).This only partially fixed the problem - now when you open the page remotely and try to access the DB I get an error message saying that permission to use the SELECT command is denied.I searched on these forums, and found this:http://forums.asp.net/69166/ShowPost.aspxFor future reference, let's call what my brother told me "Method 1" and what the previous poster did as "Method 2."I haven't tried method 2 because I don't know exactly what I am giving ASPNET permission to do. Does method 2 enable ASPNET to do anything to any database? It seems method 1 is more secure, as from the commands I could already tell it only has permission to access AdventureWorks.So, I'd prefer to use method 1. Can anyone tell me what further commands I can use to give ASPNET permission to do specific actions?If I can't figure out method 1 and have to do method 2, how can I undo what I did in method 1?Thanks!
View 1 Replies View RelatedI am trying to connect to a sql server from my asp.net application via windows authentication using the Network Service account. When I try to create a login for the ComputerNameNetwork Service account I get the following error.
I know I haven't mistyped it because I clicked the check names button and it showed as a valid id.
So what gives?
TITLE: Microsoft SQL Server Management Studio Express
------------------------------
Create failed for Login 'PREMIERDCNetwork Service'. (Microsoft.SqlServer.Express.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+Login&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)
------------------------------
Windows NT user or group 'PREMIERDCNetwork Service' not found. Check the name again. (Microsoft SQL Server, Error: 15401)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=15401&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
I use Sql 6.5 and I want to create a login for a user with
READ ONLY privileges on the tables in the databases. How do I do this?
Thanks in advance.
I need to create/read files from t-sql? Does anyone know how can I do it?
Thanks
I'm trying to connect to a database using a service account that we got created. The ID is an AD account and was added to the db as such. When I try to connect to the database using the account with the password I get [login failed for domainid]. The DBA mentioned that its setup to use windows auth, however, I can't connect with this service account using windows Auth, due to I'm using to connect via code.
How can I connect to the database from my code using this ID?
I have the ID and pwd in my code to connect with, does the ID have to be setup differently in the Database?
I have a question about ssrs security. In report manager I have set a list of users as browsers, and the builtin administrators are content manager in the parent folder where reports are. A user that is not an administrator and neither a user I added, has access to reports.
I'm thinking this person is having access because the data source of the reports use a administrator account to connect to the analysis services  using the option Credentials stored securely in the report server with the 2 checkboxes marked (use as windows credentials when connecting to the data source and impersonate the authenticated user).
I simply marked this option because SSRS is in 1 server and SSAS in other and I think kerberos need to be configured and I haven't looked into it, but I think the report access is separated from the data source.
Hi, I hope you can help.I have configured a Windows 2003 web server and SQL 2005 Server (on same box) to successfully allow remote connections and to allow access via SQL Server Management Studio Express 2005.The problem I have is that I want to restrict access to the databases on the server via the Management Studio to specific databases e.g. 1 database user "sees" only 1 database.I can configure it so that the user's remote access permissions do not allow access to other databases but they can still "see" the database listed in the Management Studio explorer.I can also configure it so that the users cannot see all the databases (by disabling View All Databases on SQL Server), but this means that they cannot not see their own database which they have permissions for.Is it impossible to have the desired behaviour of only displaying the database which the remote user accessing has permissions for and hiding all other databases?I have MSN'd,Googled and Yahoo'd this one to no avail :(Many thanksFergus
View 6 Replies View RelatedIn a stored procedure, I am trying to create a table and then read it from within that SP or from another one (nestsed). When I run the below code (simplified version of my real SP), no records are returned even though I know that there is data. The table is created and records are inserted, I just cannot read the records from the SP or one that calls it with an EXEC.
Any ideas? My code is listed below. (When I get this to work, I plan to convert the code to manipulate a temporaty table).
Alter Procedure spWod_rptWoStatusSummary_9
As
BEGIN
CREATE TABLE tblStatusSummary (TckStaffCd_F Char(3), RecType Char(20))
INSERT INTO tblStatusSummary
SELECT TckStaffCd_F, RecType
END
BEGIN
Select * from tblStatusSummary
end
I need to create a new login with SELECT rights so the users can view all tables with no UPDATE, DELETE, OR INSERT rights. But this user needs to be able to CREATE VIEWS. I have assigned the user to the Public role and gone in and modified Securables for the Database to be able to CREATE VIEW. When I connect using my new user and try to create a view, I get the error message: CREATE VIEW permissions denied in database 'test01'.
Help Please
TMDavis
Is it possible to create a database which design or data is not needed to update and i want to set to it as a READ ONLY. what are the steps to create such type of database?
View 3 Replies View RelatedI have an existing MS SQL 2005 server which is used to maintain data that has been collected from various sources around a plant. A new system has been added on our network where the data is provided in a daily MS Access database. The name of the MS Access database changes every day.
I would like to use the job manager to run a Stored Proc every night to extract the daily data from the MS Access database. Is there any way to do this.
Regards, Eric
I have an excel file with following data:
Agent State Exposure Insured Name
Rogers Inc MA 100,000 John Smith
SAN Group RI 200,000 Jim Morrison
SAN Group RI 100,000 Jimi Hendrix
123 Agency MA 300,000 Mickey Mouse
Rogers Inc MA 50,000 Mike Greenwell
I want to be able to read the file and create new excel files for each Agent listed. So for Example, the above file would create 3 separate files since there are 3 different Agents listed. Each Agent file would contain the same information from the original file. The name of the file would be somethign like AgentName.xls...So the SAN group file would have this:
Agent State Exposure Insured Name
Rogers Inc MA 100,000 John Smith
SAN Group RI 200,000 Jim Morrison
SAN Group RI 100,000 Jimi Hendrix
Is there a way to accomplish this in SSIS?
I'm using SSIS to do bulk inserts from fixed width files to about 20 tables in my SQL database.
The problem I'm running into is in creating Format Files for the bulk insert task to use. I've gotten the bcp command to create format files that will read csv files, but I can't seem to figure out how to get it to create one for fixed-width.
I know it can be done: http://msdn2.microsoft.com/en-us/library/ms191234.aspx
At the bottom (Section F) it shows an XML format file for reading a fixed-width file. When I manually create one of these to match one of my tables, the bulk insert worked fine.
Closest I've come is with this ( [] bracketed items are correct values, just censored here):
C:Program FilesMicrosoft SQL Server90ToolsBinn>bcp [database].[owner].[table] format
nul -c -f C:TableFMT.xml -x -S[Server] -U[Username] -P[Password]
My question is, what is the bcp command to create this sort of XML format file?
I have SQL Server 7 database & I need to create some interface between users & database to let users see what is in the database, but not let them change or destroy data. Do I need to create some other database in-between users & the original database? Users can't program & want access to all data.management doesn't want to create a replication of the database. They want to let users have access through MS Word or Access. What could you advise me?
View 1 Replies View RelatedHi,
How can I provide a user read only access to all the databases on the server. I have 15 databases on the server. I know I can give db_datareader access in each database individually but that is time consuming and I have 10 servers for this to be done.
thanks in advance.
The error comes up like this -
System.Data.SqlClient.SqlException .... because the database is read-only
( I'm putting this here so searches can find it easier)
I got this idea from a related thread, but they used a NETWORK user that is obsolete in 2007.
I found that you have to edit the actual file properties on the ASPNETDB.MDF and ASPNETDB.LDF files so that the ASPUSER has full access. i.e. on the file properties, security tab, hit the Add button and type in ASP.NET then return. Back at the security properties, click on the 'Full Control' checkbox at the top. That took care of it for me.
This was after checking the IIS control panel for the properties on the folder that contained my application and App_Data folders to be sure that the "Write" checkbox was already checked, it was
Of course this assumes that you have installed the express version of SQL from Microsoft
We have a production 2012 R2 SQL server that is not part of a domain. I have a database called CSSDC. I need to backup this database and make the BAK file available to a user on a different SQL server. Security is a concern. In order for this user to restore the database, they will have to be in the fixed server role sysadmins. I know I am going to have to use the Move with replace for the filegroups for the restore. Once the database is restored it will have to be altered to read-only. I would also like to remove the existing security and only have this new user access the read-only database.
View 4 Replies View RelatedHi guys,
I want to give someone read only permission to our SQL server so they can log in and look at the database structure and query a few tables. I've set up the user but the problem is that when I connect in SQL server remotley, it shows all the other DB's on the server even though it is restricting access to them. Can these other DB's be hidden so that only the DB the user has access to shows up?
Cheers for your help!