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 RepliesI created a new login and then created a new user [COM] in DB with default schema pointing to [COM]
I created then schema [COM] WITH AUTHORIZATION [COM]
I want this [COM] user to have all permissions it needs on [COM] schema only. How do I do that? When I try to create table [Com].Table it gives me permission denied.
What am I missing?
I am getting the error:
Cannot open database "aspnetdb" requested by the login. The login failed.
When I browse to my ASP.NET 3.5 LINQ web application on the IIS 6.0 server on Server 2003.
I imagine this is because while I granted SQL Server 2005 login and permissions to my database that the application stores its data in, I did NOT grant any rights to the service account the IIS Application Pool uses for its identity to the aspnetdb database on SQL Server which is where all my roles information is stored at.
My question is what are the MINIMUM permissions needed for this database so it can perform its roles related functions?
I'm using Windows Authentications with the SQL Role provider for authorization.
Thank you.
EDIT: I think I only need to open the aspnetdb database and add my login to the aspnet_Roles_FullAccess role. Is that correct?
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 ?
I have been struggling with this one for awhile now.I have a domain group which only must view the steps and history of all agent jobs.I have added the group to the sqlagentreadergroup.I have created a new role and denied this role,add job,update job,delete job etc execute permissions.But the user still can change ,delete or create a new job.
All the groups and users in th new role,does not have sysadmin rights.
we have sql 2012 enterprise version
What else can i try.I need this for audit purposes.
set up asp .net user account on sql server 2005Question:
I've read the instructions in this article: http://www.netomatix.com/Development/aspnetuserpermissions.aspxBut 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!
Hi,
I want to use a domain user account not belonging to local admin or domain admin groups in SQL 2000/2005 Enterprise edition. This is what I've done so far..
On the machine that is the Domain Controller:
- installed SQL 2005 as a domain admin
- created a domain user account using Active Directory Users and Computers. This user is only
"Member of" domain users; not any Administrators group.
- added this user to SQL Server Management Studio->Logins and in Server Roles assigned
sysadmin role.
Question 1: Do I need to give any additional permissions to this user to work with SQL?
Question 2: How can I test this user for basic SQL operations like database creation? Can I use Osql?
Question 3: Can I use this user account to login to my domain controller using remote desktop? I tried adding this user to remote users, but in vain.
Thanks!
I'm running my job from the command line using DTExec as follows:-
DTExec /FILE "C:MyPathMyPackage.dtsx" /CONFIGFILE "C:MyPathMyDtsConfig.xml" /MAXCONCURRENT " -1 "
When I log in as Administrator the package runs perfectly.
When I log in as Domain User (the one I really want to have running the package) I get:-
Started: 10:49:08 PM
Error: 2007-11-30 22:49:08.30
Code: 0xC0011007
Source: {807048F4-DE2A-465E-B9A7-82E163791556}
Description: Unable to load the package as XML because of package does not have a valid XML format. A specific XML parser error will be posted.
End Error
I have checked, and the Domain User has
"Full Control" permissions to the directory the package is in and
"Full Control" permissions for the DTSX file and
"Full Control" permissions to the directory the dtsConfig is in and
"Full Control" permissions for the dtsConfig fileAny suggestions as to what is wrong?
Hi,
We have the followoing:
-A "master domain" AD, a "sub domain" AD, a trust relationship between the two (sub trust master)
-A sql server 2005 on a win server 2003 in "sub domain" AD
-A linked server to "sub domain" AD
-A linked server login using a "sub domain" admin acccount
-A view to this linked server
-A grant on masterDomain/Domain Users to the database
-A grant on subDomain/Domain Users to the database
-We want all connections done through "Windows Authentication" not "Database Authentication".
Queries on the view work fine using "sub domain" user accounts.
Queries on the view fail using "master domain" user accounts (including master domain admin accounts)
"Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "ADsDSOObject" for linked server "ADSI" reported an error. The provider indicates that the user did not have the permission to perform the operation."
All connections are done through "Windows Authentication" not "Database Authentication".
Can we establish cross domain connectivity with "Windows Authentication" ?
Below are details of the implementation:
SELECT TOP (100) PERCENT *
FROM OPENQUERY(ADSI,
'SELECT displayname, givenName, sn, cn (etc...)
FROM ''LDAP://OU=PEOPLE,DC=subDomain,DC=com''
WHERE objectCategory = ''Person'' AND objectClass = ''user'' ')
EXEC sp_addlinkedsrvlogin @rmtsrvname ='ADSI', @useself='false',
@rmtuser='subDomainAdminAccnt', @rmtpassword='sunDomainAdminAccntPassword';
In SQL Server Mngt Studio in Server Objects/Linked Servers/Providers/ ADSI properties security tab I have:
"connections will: <be made using this security context> Remote login:'subDomainAdminAccnt' With password: 'subDomainAdminAccntPassword'
Error:
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "ADsDSOObject" for linked server "ADSI" reported an error. The provider indicates that the user did not have the permission to perform the operation.
Msg 7320, Level 16, State 2, Line 1
Cannot execute the query "SELECT displayname, givenName, sn, cn
FROM 'LDAP://OU=PEOPLE,DC=subDomain,DC=com'
WHERE
objectCategory = 'Person'
AND objectClass = 'user'
" against OLE DB provider "ADsDSOObject" for linked server "ADSI".
I am using Microsoft Visual Web Developer 2005
Here are the steps I used to create my database
Tools -> connect to database -> Select datasourse - Microsoft SQL Server (SqlClient) -> Server name - comp4_8SQLExpress -> Log on to the server €“ Use Windows Authentication -> Connect to a database -> Select or enter a database name €“ MyDataBase
comp4_8 - is the name of my computer. I am using Windows XP Professional SP2. Login as administrator without password.
Now about the problem I have. On local machine everything is great! But I hosted my website and got this error. The question is how to give permission to this user to connect to my database.
Cannot open database "MyDataBase" requested by the login. The login failed.
Login failed for user 'ANGELINAASPNET'.
Need to create a user in sql server provide grants to few system tables to the above user.
View 10 Replies View RelatedHello! I have the following problem. I developed CLR Stored Procedure "StartNotification" and deploy it on db. This sp calls external web service. Furthermore, this sp is called according with SQL Server Agent Job's schedule. On my PC SQL Server works under Local System account and this web service is called correctly (Executed as user: NT AUTHORITYSYSTEM). But on ther other server the following exception is raised during job running:
Date 17.04.2007 16:42:10
Log Job History (FailureNotificationJob)
Step ID 1
Server MSK-CDBPO-01
Job Name FailureNotificationJob
Step Name MainStep
Duration 00:00:00
Sql Severity 16
Sql Message ID 6522
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
Executed as user: CORPmssqlserver.
A .NET Framework error occurred during execution
of user defined routine or aggregate 'StartNotification':
System.Security.SecurityException: Request for the permission of type
'System.Net.WebPermission, System, Version=2.0.0.0, Culture=neutral,
PublicKeyToken=b77a5c561934e089' failed. System.Security.SecurityException:
at System.Security.CodeAccessSecurityEngine.Check(Object demand,
StackCrawlMark& stackMark, Boolean isPermSet)
at System.Security.CodeAccessPermission.Demand()
at System.Net. The step failed.
What is the reason of this behaviour? Unfortunately I do not have direct access to this server.
I have the following guesses:
1) CORPmssqlserver may have not enough permissions to call web service
2) Something wrong with SQL Server account's permissions
2) Something wrong with SQL Server Agent account's permissions
I will take the will for the deed. Thanks.
Hi all
I tried using my administrator account to add SQLServer as an ODBC Connection, but for some reason when it tries authenticating it fails and the reason is that the user is not trusted to use that connection. Yet on my account i've created on my domain works perfectly.
I've added a new user in SQL Enterprise manager, but i still get that error.
How do I set the user to be trusted to use the connection?
Any and as many assists on this would be great.
Kr33
How can I create a script that will set all the permissions for a specific user for some objects in a database?
Thx in advance.
I have a user that I have granted the server role "securityadministrator" and a database role "db_securityadmin". When logged inas this user I can create new logins but not run sp_adduser to add thenew login to as this says I don't have permission to do this. I canhowever run sp_revokedbaccess to get rid of a user from the database.
View 2 Replies View Relatedhello all....can anyone tell me how can i manage the stored procedure permissions in SQL Server Express. Untill now i have developed in SQL Server 2000 and there i used SQL Enterprise Manager for this problem..in SQL Express i can't handle it...Manny thanks.....
View 3 Replies View RelatedHey guys. I'll have an active/active cluster and seperate accounts for SQL Services and Cluster service. The question is what rights should the cluster account have in SQL if I've removed the 'builtin admins' from SQL? Thank you
Cannot open user default database. Login failed.Login failed for user
'NT AUTHORITYNETWORK SERVICE'. 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: Cannot open user default database. Login
failed.Login failed for user 'NT AUTHORITYNETWORK SERVICE'. I have This Error When i try to log into My online web site, i have no idea how to fix it,one day it was working and the next it wasnt, is there any way to find out what database the default is and if it's either incorrect or not present change the web.config in a way that will make my system work. i have the NT Authority/Network Service in my Server Properties Permissions, its given the type login and is granted Connect SQL by sa i have 3 colder copies of the web site on my server my question is, how would i use of of these to restore the original site configuration is there a way to restore the original configuration to undo whatever it is i've done to break the system ChrisStressed
Hey guys,I'm pretty new to SQL configuration, and I need to give EXECUTEpersmissions for one of the SQL user roles. I am running SQL 2005Management Studio Express - free version. I found the list of mystored procedures, but I can not locate any permissions screen. Cansomeone help point me in the right direction? Thanks!
View 7 Replies View Relatedhi,
I created an application using VB.NET, which performs accessto SQL database. the server is MS SQL 2000.
I got a few questions:
a. The application performs access to tables, performs SELECT transactions, and calls stored procedures. I want to define a userlogin that is only permitted to commit UPDATE and INSERT only by the stored procedures, and not by direct commands. Is that possible ? Do I have to deny access to READWRITE in the tables? If I do deny - will the user be permitted to call stored procedures that performs the INSERTUPDATE those tables ?
b. Do I have to create a LOGIN or a USER for the specified requirements ?
c. Where can I find REALLY detailed information about what I need to create (login, user, role ect.) ? I find only general stuff...
d. The user-defined role I need to create - is it considered as an application role (because it grants an application access to the database) or is it called SERVER ROLE ?
Thanks,
Dror.
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 to find All Permissions in the Current Database for a particularUser in SQL 2005 ?ThanksM A Srinivas
View 1 Replies View RelatedI'm trying to get specific permissions per user on each SQL database, would you let me know if it could be achieved e.g on an object level?
View 5 Replies View RelatedMy company doesn't allow using Local Service / Network Service accounts for SQL Server. So I created domain service accounts. Can multiple SQL Server installations use the same domain service accounts ?
View 4 Replies View RelatedI don't like the fact that any user I create has access to system tables inthe Master databases and the ability to issue 'sp_who' plus God knows whatother system stored procs.Is there a white paper or a script to lock down a user so that they do nothave this broad access without breaking their regular ability to run theirown queries against their own databases? I would like them to be a regularuser without the ability to snoop around in my SQL Server.--John Dalberg
View 3 Replies View Relatedam working on asp application which basically is an interface to the report server. I am currently unable to determine the source of the problem so it may have nothing to do with reporting services.
View 4 Replies View RelatedI am working on asp application which basically is an interface to the report server. I am currently unable to determine the source of the problem so it may have nothing to do with reporting services.
View 11 Replies View RelatedHow 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 all,
I do understand that it is highly recomended to have aserprate user (perfered a domain user account) for each of the SQL Server service and SQL Agent service.
What is the reason behind that? (Someone told me to not run the service with an account that has a powerul privilegs! - I don't undrstanmd this point can you explain it please?)
What is the diffrent between: 1- Local System account 2 -Network Service account
Thanks in advanced!
CS4Ever
Hi,
My server administrator has created a few users as domain user privilege for connecting SQL server from my application. However it faild to connect, and
with Domain Admin privilege, my app is running ok.
I asked admin person to upgrade domain user privilege to connect SQL server,
but he couldn't do this for security reason, and told it should work with domain user.
Is it true for domain user to connect SQL server without any problem?
Do I need to add the user, domain user, into Logins of Security inside SQL server Enterprise Manager?
Or inside users of my database section of SQL server EManager?
Thanks,
dixon
Hi,
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.
As part of our security project, I've done the following when logged in as 'sa':
Created database roles 'dbrole1' within dbAccount
Created login and user 'user1' and added user to be a member of 'dbrole1'
Granted execute permissions on sp1 and sp2 to 'dbrole1'
However, I didn't see the above permissions listed in SQL Server Management Studio - Database - Security - Roles - Database Roles - 'dbrole1' properties - securables
Any ideas? Thanks!
My SQL Server 2005 runs on a local account. Is it neccesary to assign this login in SQL to a System Administrator role?
And is there any difference in SQL Server 2000?
thanks
Przemo