All Questions
104 questions
0
votes
1
answer
50
views
SQL Server read-only SystemAdmin account
Is there a way to create a sysadmin like account with only read privilege's.
I know that you can give a lot of grants to an account like view server state, view any definition, view any database etc, ...
- 511
3
votes
1
answer
620
views
How to set minimal permissions to change SQL Azure Firewall rules
I have a desktop app that connects to SQL Azure database.
Some users have dynamic IP addresses, and it is not possible to get them to use fixed addresses.
I've written an ASP.NET page hosted on the ...
CommunityBot
- 1
1
vote
2
answers
84
views
User can't access a database because he belongs to multiple AD groups
We have an SQL Server 2016 that has 20 different databases on it that are used by different applications and different teams in the company. There are groups of users in Active Directory that combine ...
- 14.5k
0
votes
0
answers
79
views
How to prevent user from connecting via SSMS but allow query via .net application connectionstring?
I have been given task to create a SQL login that can be used to query the db via a .net application connection string.
But the user shouldn't be able to connect to SQL via SSMS.
I have created the ...
- 3,489
9
votes
9
answers
61k
views
Error Authenticating Proxy Account when Executing SSIS job
I have a SQL Server instance that runs 5 scheduled tasks each night, each of which run SSIS packages.
These packages have been running for years and the associated steps run via a Proxy Account (...
- 317
1
vote
1
answer
498
views
What is the difference between a securable and permission in SQL Server?
In Microsoft SQL Server you have the possibility to set permissions at server and database level. You are also able to select a server login or database user and then set securables. I am wondering ...
- 984
6
votes
2
answers
1k
views
Sql Server Agent is part of the sysadmin role even tough Sql Server says it's not
I created a Windows account with no extra rights other than domain user and set it as the SQL Server Agent service account with the SQL Server Configuration Manager.
The BoL/MSDN says that this user ...
- 2,201
-1
votes
1
answer
4k
views
How to query the effective permission of sql login (windows and/or sql user)?
How to query the SQL server to fetch the effective permission of the following SQL logins under the Security/Logins:
SQL user
Windows user
Windows group
CommunityBot
- 1
4
votes
2
answers
19k
views
What are SHOWPLAN permission security risks? (MS SQL Server)
I'm having some friction when it comes to showplan permission security.
Say there are 5 databases on a server. And each one has a test/ production split (so 10 total).
There is one sysadmin.
There ...
- 875
1
vote
1
answer
3k
views
Restrict access to a new schema to a specific role despite users having db_datareader?
I'm working in a SQL Server 2016 database that is not of my design nor can I fundamentally change the security structure. I know it is likely trash/worst practice but I have to play the hand I am ...
- 238
4
votes
2
answers
6k
views
Can SET IDENTITY_INSERT be allowed with less privileges than db_ddladmin?
I've inherited a system where the application is running under sysadmin account.
I limited this account to db_datareader + db_datawriter + EXECUTE on all database and set extended events session to ...
- 229
0
votes
3
answers
2k
views
Unable to grant membership to public role
I'm trying to refresh my dev db with a snapshot from prod. I've restored the production db from the prod server to my dev server just named as development. I'm trying to grant access to the dev db to ...
- 18.3k
1
vote
1
answer
311
views
Unable to restrict user from built in SQL Server Functions
We have a SQL Server (Standard) database with a user whom we are attempting to restrict permissions to only CRUD Operations to certain tables (CREATE, SELECT, UPDATE, DELETE) and only allow connect ...
- 69.5k
5
votes
2
answers
3k
views
Recover SA Password - PSTools - Not working
I'm on a new company, and the first thing I saw is that they don't even know how many instances they have. they don't have passwords and the old DBA had no Idea what he was doing.
long story short, I ...
- 90.3k
16
votes
4
answers
20k
views
Security and Performance implications of "View Server State"
This question points out that "View Server State" permission is required for various DMV's (dynamic management views), but I can't find anything about who you do and do not want to grant the ...
- 69.5k
1
vote
1
answer
394
views
How to remove user from login (user is already part of group) and keep the jobs?
I have a sql login for windows group.
One of the members of ths group was also added as a sql login. This user has 1 job in which he is registered as the owner.
I want to remove this user login ...
- 70.5k
0
votes
1
answer
134
views
Does deleting the login retain the permissions given via group membership?
I have a windows group account added as login in sql server. The group contains around 100 members.
The above windows group login is given specific database level permissions like db_datareader, ...
- 70.5k
1
vote
1
answer
138
views
What is effective permission if windows user is added as login as well as via group membership; with only public permission given via the user login?
Link: https://learn.microsoft.com/en-us/sql/relational-databases/security/authentication-access/determining-effective-database-engine-permissions?view=sql-server-ver15#principals
Remember that a ...
- 70.5k
0
votes
1
answer
165
views
is it safe to grant permission to all users in Microsoft sql server
I have a global production server and database, as I all users should have access to it by remote desktop.
Is it possible to grant NTFS permission on all database files (mdf+ldf files) to all users? ...
- 388
27
votes
8
answers
106k
views
A query that lists all mapped users for a given login
When looking at the properties of a particular login, it's possible to see a list of users mapped to that login:
I profiled SQL Server Management Studio (SSMS) and I see that SSMS connects to every ...
- 182k
10
votes
1
answer
10k
views
Is there a difference between granting SELECT to a user and adding them to the db_datareader role?
I was asked to grant SELECT, INSERT and UPDATE to a given user for all tables in a database. I ended up adding the user to db_datareader and granting them INSERT and UPDATE at the database level.
...
- 24.3k
1
vote
2
answers
5k
views
How to grant permissions to allow anything inside a SQL Server schema?
I have a schema called training. I have a new role called training_modify. Here are the desired permissions for users placed in that role:
Select, update, insert, and delete permissions on any object ...
- 69.5k
1
vote
1
answer
3k
views
SQL Server Grant CRUD to Users on One Schema
I have a schema called training. I want users who will be trained to have CRUD permissions on any object on that schema. I do not want them to have any other permissions on any other object outside ...
- 10.4k
11
votes
2
answers
2k
views
Break the link between a Database User and SQL Server Login
When restoring database from once instance to another one sometimes has to relink SQL Server Logins to the Database User.
This is normally achieved with one of the following procedures.
Deprecated ...
- 18.3k
3
votes
1
answer
8k
views
Login failed for user Error: 18456 State: 10
In one of my vb.net applications I am getting the error Login failed for user 'user'.
But the user has full permission (sysadmin). Also this error happens intermittently.
Error log has the following:
...
- 182k
4
votes
1
answer
23k
views
Permission in schema for user
I created a default database and created some schemas within this database. Created users for each schema, now I want to give full permission for each user in your schema only, how should I do?
- 182k
2
votes
2
answers
8k
views
Granting minimum select permission on system "sys" tables in every database
Our development team has created some SSRS reports that retrieve metadata only (no 'real' data) from every database. I am trying to lock down the service account running these reports to the minimum ...
- 2,696
5
votes
1
answer
12k
views
How to grant permission to execute stored procedure in master database from another database
Our developers have a domain account used by their application that needs to be able to execute stored procedures created in the master database using the "sp_" naming convention from other databases ...
- 16.5k
2
votes
1
answer
2k
views
Permissions for SSIS/SQL Server agent jobs for multiple user groups
What permissions should I use for Active Directory groups to be able to deploy and then run/schedule their own jobs only? For example, I have 3 folders in the Integration Services catalog. Dept_A, ...
CommunityBot
- 1
3
votes
1
answer
280
views
Allowing non-privileged users to cause privileged code to run
Context:
Our SQL server is used for teaching, and we need to be able to automatically create databases for the students on demand and in real-time. We also need the students to be able to delete ...
- 69.5k
14
votes
1
answer
22k
views
Access view based on table in another database without account in that other database
I created view in database1 based on tables in database2. I gave SELECT permission to a user who has access only to database1. The user can't get this view to work because he doesn't have an account ...
- 69.5k
0
votes
1
answer
823
views
Create a user on database A with a procedure from database B (with EXECUTE AS)
I have 2 logins :
LoginA: role dbcreator + CONNECT ANY DATABASE
LoginB: role sysadmin
I have 2 databases :
dbA: everyone has all rights on it
dbB: only LoginB has rights on it, LoginA is only the ...
- 2,437
3
votes
1
answer
1k
views
How do I grant a SELECT permission to an individual user who's part of a large group that is explicitly denied?
I have a table SuperSecretData which is currently setup to deny SELECT to everyone in the company by the standard Active Directory account "Domain Users". But a couple of specific individual users do ...
- 47.6k
4
votes
2
answers
34k
views
What does GRANT CONTROL on a schema do in MSSQL?
I'm using SQL Server 2016 for these tests.
The following did not allow me to create a table in schema S for user U
USE [D];
GRANT CONTROL ON SCHEMA :: [S] TO [U];
But this did:
USE [D]
GRANT ALTER ...
CommunityBot
- 1
1
vote
1
answer
71
views
Prevent updating table using procedure in different database
This is my scenario:
Windows User Gabe has db_owner access of database DB1.
Windows User Gabe has db_reader on database DB2.
When the user executes UPDATE DB2.dbo.Tbl1 SET X = 1, they get "The ...
- 28.5k
0
votes
1
answer
431
views
how to script server side permissions - specifically EXECUTE ON SYS.XP_PROP_OLEDB_PROVIDER?
I could not script the following server side permissions:
GRANT VIEW DEFINITION ON LOGIN::[site_helpdesk] TO [Radhe]
but thanks to the question below I found a solution to that:
How to script out ...
- 1,981
2
votes
1
answer
6k
views
Grant permissions on a stored procedure to tables in another schema
I created a user in SQL Server 2012 database and revoked all permissions given by the public role.
Then I granted EXECUTE permission on a stored procedure.
The user can execute the procedure but ...
- 69.5k
3
votes
1
answer
3k
views
How to grant execute permissions to a stored procedure but not to the underlying databases
I am trying to allow a user to run a stored procedure while restricting them read/write access to the underlying tables. I am able to complete this with other stored procedures, but the one I am ...
- 8,540
3
votes
4
answers
4k
views
What permissions are required for UPDATE STATISTICS #table WITH ROWCOUNT = xxx?
Can anyone point me to the correct documentation or additional permissions I need besides SA to do the following?
When I run it, I receive the following error:
Cannot find the object "#test" because ...
- 69.5k
4
votes
1
answer
1k
views
Execute Permissions for a Store Procedure that creates databases
I have a Stored Procedure that creates a Database for a specific project which needs to run every month by one of our Data Analysts. The question is how do I structure this to enable the Analyst to ...
- 69.5k
4
votes
1
answer
6k
views
SQL Server impersonation
This question is about impersonation in Microsoft SQL Server. I want to know that if I have impersonation permissions on a specific login then I can easily impersonate that login whenever I want and I ...
- 69.5k
0
votes
0
answers
426
views
Column level security in SQL server
Is it possible to assign column level security in SQL Server directly on a table, without using a view?
For example, for table 'tbl', if I want column 1 to be restricted for user 1, is it possible ...
- 7,984
3
votes
4
answers
2k
views
Prevent SysAdmin from doing a Database Restore
I am a sysadmin on my SQL Server 2008, and I need to be, but I would like to setup my security on the Production server to prevent me from accidentally restoring to the production database. I ...
- 3,904
0
votes
0
answers
48
views
NT SERVICE accounts should be sysadmins? [duplicate]
I have the following query to get me a list of sysadmins in the current server:
select 'ServerRole' = SUSER_NAME(rm.role_principal_id),
'MemberName' = lgn.name ,
'Status'= Case when ...
- 16.6k
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 ...
- 450
0
votes
1
answer
4k
views
cross database ownership - The server principal is not able to access the database under the current security context
I have this script that set a bunch of databases to the same db owner, and set cross database ownership on at database level
select database_name=name,
db_owner_name=suser_sname(owner_sid),
...
- 47.6k
1
vote
1
answer
1k
views
how to script out all the permissions in a folder using powershell?
Using the script below I find out where are the data and log folders:
declare @rc int,
@dir nvarchar(4000)
declare @data nvarchar(4000)
declare @log nvarchar(4000)
...
- 16.5k
2
votes
1
answer
578
views
Does a user who has permissions to select from a view also need select granted to the children entities of that view?
If view 1 uses tables A and B, and a user has SELECT granted on view 1 is that sufficient for the user to select from view 1 or does he also need SELECT granted on tables A and B.
Specific details on ...
- 39.5k
7
votes
1
answer
2k
views
How to GRANT permission to execute stored procedure?
I have an AD group that is setup as a Windows Authenticated SQL Login on my SQL Server. On DatabaseA it has the db_datareader and public roles. So the users of this AD group only have read access to ...
- 18.3k
0
votes
1
answer
251
views
Read Only Access Except Stored Procedures in Specific Schema
I'm building an application that is attached to a legacy application database. The application has it's own database that is on the same server as the legacy database.
My application is ASP.NET and ...
CommunityBot
- 1