SQL 2012 :: Permissions For Connect Any Database?
Feb 24, 2015
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.
View 5 Replies
ADVERTISEMENT
Feb 18, 2008
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!
View 2 Replies
View Related
Feb 24, 2014
When opening .sql files, I get a connect to database engine prompt every single time. how to stop this from prompting vs. just using my current active connection?
View 4 Replies
View Related
Oct 17, 2014
I designed the AlwaysOn wrong, but every time we fail over from primary server to another server, my applications cannot connect because the sql logins cannot connect to their default databases. Once I run the command to link the login with the user in the default database then the users are able to connect. Did I do something wrong when designing AlwaysOn?
View 9 Replies
View Related
Jul 3, 2007
Hi All,
i was ask to grant a developer permissions to use integration services.
he can run the ssis packages and it works fine, but he cant open integration services with sql management studio.
if i am adding him to the local administrators group on the server it works, but he is not a system administrator.
how can i connect to the integratin services without been a local administrator on the server ???
View 3 Replies
View Related
Jun 27, 2006
We are trying to take advantage of the new Security Context for SSIS but users are unable to connect remotely to the SSIS Service unless they have been added to the Administrators Users Group on the server. I have tried adding them to Guests, Power Users, Remote Desktop Users, Users and SQLServer2005DTSUser$machine but the user is unable to connect.
The message received when the user is unable to connect is:
Cannot connect to 192.x.x.x
Additional Information
-> Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
-> Connect to SSIS Service on machine "192.x.x.x" failed:
Access is denied
What is the list of permissions and privileges that a user that does not belong to the server's Administrator group and have sysadmin server role, must have to allow them to create and run a package through SSIS.
View 6 Replies
View Related
Jul 2, 2014
I have an availability group with read only replicas, readable secondary set to yes and allow all incoming connections. I have also configured the read only routing (at least I'm pretty sure this is correct).
If I login to SSMS with a user in the sysadmin role I can view the objects in the read only replica database. If I login with a user in the public role I'm unable to get past the obvious error:
"The database databaseA is not accessbile. (ObjectExplorer)"
I've also tried adding the "ApplicationIntent=ReadOnly" option.
View 4 Replies
View Related
Nov 19, 2014
I need to grant create cubes to developers, but if I include them into server administration role, then they could delete or modify all the databases.
Each developer require full administration on their own databases, but not other developer's databases.
View 0 Replies
View Related
Jan 15, 2015
I am logging into a SQL instance to run the following query:
DECLARE @ReturnCode int EXECUTE @ReturnCode = [master].dbo.xp_create_subdir N'sharemasterFULL' IF @ReturnCode <> 0 RAISERROR('Error creating directory.', 16, 1)
The share in which the folder is to be created has my account added with full permissions to create files. However this command fails unless I add the SQL Service account user with rights to the folder also.
Is this expected behaviour, is this something specific to extended stored procedures?
View 0 Replies
View Related
May 28, 2015
We have a process that uses the following method to move data quickly:
TableA = 600million records
TableB = 0 records
To "move" data from TableA to TableB
Rename TableA --> TableA_HOLD
Rename TableB --> TableA
Rename TableA_HOLD --> TableB
The problem with this is that after every rename, permission to the TableB is lost so we have to execute a statement to grant permission back to TableB after the process is complete.
My question is why is this necessary? Is the permission tied to the table in such a way that it can't use the name only?
View 9 Replies
View Related
May 29, 2015
I've got a Server setup at work on our RD domain
It's Windows 2012, running SQL Server 2012 with reporting Services
I've installed our application and reports to this server.
If I'm on my CORP domain on my development laptop I can enter the following into IE [URL] ...... and the SSRS reports page opens up fine
I then RDP into this server using my same CORP credentials Open IE on this server and enter the same URL within IE and get User 'CORPORATEjoep' does not have required permissions. Verify that sufficient permissions have been granted and Windows User Account Control (UAC) restrictions have been addressed.
I'm on the Server itself. Why does my account not work there, but when I access SSRS from outside this SERVER with same login, it works
View 1 Replies
View Related
May 6, 2014
Only to a specific schema? Can this be done?
View 5 Replies
View Related
Dec 18, 2014
1. A few months ago, I received a request to deny delete to a group (Windows AD login) against 3 databases. So I issued a DENY DELETE to the relevant Windows login.
2. Now, someone is having trouble updating data. The issue seems to be that a trigger fires during the update. In the trigger the code tries to delete from a table in the previously delete-denied databases. So the update fails.
-- In retrospect, perhaps I should have used REVOKE - not sure
-- Also, I can't seem to find a listing of the DENY permissions - or rather, how to list what permissions are denied. I guess maybe that is a hard thing to do if the result of a DENY is to leave certain permission columns NULL (vs adding 'DENY'). But it would be useful for me to see what the current permission state is.
-- Finally, I tried to undo the DENY without success. Here is my attempt.
a. I tried GRANT DELETE... - no luck
b. Then I saw an article explaining to use REVOKE, so I tried 'REVOKE DELETE' followed by 'GRANT DELETE' - still no luck.
View 3 Replies
View Related
Apr 28, 2015
I have stored procedures. I have heard that it is a best practice to use stored procedures to encapsulate some SQL statements and then grant permissions to execute the stored procedure. But when I try this and use EXECUTE AS to test it out, the user in question gets errors about not having access to some of the underlying objects.
How does this best practice work? If I need to grant the user permissions to the underlying objects anyway, I am not sure why a stored procedure is considered best practice in this regard.
View 8 Replies
View Related
Apr 17, 2015
I have a sql server 2012 server and I need to prevent the users from creating new schemas by mistake. Is there any way to revoke that permission alone but still letting the user to create their own objects in dbo (yes I know that shouldn't be in dbo but that is another issue).
View 2 Replies
View Related
May 13, 2015
I have a stored procedure which executes about forty other stored procedures in several different databases. All of these other procedures truncate tables and insert new data into those tables selected from still other tables.
I want to run this top-level procedure using an account which can't do anything else.
Is there a simple way to give it all the permissions it needs without empowering it to do anything else?
View 0 Replies
View Related
Aug 4, 2015
I found this script online..
SELECT prin.[name] [User], sec.state_desc + ' ' + sec.permission_name [Permission]
FROM [sys].[database_permissions] sec
JOIN [sys].[database_principals] prin
ON sec.[grantee_principal_id] = prin.[principal_id]
WHERE sec.class = 0
ORDER BY [User], [Permission];
but the results are this: 2 columns - User and Permission
User Permission
User1 GRANT CONNECT
User2 GRANT CONNECT
IS there a way in SQL Server (2005/2008/2012) to run a script against a Database that will show all users that have permissions to that Database and the type of permissions?
View 7 Replies
View Related
Jul 5, 2014
I am struggling to set a SQL Server Express for remote access...
Since it is the only instance on this server, I have set it up to use port 1433 by amending the IPALL entry of the Server Network Configuration, TCP/IP Properties.
I have also disabled "Shared Memory" and "Named Pipes" to leave only TCP/IP enabled as a server network config.
I have stopped the Browser Service because I think I don't need it if I use port 1433.
From the server itself, I can connect to my SQL Server through "localhostSQLEXPRESS" or "servernameSQLEXPRESS" but not from "10.xxx.xxx.xxxSQLEXPRESS" or "127.0.0.1SQLEXPRESS"
I suppose until I can access my server from itself using the IPAddress, there is no chance connecting remotely...
View 5 Replies
View Related
Aug 17, 2014
I use SQL Server 2012 Management Studio, SQL Server Data Tools and Visual Studio 2012 to deploy a data mining project.
I get the following error message: A connection can not be made. Make sure the server is running.
I am able to pick up the database using the details below:
Machine Name: NIRMALK01-PC
SQL Server: SQLEXPRESS
SQL EXPRESS: Running
SQL BROWSER: Running
SQL Agent: DISABLED
I can not find Analysis Services in services.msc
This is the error: Error 1
The project could not be deployed to the 'nirmalk01-pcsqlexpress' server because of the following connectivity problems : A connection cannot be made to redirector. Ensure that 'SQL Browser' service is running. To verify or update the name of the target server, right-click on the project in Solution Explorer, select Project Properties, click on the Deployment tab, and then enter the name of the server.
View 2 Replies
View Related
Feb 17, 2015
I had two servers, in which SQL Servers are installed. I created a alias for server
Server 1 : test1.sqlcloud.com (Alias name with port no other than 1433)
Server 2 : test2.sqlcloud.com (Alias name with port no other than 1433)
I can connect to the servers using the port. for ex server1 to server2 and viceversa using "test2.sqlcloud.com,port". But I could not connect to the server by only specifying the alias name ("test2.sqlcloud.com"). When I am connecting I am getting the following error.
"TITLE: Connect to Server
------------------------------
Cannot connect to test2.sqlcloud.com.
------------------------------
ADDITIONAL INFORMATION:
Connection Timeout Expired. The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement. This could be because the pre-login handshake failed or the server was unable to respond back in time. The duration spent while attempting to connect to this server was - [Pre-Login] initialization=21020; handshake=0; (Microsoft SQL Server, Error: -2)
View 1 Replies
View Related
May 6, 2015
I have very unusual question. I have seen a query that looks something like this:
SELECT A, B, C FROM 'LDAP://abc.def.com'
What exactly does this mean? Could such a query be executed in the SQL Server Management Studio? Where would such a table/database be actually located?
View 3 Replies
View Related
Sep 1, 2015
Enabled DAC and when connecting, I get a "Failed to connect to server ADMIN:SERVERINSTANCE"; however query window shows I'm connected on ADMIN:SERVERINSTANCE and checking tcp_endpoints + dm_exec_sessions, I'm definitely on the DAC.
View 1 Replies
View Related
Sep 15, 2015
Every time I start SSMS 2012 the "Connect to Server" box is partially obscured in that the top of it, the move bar can't be accessed. I've manually moved it using "alt+space" then "M" followed by the arrow keys, but it's getting annoying. Is there any way to change the initial start position of this box?
View 6 Replies
View Related
Apr 14, 2014
can i connect to SAP from SSRS and execute reports?
View 1 Replies
View Related
Apr 30, 2014
Currently we are unable to connect to analysis services and getting the below error
Element 'return' was not found.Line 5,position 2
What could be causing this?
View 1 Replies
View Related
May 6, 2014
I have an issue when connecting with application Intent=readonly when using Listener name. I can connect to the read only secondary with application Intent=readonly if I use the server name. It gives a host does not exist error when using listener. This happens only if I am using the application Intent keyword. I can connect for normal readwrite operation using listener name.
View 3 Replies
View Related
Nov 17, 2014
I am working with SSRS. I do have admin rights. I can connect to the url https://localhost/Reports and can create the new reports and subscription. Delete the reports and subscriptions.
But when I tried to connect to Reporting Services from SSMS, I am getting below error.
I used the windows authentication. It is showing that the user name or password is not valid or you don't have permission to access this server
I can connect to the database engine and Integration services but unable to connect to the Reporting services.
View 4 Replies
View Related
Jun 22, 2015
I am unable to connect to reporting server , services are running fine .Checked in logs and found
ERROR: Error initializing configuration from the database: Microsoft.ReportingServices.Library.ReportServerDatabaseUnavailableException: The report server cannot open a connection to the report server database. A connection to the database is required for all requests and processing. ---> System.Data.SqlClient.SqlException: A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)
How to resolve this ..
View 8 Replies
View Related
Jan 29, 2007
Hi, In my web app I've got two databases (the asp profile one and my own custom one). If I take the code and data and use it to create another website on another box, I usually get a database error saying the permissions are wrong. In this case I usually just give everyone full control and it works, but this obviously isn't good practice!So, I'm wondering what permissions on database files does ASP need? Which users need what permissions? Thanks
View 3 Replies
View Related
Mar 5, 2004
Hello everyone...
I am trying to learn ASP.NET with SQL Server and I am having trouble with database permissions...
I am trying to use the following connection code:
Public dbconnstring As String = "Data Source=.;Integrated Security=SSPI;Initial Catalog=tempdb"
Now... this code works fine for the database "tempdb"..... but gives me an "access denied" error with the database "Northwind"...
I have tried to play around with the permissions for the Northwind database... but nothing happens...
Does anyone know haw to work around this??? either through changing the connection code or through changing the database permissions????
View 7 Replies
View Related
Mar 20, 2000
I created a database using the sa account !!
I then created another user and made it a dbo of the database and used this account to create the tables etc and load the intital data.
My problem is that the sa account (or any new account that I create - regardless of the role) can not access the data in the tables.
Any help in resolving this would be appreciated.
Zane
View 1 Replies
View Related
Nov 12, 2004
I have a user that need to create stored procedures but as the dbo account and not his own account so that the stored procedure is called dbo.storedprocedure and not domainuser.storedprocedure. He is a database owner but in order to have this happen I have to have him in the local Server Administrator group. What have I done wrong?
Also, he need to be able to run Enterprise Manager and SQL Ananlysis manager but I do not want him to be a local administrator but they will not start if he is just a local user. How can I accomplish it.
Thanks,
Stryder :confused:
View 1 Replies
View Related
Apr 26, 2007
I have db 1 that has a process user called db1process that calls an exec spuserprocess
now spuserprocess has in it a call to
dbo.db2.spnewuser
and i gave spnewuser also permissions to userprocess but spnewuser also does selects from other tables and it is returning a permission error -- do i have to give spuserprocess access to all the other tables? is it not enough to give access to that procedure?
View 4 Replies
View Related
