Permission For Linked Server User
Aug 30, 2007Dear,
What minimum permission do i need to grant to a Remote Login to create a linked server.
Regards
Sufian
ADVERTISEMENT
User On Trusted Domain Does Have Permission To Access Linked Server On AD Deployed In Another Domain
Sep 28, 2007Hi,
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".
Linked Server And Permission
May 14, 2008Hi
I need to add a linked server .I am able to add it using SA account however if i use any other account i am unable to do so.
My question is what permisson does a ordinary user requires to add a linked server .
regards
Hrishy
How To Grant Sysadmin Permission To SQL Server User ?
Jul 26, 2001Hi,
I'm trying to run the Bulk Insert statement but in order for me the run it, i need to have the sysadmin permission. Can someone show me how to grant sysadmin permission to my SQL Server user? This is really urgent. Thank you in advance.
Getting Permission For User Sa On SQL Server Express 2005
Oct 16, 2007I originally installed SQL Server Express 2005 on my computer using Windows Authentication mode, and discovered when I tried to add another user/login that I didn't have permission to do so. This is rather odd as the windows account that I installed SQL server with is the system admin for the computer.
I have successfully changed the login mode to mixed, and have tried to login in as "sa", but it appears that "sa" was given some sort of password (did SQL server automatically generate one?), and I don't know what it is. When I go into command prompt and try to change the password, it says that it cannot alter the login 'sa' because it does not exist or I do not have permission (i'm pretty sure it's the later, as 'sa' shows up on the list of logins in SQL server express).
I'm so stuck! Please help!
SQL Server 2008 :: Grant User Full Permission Within Schema Scope
Jul 13, 2015The requirements are:
1. the user has read-only permissions to dbo tales.
2. the user can do everything within the rpt schema, which contains all objects analyzing dbo tables.
3. the user does not have any permission outside rpt schema, except permissions in #1.
The current solutions are:
1. grant the user select only on dbo tables.
2. make the user the owner of rpt schema.
3. Grant the user database permission on create table/create procedure/create view/create function.
My question is - in step 3, should I just grant "Alter" database permission to the user? Granting Alter seems to be cleaner and simpler. According to MSDN,
"Alter" confers the ability to change the properties, except ownership, of a particular securable. When granted on a scope, ALTER also bestows the ability to alter, create, or drop any securable that is contained within that scope.
What Permission I Need To Grant To A User If He Need To Read Or Write A Link Server Tables
May 31, 2007hello,
What role or system privilege do I need to grant to a user if he need to read the data from a table which is in a link server object? where I can find the document about these commands.
Thanks
SQL Security :: How To Copy Permission Of A User To Newly Created User
Oct 24, 2015How can i assign permissions to a newly created users as of an existing user?
View 3 Replies View RelatedSQL Server Admin 2014 :: How To Grant User Permission To View Specific Views
Aug 5, 2015I have a user who needs access to views like(dbo.viewnameabc1,dbo.viewnameabc2 and so on...) dbo.viewnameabc* and anytime the user creates the view he already have the permission to view those views....
View 3 Replies View RelatedCannot Debug Stored Procedures Because The SQL Server Database Is Not Setup Correctly Or User Does Not Have Permission To Execute Master.sp_sdidebug.
Aug 7, 2007Hello All,I tried to set the access permissions for debugging stored procedure by reading the articlehttp://msdn2.microsoft.com/en-us/library/w1bhybwz(VS.80).aspxandhttp://technet.microsoft.com/en-us/library/ms164014.aspxI have tried to add the role to sysaminas follows1)SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'sp_sdidebug'(to find the sp)Error:--The stored procedure not found2)sp_addsrvrolemember 'Developmentswati.jain', 'sysadmin' though this is executed successfuly . Error is still persisting
Cannot debug stored procedures because the SQL Server database is not setup correctly or user does not have permission to execute master.sp_sdidebug.
SQL Server - Permission Issues : Execute Permission Denied On Object 'SprocName'
Dec 13, 2005I have an application that uses Integrated Windows authentication. My Web.config looks like below
<add key="dbconnection" value=" server=XXX;Initial Catalog=XXX;persist security info=False;Integrated Security=SSPI;Pooling=true" />
When users try to access my application, they get the below error:
Execute permission denied on object 'SprocName', database 'DBNAME',Owner,'dbo'
The Only way I could get rid off the error is if I set DBO permissions for the user group on the databse.
Can someone suggest how to set up a security group with the ‘necessary’ permissions on SQL SERVER (ie read,write execute Sproc etc) and not too many extra ones, like DBO.
Thanks,
SQL 2012 :: Allow Authenticated User To Query Excel File Via Linked Server?
May 25, 2014I have a 3rd party dashboard application that I can only use SQL authenticated logins to connect to the database.
I'm trying to create a query within the application that will directly access an excel file through a linked server.
As a test, I login to SSMS as the sql auth user to run the linked server query below but the following error is returned:
select *
from Corporate...[Sheet1$]OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "Corporate" returned message "Cannot start your application. The workgroup information file is missing or opened exclusively by another user.".
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "Corporate" reported an error. Authentication failed.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "Corporate".
When I login as a Windows auth user, I can successfully run the above query.
I noticed within the linked server's security definition that I cannot specify a windows auth user as the mapped Remote User or as the Remote login
I've tried creating a Credential object with the identity of the windows user and assign that object to the sql auth user but to no avail. I still get the same error
I am using SQL Server express so the option of an automated server agent job to import the excel file is not available.
Details:
SQL Server Express 2012
Office version: Excel 10
Provider: Microsoft.ACE.OLEDB.12.0
User Permission
May 6, 2007Hi,
I grant read/update/insert permission on the speicific columns of the table on the users. I want to write the T-SQL to retrieve what permission/column permission does the user have.
Set Up User Permission
Mar 13, 2008
Hi,
I am new to reporting service. I set up a couple reports and deployed them to Report Manager. I then add my colleague's Windows user account to the report folder so she can view the reports. When she tried she got Login failed error. I have to go into the SQL database and manually add her account into the Login and assign permission to her for each database or stored procedure involved. Then she can view the reports. We do want to control who can view what reports and later hopefully who can view which part of a report. But do I have to manually give permission to each user account/or group in the SQL server? There got to be a better way to do this I feel. Can we use just one account to talk to the database and let what we set in report manager control who can view what? Thank you very much for your help in advance.
Login Failed For User 'NT AUTHORITYANONYMOUS LOGON' For Insert Reference Using New Linked Server
Mar 23, 2006I get this error when trying to alter a stored procedure that has an insert statement referencing a new linked server I created:
INSERT INTO [servername].databasename.dbo.DirectReport
...
Msg 18456, Level 14, State 1, Procedure Get_Direct_Pay_Move_Data, Line 17
Login failed for user 'NT AUTHORITYANONYMOUS LOGON'.
I added Administrators and my logon to the permissions of my linked server but still get this error when it tries to save my stored proc...the one which has that insert.
User Permission Needed
Jun 7, 2001Hi all,
I need to run a query that would return all users with sa privileges at a server level. Sysusers has the db users for each individual databaase. But I need to list all sa users and the databases they have rights to. Looks like I may need a cursor...not sure. If any of you can help me build this query, I would really appreciate it. Thanks.
Restoring User Permission
Sep 10, 2000I have a nightly job that restores a production database to a reporting server. I have some report- related stored procedures
that need to run on the reporting server once the database is restored. However, during the restore, the permissions for the
reporting stored procs get deleted and replaced with the production permissions, which are useless in this case.
Is there a script or system stored procedure I can schedule to run after the nightly restore that will also restore user
permissions? Thanks, Frank.
User Permission List
Apr 28, 2006Hi, Does anyone has a script to get user permissions on all tables?
Thanks
How To Grant Permission To User?
Apr 25, 2008Hi,
I newly created one database (using creat database testdb ). After that i created login name and password for that database ( using create login login1 with password = 'pass1'; use testdb; command) and i created user for that login name ( using create user user1 for login login1 command).
Then i connected testdb database using login1. But when i trying to create table in that database, it thrown error. Anyone please tell me that how to assign all privileges to the user user1?
Sathish kumar D
User Does Not Have Permission (error 15247)
Apr 1, 2007Hi,I have installed sql server 2005 express on a Windows xp system with windows authentification. I did this with my administrator account (dan). When i start sql server management studio, i see: user name: myserverdan .Now i want to create a new login, so i rightclick on 'logins' in 'Security' of 'Database'. I take an existing windows-account, but when i click on OK, i get the error: user does not have permission to perform this action (error 15247).I also tried to delete a login, but same error.Any help would be appreciatedT.
View 2 Replies View RelatedUser Does Not Have Permission To Perform This Action.?
Aug 2, 2007I am trying to use a query analyzer to perform a task and I keep getting this message....how do I get around this?
View 1 Replies View RelatedLogin/User Permission For App Developers
Mar 18, 1999Hello Everyone,
I need your input. When, if ever, should programmers be given the 'sa' login. The reason that I ask is because a group of three developers have approached me asking for the 'sa' login so they could write stored procedures and do some database level tasks. I am relatively new to data admin and I am under the opinion that I should probably just alias their login to 'dbo' on the one database that they are developing their app for.
What do you guys think about this.
Thanks in Advance,
Terry
User (guest) Does Not Have Permission To Run DBCC
Jan 15, 2015I try to attach a database mdf file to Microsoft SQL server 2014 on Amazon Elastic Computing Cloud, EC2, but fail with the following message, "User 'guest' does not have permission to run DBCC checkprimaryfile. (Microsoft SQL Server, Error: 2571)" The ID I use to REMOTE login has administrator rights and I have chosen to "run as administrator"
View 1 Replies View RelatedScript To Generated The User And Permission
Oct 3, 2005Here is the simple script, which generated the user and user permission and can be transferred to another database. I think it may useful for new DBA. I just wrote it 2 days ago.
-----------------------------------------------------------------
declare @login varchar(20),
@db_user varchar(20),
@role varchar(30),
@role_mem varchar(30)
print 'USE '+ db_name()
print '-------------------------------------------------------------'
print '--Granting current DB access'
print '-------------------------------------------------------------'
declare add_user cursor for
select b.name as 'login', a.name as 'db_user' from sysusers as a inner join master..syslogins as b
on a.sid = b.sid and b.name <> 'sa' order by a.name,b.name
open add_user
fetch next from add_user
into @login, @db_user
while @@fetch_status = 0
begin
print 'exec sp_grantdbaccess '+''''+@login+''''+','+''''+@db_user+''''
fetch next from add_user
into @login, @db_user
end
close add_user
deallocate add_user
print '-------------------------------------------------------------'
print'--Adding role'
print '-------------------------------------------------------------'
---------add role
declare add_role cursor for
select name from sysusers where issqlrole =1 and gid <> 0
open add_role
fetch next from add_role
into @role
while @@fetch_status = 0
begin
print 'exec sp_addrole '+''''+@role+''''
fetch next from add_role into @role
end
close add_role
deallocate add_role
----------add role members
print '-------------------------------------------------------------'
print '--Adding role members'
print '-------------------------------------------------------------'
declare add_role_member cursor for
select g.name as 'role',u.name as 'name'from sysusers u, sysusers g, sysmembers m
where g.uid = m.groupuid and g.issqlrole = 1 and u.uid = m.memberuid and u.name <> 'dbo' order by g.name,u.name
open add_role_member
fetch next from add_role_member
into @role_mem,@db_user
while @@fetch_status = 0
begin
print 'exec sp_addrolemember '+''''+@role_mem+''''+','+''''+@db_user+''''
fetch next from add_role_member
into @role_mem, @db_user
end
close add_role_member
deallocate add_role_member
Sanjeev shrestha
The User Does Not Have Permission To Perform This Action.
Sep 14, 2006hi,I am getting the following error while trying to add a remote server.The user does not have permission to perform this action.The command i used is:exec sp_addlinkedserver 'remote_server_name'What is the solution?Regards,Kalaivanan.
View 1 Replies View RelatedThe User Does Not Have Permission To Perform This Action
Jul 19, 2007Hi All
I kind of hit the wall with this message.
Here is what I do:
select * from sys.dm_db_index_physical_stats(24,null,null, null, null)
My server is 64 bit 2005, sp2.
That's the message I get.
I am loging int as Administrator.
Does anybody know what needs to be done?
User Does Not Have Permission To Perform This Action. Problem
Jan 31, 2007hey people having a nightmare getting asp.net 2.0 to work with sql server 2005 express, bit of a newbie with it. trying to display a table from my sql database and every time i run the aspx table im getting this error. User does not have permission to perform this action.
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: User does not have permission to perform this action.
Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
Stack Trace:
[SqlException (0x80131904): User does not have permission to perform this action.]
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +734995
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1838
System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) +33
System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +628
System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +170
System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +130
System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +28
System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +424
System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +66
System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +496
System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +82
System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105
System.Data.SqlClient.SqlConnection.Open() +111
System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +121
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +137
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +83
System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +1770
System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +17
System.Web.UI.WebControls.DataBoundControl.PerformSelect() +149
System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +70
System.Web.UI.WebControls.GridView.DataBind() +4
System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +82
System.Web.UI.WebControls.CompositeDataBoundControl.CreateChildControls() +69
System.Web.UI.Control.EnsureChildControls() +87
System.Web.UI.Control.PreRenderRecursiveInternal() +41
System.Web.UI.Control.PreRenderRecursiveInternal() +161
System.Web.UI.Control.PreRenderRecursiveInternal() +161
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1360 keep getting the user permission error, even though it says on the management console that i have access to the database any ideas? rob
How To Grant User Permission To Create Other Users?
Jun 26, 2007There'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.
Error:User Does Not Have Permission To Perfor This Action.
Feb 7, 2006I just installled the Visual web developer - express addition. When I try to add aSQL database to my project it gives the following error - "User does not have permission to perfor this action" . Can someone please help?
View 4 Replies View RelatedCreating 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
How To Give Permission To This User To Connect To My Database???
Dec 1, 2006I 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'.
Will User's Permission Be Overriden By Database Role?
Dec 26, 2006in SQL server 2005, Database User's permission will be overriden by the database Role's permission or ottherwise? For example, a userA is owner of table AA so it has all permisions on table AA but the user is a member of GroupB but group B has no permission to access to Table AA. What happen on User A?. has it permission to access to table BB or not? How can I find document or example about this?
Please help me, thanks so much
Cannot Add Select User Permission For A Person On A Table
Feb 24, 2008
Hello,
I am trying to grant solely the SELECT privilige to a particular user and to a particular database.I am using Sql server management studio express to do this and SQL Server 2005.
I have created a new login and set the server roles to public, then on the user mapping page, I have checked the box next to the table I wish the user to access and have selected the database membership role - public.
Next, On the Table Properties, I have added the user on the Permissions Page and Selected "Select" under the grant column
When I click on effective permissions, the select command does not show and when I try to login with that user, the table does not display.
What I am doing wrong?
Thanks