All Questions
Tagged with permissions t-sql
31 questions
1
vote
1
answer
30
views
how do I restrict users to execute SET QUERY_GOVERNOR_COST_LIMIT 0 in SQL Server
We had set up a server configuration of QUERY_GOVERNOR_COST_LIMIT to restrict expensive queries, but as it turns out some clients started using the statement :-
SET QUERY_GOVERNOR_COST_LIMIT 0
to ...
- 11
0
votes
1
answer
638
views
What permission have to set for SQL user for configuration of change tracking in Database?
I am building application which have to have option to configure (disable/enable) change tracking on tables on specific database. I am connecting to database via SQL user. Which database permissions ...
- 421
0
votes
1
answer
1k
views
Grant permissions only if an object exists, but without mentioning the object twice
We have a number of database build scripts that grant permissions to specific objects (sprocs, functions, etc) only to specific roles. We have recently moved to an automated deployment model that has ...
- 101
7
votes
3
answers
748
views
Preventing stored procedures from writing to databases
I am a database developer mostly writing SSRS reports. I would like to create stored procedures and assure the DBAs that those stored procedures cannot write to the production databases, without the ...
- 83
0
votes
1
answer
575
views
Deny DDL_ADMIN permissions to a role on [dbo] schema
I have several database schemas like,
[ext],[stag],etc
and of course the built in [dbo] schema,
and also have a role called [MyRole].
My plan is to add [MyRole] to the DDL_ADMIN role, so they can ...
- 265
0
votes
1
answer
656
views
Permission to select sys tables in stored procedure
I have implemented the stored procedure to check if a specific [schema].[procedure] exists.
CREATE PROCEDURE [BlobCheckExisting].[usp_DoesStoredProcedureExist]
@schemaName varchar(128),
@...
- 455
2
votes
3
answers
3k
views
T-SQLRevoke permissions to all tables in the sql server in one go
How can we revoke a certain permission to all tables?
I've tried (T-SQL):
REVOKE SELECT on schema::DBO TO [public] AS [dbo]
But nothing happens.. All tables still have Select permission.
I can ...
- 131
0
votes
0
answers
38
views
How configure security to query second same server database
I have a database which holds all my permissions and a central database which holds all the logins for all my application. I need to create view that queries both database which reside on the same ...
- 331
5
votes
1
answer
226
views
Grant dbcreator only for databases matching prefix
In Microsoft SQL Server 2017+ I would like to grant the dbcreator role on a single user but only allow her to create databases whose name matches a fixed prefix.
Is it possible to do this at the ...
user184141
1
vote
1
answer
644
views
Disable Saving/Exporting Results of TSQL Query in SSMS?
I really don't want to go in to too much detail as to WHY I want to do this. Just know that it would be ideal for my use case.
Is there a way to disable a specific database role from using the Copy, ...
- 213
-1
votes
1
answer
2k
views
How to change the default database for login in T-SQL
I was trying to change the default database for logins in the sql server instance to the database they are mapped to. Currently, the default database of all logins in master database.
Alter login ...
- 133
1
vote
1
answer
68
views
Database Reader Role with the ability to Create, View and execute stored procedure's and view's in sql server
I will need to allow report writers on the Subscription database to be able to view the table, create alter and delete stored procedure and views. Since it is a transactional replication they cannot ...
- 1,063
5
votes
1
answer
4k
views
What are the rules for using COLLATE in a query?
I am putting a script together, that gives me the permissions on database objects.
SELECT permission_order=710
,permission_type = 'Object Level Permissions'
,db = db_name(),
login_=...
- 16.6k
1
vote
2
answers
5k
views
Why is dbo the only grantor in Microsoft SQL Server?
I can't understand why is dbo the only grantor in SQL Server? Or am I wrong?
I mean it as follow:
No matter as which user I am currently loged in in SQL Server Management Studio, when I go to ...
- 23
0
votes
2
answers
15k
views
The EXECUTE permission was denied on the object 'SPROC', database 'DATABASE', schema 'dbo'
I have a few users that are unexpectedly receiving this error message today when they attempt to refresh a stored procedure via Excel:
The EXECUTE permission was denied on the object 'SPROC', ...
- 213
1
vote
1
answer
753
views
What permissions does HAS_DBACCESS check?
I assume that
SELECT HAS_DBACCESS('database name')
is equivalent to
SELECT HAS_PERMS_BY_NAME('database name', 'DATABASE', 'permission name')
for some permission (or set of permissions, the results ...
- 259
1
vote
2
answers
638
views
Disabling Agent XPs causes sp_add_job (and sp_verify_job) to error with Msg 15281
Disabling Agent XPs is preventing sp_add_job from successfully running.
When running through maintenance, our software is disabling Agent XPs. This needs to be fixed, but ALL of our other sites ...
- 325
1
vote
2
answers
5k
views
Can you DENY access to a certain schema for a user with dbcreator role?
I have a database with two schemas core and dyna.
I am using dacpacs to update the database.
I want the user account to only be able to interact with tables (etc) in the core schema.
This is because ...
- 237
2
votes
1
answer
833
views
How I can impersonate a login with securityadmin server role
On MSSQL 2008R2 I have two instances on different servers, and I need sync up the password changes for all logins.
I thing that i could create a store procedure where a user (impersonate) with ...
- 449
-4
votes
1
answer
91
views
template for creating functions in the master database
I need to create functions in the master database, and I am developing a template for that, I want first to check if the function already exists, if not create a dummy function.
then grant all the ...
- 16.6k
-1
votes
1
answer
807
views
Is there a way to create a stored procedure in a database when you don't have permissions?
From a security perspective, I'm curious to know if there is a way to create a stored procedure without permissions? For example, the following error is returned when tried "CREATE PROCEDURE ...
- 41
1
vote
1
answer
2k
views
Scripting execute permissions on stored procedure
My goal is to write a script which:
Creates a stored procedure
Grants execution permissions to a particular user on that new stored procedure
On this SO question the recommendation is to add GRANT ...
- 13
1
vote
1
answer
182
views
Stored Procedures execution permissions
So what I am trying to do is to limit the access to a stored procedure so that only another stored procedure may call it. Here is an example:
create procedure Database_Deployment_Proc -- This ...
1
vote
1
answer
112
views
Forbid user to query internal SQL server information
I'm trying to forbid database user to get SQL server internal information.
Script that creates DB user:
CREATE USER [testuser] WITHOUT LOGIN WITH DEFAULT_SCHEMA=[dbo]
DENY SELECT, INSERT, UPDATE, ...
- 113
20
votes
2
answers
47k
views
Why scalar valued functions need execute permission rather than select?
I am wondering why, for scalar valued function, that I have to grant the user to execute rather than just a select?
meanwhile a table valued functions works just fine with only select permission or ...
- 413
0
votes
4
answers
169
views
Setting Select Permission on 100+ tables in a database
I got the 'Select permission denied' error for some of my tables in a database.
In MS SQL Server 2012, setting Select Permission on 1 table is easy. I just right click on the table > Properties &...
- 113
2
votes
0
answers
533
views
What is the bare minimum access needed in the public role?
I need to lock down access to a specific database for a user - they should only have access to execute a couple of stored procedures.
However, by default the user gets everything in the "public" role....
- 405
14
votes
1
answer
15k
views
Mapping of multiple server logins to one database user
This seems to be a stupid question but despite some research I was unable to find any information regarding this only (possibly due to using the wrong terminology).
Is it possible to log multiple ...
- 263
3
votes
1
answer
1k
views
DENY EXECUTE on one schema object hides other objects in same schema
I have five stored procedures in a schema [XTR], the users are all in one AD group [AD\Users] and have db_datareader and public assigned to them. On one of the stored procedures I don't want to give ...
- 2,907
2
votes
1
answer
9k
views
CREATE DATABASE Permission denied in database 'master' error
I'm working with a tool that as one of the automated steps will attach databases from my file system to my local SQL Express 2008 instance. The tool requires a login in the sysadmin server role, for ...
- 123
-4
votes
1
answer
9k
views
T-SQL to list all the user mappings with database roles/permissions for a User
I am looking for a t-sql script which can list the databases and and the respective roles/privileges mapped for a particular user. Using SQL Server 2008 R2.
Regards
Mohammed
- 9