set up asp .net user account on sql server 2005
Question:
I've read the instructions in this article: http://www.netomatix.com/Development/aspnetuserpermissions.aspx
But do not know how to do this:
You can grant 'Network Service' or 'ASPNET' user accounts permissions to connect to database.
Please provide example on how to do this, thanks!
I have a larger stored procedure that is running, but I am getting stuck on where I need to grant permissions to a user in a a different database on various functions and stored procedures. For example:
Code Snippet
use [Database1]
grant exec on [Database2].[dbo].[MyFunction] to bob
returns this error: Cannot find the user 'bob' , because it does not exist or you do not have permission.
However, I know 'bob' exists, plus when I change the use statement to Database2, the line of SQL works correctly. Given the nature of the overall stored procedure this will be running in, I won't have the ability to just change the use statement. Is it possible to grant permissions to a user on a different database without explicity having the use statement set to a particular database?
Thanks for any advice!
-Flea#
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 RelatedHi,
I created a database,login,user and schema like belows.
-- 2. create database
CREATE DATABASE MyTempDatabase;
-- 3. create login
CREATE LOGIN MyTempLogin WITH PASSWORD = '#mytemplogin$',
DEFAULT_DATABASE = MyTempDatabase,
CHECK_EXPIRATION = OFF,
CHECK_POLICY = OFF;
--
USE MyTempDatabase;
-- 4. create user
CREATE USER MyTempLogin FROM LOGIN MyTempLogin WITH DEFAULT_SCHEMA = MyTempSchema;
-- 5. create schema
CREATE SCHEMA MyTempSchema AUTHORIZATION MyTempLogin;
The created user,MyTempLogin, must have permissions that can create tables,drop tables,select,insert,delete,update and bulk insert.
How can I grant permissions to the user?(or schema?)
I failed to grant by T-SQL query.
Additionally, what is purppose of the ROLE? Should I create or use it?
I'm confusing in security concept(login,user,schema,role).
Thanks.
I've been trying to workout how, without impersonation, I can allow an application running under the default ASPNET user to access a network resource (SQL Server/ADAM) not on the same physical machine.
It seems that because the ASPNET user is a local user not a domain user I can't setup windows authentication on the network resource. That seems to leave impersonation which MS et al say is unwise.
Have I mised something here?
Regards,
Steve.
In SQL 2005, is this an acceptable (prefered) way to give an application account EXEC permissions for sprocs and funcs in a specific database?
CREATE ROLE db_executor
GRANT EXECUTE TO db_executor
And then of course assign my user to this role on the database level.
I am trying to get away from adding exec to every sproc "manually" and then of course also having to add exec for any new sprocs that get added into the database.
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
Hi,
I wanted to deploy my Sql 2005 reports to my local machine and want it to get viewed by all the users through Asp.Net application.
User can directly view the reports by clicking on the direct link to report but when they try to view it using application having Report Viewer, running on iis it gives acess denied 401 error and can't view the reports
If I make my application to run on default port then it works fine
If I give rights to <particularMachineNameASPNET> then that user can view the report but if there are 100 users then will i Add 100 such entries???
I think I am making mistake in this case.
Any response will be appriciated.
-Thanks,
Digs
This may be an idiotic question:
I am attempting to use Visual Web Developer Express with a connection to a SQL Express db from a non-admin account on my XP Pro SP2 machine.
I can do everything in the app under an admin login, but can't seem to configure the db to allow the non-admin account access to the db. I've tried tweaking WMI, using Network Service, Local Service, and Local System with NT AUTHORITY, individual logins, and group permissions, but I'm stuck.
Any thoughts?
After SQL Server 2005 Database Engine is installed by domain administrator, how to give permissions to a regular domain user so that user can control SQL Server Database service?
View 3 Replies View Relatedi have a sql cluster setup, and need to change the user account that sqlserver starts with....any ideas? i screwed up and left it using localsystem account and now i can`t get sqlmail to work. i`m trying to avoid having to create the cluster again. any info appreciated.......jim jones
View 1 Replies View RelatedThis has been extremely confusing for me.
I want to just make a simple backup.
first of all when i choose the pick a folder to backup, no mapped drives I make are even THERE.
I realize this is probably related to the account being used, okay I thought let me change the user account to a network admin account... I still cannot see the drive.
Can't this thing just accept whatever I tell it to access like any other program??
You would think they would at least keep the standard Open File dialog so we can use the network browser or something...
I've changed my accounts all to NETWORK SERVICE, then LOCAL SYSTEM, then a DOMAIN ADMIN...
I can't get this to work correctly on this freshly installed server... can someone please help?
I'm at the point where I don't care if i have to just re-install the damn thing...
Just someone please tell me what to pick for the accounts.
Bonus: I have this same issue with reporting services and Services for Unix NFS Mapped drives.
How can I map a drive with NETWORK SERVICE Credentials so it finds the datasource path?
I've only been able to do something like this with psexec and Local System.
When logged in as Domain Admin it will show a disconnected network drive that you cant get rid of but system account can use.
I have a root domain and child domain.
After using ADMT to migrate the domain user or group into the root domain, when I use enterprise manager to try and change the permissions allocated to that domain user/group, i get the 'Error 15401 NT user or Group not found'.
This is a correct error as the user is now in the root domain, however sql (in sysxlogins) still thinks its in the child domain.
Is there a simpler way, other than collecting the users permissions, deleting the user from SQL then adding back in with the correct domainusername format, then adding the permissions back?
I tried renaming the 'name' in sysxlogins (not recommended) and while that worked, whenever I tried to add the migrated user to another database, the login name was missing and would not resolve.
I believe it is something to do with the SID not matching.
Any ideas on how to fix this ?
hello all,
I would like to grant the MACHINENAMEASPNET user all acccess to my MSSQL database (i.e tables and stored procedures. I can do this through enterprise manager but id rather do it programatically so i can add it to my database creation script. What is the syntax for this?
I thought it would be along the lines of:
Grant all on databasename to localhostASPNET.
But obviously its not! Please help!
thanks
Tom
Do we still need the below service accounts in SQL 2008+ version even if we have proper SQL service accounts added in the logins?
[NT AUTHORITYSYSTEM]
[NT ServiceMSSQLSERVER]
[NT SERVICEReportServer]
[NT SERVICESQLSERVERAGENT]
[NT SERVICESQLWriter]
[NT SERVICEWinmgmt]
hii all;
My question is from 2 sections:
1- Is it secure to put user accounts (userName and password ...) on the database?
2- How can I set username and password for SQL server 2005 express file?
Thanks
SQL script that creates a User Mapping for NT AUTHORITYNETWORK SERVICE to a database?
View 3 Replies View RelatedI am trying to start ms sql server 2005 express as a network user from originally starting as local system. I cannot start the service.
I have given this network user administrator access to sql express from the sql server surface area configuration "Add New Administrator". I have went into the local users/groups and added this network user to the 3 security groups
SQLServer2005MSSQLServerADHelperUser$SQL1
SQLServer2005MSSQLUser$SQL1$SQLEXPRESS
SQLServer2005MSSQLBrowserUser$SQL1
I receive one alert int he event logs/application
EventID: 26010
The server could not load the certificate it needs to initiate an SSL connection. It returned the following error: 0x8009030d. Check certificates to make sure they are valid.
I receive five errors in the event logs/application
EventID: 26014
Unable to load user specified certificate. The server will not accept a connection. You should verify that the certificate is correctly installed. See "Configuring Certificate for Use by SSL" in Books Online.
EventID: 17182
TDSSNIClient initialization failed with error 0x80092004, status code 0x80
EventID: 17182
TDSSNIClient initialization failed with error 0x80092004, status code 0x1
EventID: 17826
Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately prceding this one in the error log.
EventID: 17120
SQL Server could not spawn the FRunCM thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.
I don't recall assigning any sql specific certificates. The reason I'm trying to run this as a network user is because an application on a remote windows server requires it.
After starting the service back running as local system as it was before, I went into the
SQL Server Management Studio Express and edited the properties for this new network user I wish to run the service as. I gave him full access including checking all grants in securables.
I now see a bit of a difference in the event logs with these errors.
EventID: 17058
initerrlog: Could not open the log file 'c:Program FilesMicrosoft SQL ServerMSSQL1MSSQLLOGERRORLOG.' Operating system error =5(Access is denied).
I saw a notification when changing the sql server service's logon that the new network user has been granted the log on locally right.
The second and last new error is
EventID: 17053
UpdateUptimeRegKey: Operating system error 5(Access is denied.) encountered.
I'm using the methods of the Microsoft.SqlServer.Management.Smo namespace within a .NET application to create a backup file from a SQLEXPRESS database. I can then restore the database from that backup device using methods in the same namespace. Here is a snippet from the restore code:
srv = New Server("MYPCSQLEXPRESS")
db = srv.Databases("washmaster")
Dim bdi As New BackupDeviceItem(BackupFileName, DeviceType.File)
Dim recoverymod As RecoveryModel
recoverymod = db.DatabaseOptions.RecoveryModel
rs.NoRecovery = False
rs.Devices.Add(bdi)
rs.Database = "washmaster"
rs.ReplaceDatabase = True
srv.KillAllProcesses("washmaster")
rs.SqlRestore(srv)
This works great as long as I used one of the backup files that I created directly on the disk. However, my application has a utility that allows the user to copy the backup files onto another drive, such as a CD or a thumb drive and when I try to restore from the copy of the backup, I get the following exception:
....Cannot open backup device..[filename]...Operating system error 5(Access is denied.)
The reason I get this error is that the "NETWORK SERVICE" account was removed from the file permissions when the file was copied.
How can I copy a backup to another drive and preserve the "NETWORK SERVICE" account? If I can't do that, is it wise to try to add the account back to the file before using it to restore or is there a better way?
Thanks,
SJonesy
Can somebody tell me without pointing to any other link how to grant Create Procedure permission to DB user.
I'm trying to grant a user group select access to all the views in a database. I already made a query which creates a result set whit the SQL Syntax I (displayed below) need but it seems to be impossible to get this result set executed after creation.
use [AdventureWorksDW2008R2]
SELECT
'GRANT SELECT ON [' + SCHEMA_NAME(Schema_id) + '].[' + name + '] TO [DOMAINGROUP]'
FROM sys.views;
I am currently hardening our SQL 2012 (with AlwaysOn Availability Groups) environment. Both the SQL service and agent account are using service accounts (only domain user). SQL browser service is disabled. Permissions to all roles are handled by using domain groups.
Currently a lot of (default) NT Service accounts are listed (some with sysadmin privileges). Are there accounts that can be removed?
Hello,
In our project, we would like to use the same data source for our analysis service database cubes and for our reporting service reports.
I created the analysis service project first, deployed successfully. When trying to setting up the data source in the report model project, I selected the "create a data source based on another object", and then selected the "create a data source based on an analysis service project". However, there is no analysis service project to select, and no browse button to see where the reporting service is looking for analysis service project either.
I have tried creating a new analysis service project with data source views, cubes, dimensions and all the stuff, but still cannot see the analysis service project in the drop down box to be selected for my reporting model project data source.
As I am fairly new to the reporting service, I'm sure I'm missing something, but couldn't find much information in the help or on the web. Any suggestion would be much appreciated.
Thank you very much,
Annie
Ive created a DAL called Artist.xsd. Ive used stored procedures to access the data. The wizard created a stored procedure called 'dbo.ArtistSelectCommand' Ive granted the ASPNET account execute permissions on this stored procedure When I run the application and try to execute the stored proc, I get this error
EXECUTE permission denied on object 'ArtistSelectCommand', database 'EBSNet', owner 'dbo'.
as far as im aware ive givne the ASPNET account the correct permissions
EXEC sp_grantlogin [MachineNameASPNET]
EXEC sp_grantdbaccess [MachineNameASPNET], [ASPNET]
** Ive also done this by selecting the stored proc in sql server 2005 and setting permissions by right clicking and selecting properties.
Is there anything else I need to do ?
I have 4 new SQL Server 2005 installations on Windows 2003 that I configured at our main office and shipped to a hosting center. All four servers are members of our domain. I set up test datbases with replication on one of the servers and facilitated this with a domain account.
Now that I've moved the servers to the hosting center (which has a DC) and I'm not having any luck adding domain accounts to the permissions section on any of the the SQL Server boxes.
When I try to add a domain account in the SQL Server's permissions window I get "Name Not Found". By every indication the server is connected to the domain. I can log on using my domain account; I can create shares specifying domain accounts but I can't seem to add domain accounts to the SQL server permissions. When I look in the permission's tab I still see the original domain account, I had added back in the main office, stranded by itself in the list of users. We're using mixed authentication by the way.
Why doesn't SQL Server recognize the domain? Where does it get it's list of users? Does the account I'm logging in with just not have the permission to add domain accounts? These diaglogs are slightly different from the normal 'add a user' dialog boxes.
I feel like this must be a simple oversight. Any help would be appreciated. I'd prefer to move away from local accounts to keep things simple.
We have a monitor tool to monitor our SQL servers.The vendor has a script to grant permission to application account to SQL server so that they can do the monitoring.
I don't want to grant system admin role, but the script they have is very specific, the minimum is:
must be a member of db_datareader role on the msdb database
must have view server state permissions
view any definition
connection permission to master database
execute permission on the xp_readerrorlog stored procedure
connect permission to the msdb database
must be member of db_Datareader role in the msdb datab ase
connect permission to all databases.
The script given by vendor is as follows in the attachment.I don't like the last execute statement for if a new database is created, we have to remember manually add the monitoring acccount to that database.
I know in SQL 2014 it has a new feature of connect to any database, but unfortunately we are using SQL 2012 and 2008.
script to connect to any database but no need to add that each time we creates a new database.
Hello,
Ho do I allow multiple users to share a database?
Background
I have developed a Windows App in VS.NET 2005 which connects to a SQL 2005 Express database.
Now I want to install the app and database on the network and I am getting an error "File 'file_name' is on a network device not supported for database files"
What is the best way to get this working
Thanks in advance,
Phil
I recently created a program that connects to a Microsoft SQL database that was stored on my computer and it worked fine. As soon as I tried to connect to the same database via a network drive I got an error stating that "The file Y:Filename.mdf is on a network path that is not supported for database files.". I can't seem to get it to work, if anybody has any ideas what I'm doing wrong I would appreciate your help.
Don
Hi all,
Can I grant select only permission on all objects in the database? I have users that I need to give view access only on stored procedures, triggers, and functions. Thanks.
I am trying to write a SQL script that grants execute permissions on all stored procedures for a single user (test_user).
I am running SQL server 2005
Does anyone have a script to do this.
Many thanks
Hi all,
Iam getting this error
System.Data.SqlClient.SqlException: Cannot open database "XYZ" requested by the login. The login failed.Login failed for user 'xyz-abcASPNET'.
when trying to open the page....
http://localhost:1807/projectname/WebFormName.aspx
as
http://localhost/projectname/WebFormName.aspx
Couldnt figure out the solution.Please help
Soujanya
I'm trying to harden our SQL Server environment to improve overall security of our systems. I would like to check what specific permissions are granted to SQL Server, SQL Server Agent and SQL Server Integration Services account. I've tried with the "sc qprivs <service_name> " command, but it returns an empty privilege list. Is there a way to retrieve the permissions using a cmd or powershell command?
View 2 Replies View RelatedIt seems that SQL7 has lost the "Grant All" option previously available in SQL 6.5. Is anybody aware of an equiviant in SQL7?
Any help would be gratefully received.