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, ...
- 66
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 ...
- 4,690
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
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 ...
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 ...
- 13
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 ...
- 111
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 ...
- 111
-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
- 3,489
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 ...
- 3,489
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 ...
- 3,489
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, ...
- 3,489
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? ...
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 ...
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 ...
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
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 ...
- 75
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 ...
- 133
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 ...
- 11.2k
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 ...
- 310
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 ...
- 39.5k
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 ...
- 1,336
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 ...
- 16.6k
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 ...
- 31
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 ...
- 473
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 ...
- 223
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 ...
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 ...
- 131
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 ...
- 1,063
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),
...
- 16.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.6k
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 ...
- 39.5k
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 ...
- 105
0
votes
1
answer
6k
views
Why securityAdmin does not have enough permission?
I am a server securityAdmin.
The securityAdmin role should be treated as equivalent to the sysadmin role, according to Microsoft, but I failed to do below 2 things
make myself as sysAdmin
grant ...
- 123
1
vote
1
answer
121
views
denydatawriter does not work in SQL Server
I have a couple of admin roles in a SQL Server 2012.
To prevent myself from changing data in one database, I added my login as a member of denydatawriter role to that database.
It seems does not ...
- 123
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 ...
- 1,709
1
vote
1
answer
4k
views
What would cause "SELECT permission denied" for an object?
This is a work backwards kind of issue, since I can't tell you all of the requirements to cause this to happen.
I'm getting the following error for a table (TABLE01) in one database (DB01), but not a ...
- 2,116
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 ...
- 141
5
votes
4
answers
8k
views
Why does user not have access to database?
I have a user ls_readonly which is supposed to have db_datareader privileges on several databases. I thought I had set it up right:
But when I connect to the server as ls_readonly and try to open the ...
- 2,943
1
vote
1
answer
2k
views
Granting permission to the service account
I am currently granting permissions to a service account on a database. I want to ensure if the granting db_datareader and db_datawriter database roles is the same as my grant command.
Adding service ...
- 141
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 ...
- 2,116
3
votes
1
answer
2k
views
Granting SELECT permission on a object
I am not sure if this is an odd question, so please let me know if what I describe below is possible.
We have a server, which is configured by someone else and none of us are "added in it".
As of ...
- 1,600
3
votes
1
answer
162
views
Status of objects owned & permissions granted by a (now)-disabled Windows Account
When an employee (esp. sysadmin) leaves (ie, their AD account is disabled), what happens to objects they 'owned' (permissions they granted) in SQL Server?
In other words, for SQL Server security, do ...
- 71
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,053
9
votes
3
answers
72k
views
Create table permission for a user in specific schema
I want to give Create , alter and drop permission to user A on a schema1 of a database.
I guess this question has been already asked, and what i have found is to Grant Alter to the schema and also ...
- 190
4
votes
4
answers
12k
views
See who has access to databases via AD group membership
SharePoint has a button to "Check Permissions". Here you can select a user from Active Directory, and it will magically traverse groups and show you all the access the user has on the site based on ...
- 2,053
6
votes
1
answer
751
views
How to GRANT object permission to a user defined server role?
I want to create logins that are granted permission to access
all user databases
view all definitions
select all records of all tables
To me it seems to be too much overhead to assign the login to ...
- 4,807
0
votes
1
answer
25
views
Calling Sproc From Another Server - runs as SA
I have a sproc on ServerA that calls another sproc on ServerB
The servers are connected through linked server.
When the sproc gets called on ServerA it is called by the user login. However, then on ...
- 1,414
1
vote
2
answers
876
views
Server role with permission only to create server login, not alter one
I am working with Microsoft SQL Server Management Studio and I want to create a server role that has permissions to create server login but only create, not alter!
Does anyone know what is the ...
- 133