Tag Info

New answers tagged

1

Yes you can. Per documentation: Note that the user performing the insert, update or delete on the view must have the corresponding insert, update or delete privilege on the view. In addition the view's owner must have the relevant privileges on the underlying base relations, but the user performing the update does not need any permissions on the ...


2

@AaronBertrand is right. You should consider a different method of refreshing your staging environment. If for no other reason that any sensitive data that you have in production is now available to a new group of users who may not be as carefully restricted as in production. However I realize that changing your process can take time (if management will ...


5

To check for a specific effective permission on a securable, use HAS_PERMS_BY_NAME. For example, to show if the current security context has SELECT permission on the Production.Product table in the AdventureWorks sample database: SELECT HAS_PERMS_BY_NAME ( N'Production.Product', N'OBJECT', N'SELECT' ); Related: To ...


1

I think that your simplest option is a job. You can grant the permissions to start a job through the SQLAgentOperatorRole role in msdb. Members of this role can start jobs owned by a sysadmin login, which can execute the commands you need. Here's an example based on your requirements: USE master GO CREATE LOGIN testUser WITH PASSWORD = 'strongPassword' , ...


2

@Joanna.B Allowing access is a tricky and well planned strategy. Ultimately DBA's are responsible to allow access and maintaining of databases and mainly in STG and PRODUCTION environment. You do not want to allow any unnecessary access into STG and PROD environment because ultimately you DBA's will be responsible for any changes. First of all if you allow ...


1

A common practice in my experience has been that if a user needs elevated access to a higher environment, you restore a copy to the level which they have access to. This ensures that you can keep similar security across most environments and mitigates privacy and security concerns as well as the creation of new issues. For example, if tech_leads need to ...


2

You just need to deny EXECUTE permissions on any object that you don't want the app login to see. Denying VIEW DEFINITION will only prevent the code of the object from being seen by that user. That is the point of VIEW DEFINITION: to allow users to run code that they can't see the code for. Denying EXECUTE on the object will removing it completely as far as ...


0

Can you confirm with a Profiler trace which credentials are attempting to execute sp_start_job? I'm curious if it's the credentials of the client using the browser or the underlying application pool. If the account in question has sysadmin, it should already have the permissions to execute jobs.


0

Besides what @James raised on the comments regarding the SSAS settings of commit timeout and force commit timeout, It was the change that I did on SQL Server that make it work at the end. EXEC sys.sp_configure N'remote query timeout (s)', N'6000' GO RECONFIGURE WITH OVERRIDE GO this is the same value as you can see on the picture below: you get there by ...


0

I have a Credential running under a generic (AD) domain account. I changed the password last week. That broke the SSIS jobs. Updating the password fixed it.


3

Use module signing. Sign your procedures with a certificate and then grant the required permissions to a certificate-derived user. The link contains a full example.


4

NO, With just Execute permission doesn't allow a user to execute a stored procedure successfully if it contains some DDL (CREATE, ALTER, DROP, TRUNCATE, RENAME) statements.


1

After some reading I think I got it. "NT SERVICE\SQLSERVERAGENT" is a member of the local group "SQLServerSQLAgentUser$RINUS$MSSQLSERVER". This group has al the right that a sql server agent account needs "NT SERVICE\SQLSERVERAGENT" is a part of the sql server fixed sysadmin role "NT SERVICE\SQLSERVERAGENT" can't be selected in the list of available ...


1

It Requires ALTER TRACE permission on the server. Note :- This feature will be removed in a future version of Microsoft SQL Server (2014 above). Avoid using this feature in new development work.


0

Maybe this is not an answer to your question but I believe it is relevant. I would write a comment if I could but I want to add the pictures and the script I used. I had the same problem while ago, I was getting the following error message while trying to re-attach a database: The way I sorted this out was impersonating the login used to detach the ...



Top 50 recent answers are included