All Questions
Tagged with permissions sql-server-2008-r2
104 questions
0
votes
1
answer
1k
views
Is it possible to grant EXECUTE permissions on a custom function without compromising read-only access?
We have an ancient database instance - Microsoft SQL Server 2008 R2 SP1 - that is running an (also obsolete) business-critical application. I know this is horrible, but for now it's not my call.
Long ...
- 103
2
votes
2
answers
484
views
How to remove dbcreator server role?
I was able to add the dbcreator server role to a user of a SQL Server DB, thanks to this nice question/answer: create-database-permission
How can I REMOVE that permission? Is there a stored procedure ...
0
votes
1
answer
193
views
How To Grant A User In AD Groups Which Is Denied To DML Commads, To Update On A Spesific Tables
I have a Active Directory user account named SQLQRY in my sql server. SQLQRY is permitted only read data. Those users can use onyl select statement. I need to give just update and delete permission on ...
- 69
1
vote
3
answers
3k
views
How to Add A User To A Database In Standby / Read-Only Mode In Sql Server
I have a database which is in standby / read-only mode in my reporting server. But, I can not add a new user to this database. How can i add a new user to this database?
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
0
votes
1
answer
835
views
Set up read-only privileges on the dbo schema, but allow creation of entities in another schema?
Is it possible to create a custom schema in a database such that users that have read-only access in that database for the dbo schema can be set up with the ability to create and modify entities for ...
- 39.5k
0
votes
2
answers
224
views
How To Grant A User Just To Create A Table In Existing Database But Not To See Other Existing Tables In It
We have dealed with an outsource company to install an application for use of our accounting department. Now, they want to access our existing database to install their application but i don't want ...
- 47
0
votes
1
answer
84
views
List of databases which a user can access in SQL 2008 R2 [duplicate]
Is it possible to create a list of all the databases where a specific user can access?
For example,
My user is 'Domain\MarySmith'. This user can access on a single database or can access many ...
- 113
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
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
2
votes
1
answer
834
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
4
answers
5k
views
What minimum permissions and/or roles must a SQL Server login possess to be able to grant database db_datareader role to other logins?
To delegate the ability for a security admin to add new SQL logins to the db_datareader role within a database, what are the least permissions and/or roles the security admin must possess?
- 153
1
vote
1
answer
6k
views
Does SHOWPLAN permission toggle exist in SSMS
I'm using SQL Server 2008 R2.
Is there an option in SSMS 2016 to toggle SHOWPLAN permissions for logins (I know this is issued at the database level)?
Is there a query to see if a user, other than ...
- 333
1
vote
2
answers
9k
views
What am I missing for my SSRS Service account local server permissions?
I have a relatively new instance on SSRS 2008R2 SP3 running on Windows Server 2008 R2. I am trying to set up separate domain service accounts for the separate SQL Services i.e. agent, engine, ...
- 365
2
votes
2
answers
369
views
Prevent Modification of dbo objects while allowing other schemas to be modified
I have a vendor solution that uses a sql server (2008r2) database. We are allowed to do anything we want in our own schema, but cannot modify dbo objects without vendor permission. Any customization ...
- 505
1
vote
1
answer
596
views
Job not running as agent but as engine account
I recently created a new SQL Server environment and I am trying to use best practices where I can, especially the least privileged approach. Other servers in our environment just have jobs running ...
- 365
3
votes
1
answer
3k
views
Can I not add VIEW SERVER STATE to a role?
My server admin asked me to grant a user SA privileges for the purpose of upgrading our vCenter environment from 5.5 to 6.x and I refused. So I found a VMWare article that outlined all of the required ...
- 365
3
votes
2
answers
309
views
Can I trust the effective permissions of default database roles?
I've granted membership in the db_datareader and db_datawriter roles for some testing being performed by a specific user.
Looking at the names of each role, I know what the roles do. However, I want ...
- 365
5
votes
2
answers
3k
views
Can't use msdb.dbo.sp_send_dbmail when in service broker - executes as guest?
I have a procedure TheNotificationProcedure that makes a cross-database call to msdb.dbo.sp_send_dbmail.
It gets invoked (indirectly) from a service broker queue:
CREATE QUEUE [Blah].[TheQueue]
WITH ...
- 151
3
votes
3
answers
3k
views
Hide Table Structure
Is there a way to restrict a user from viewing the table structure? I just want them to have a SELECT, INSERT, UPDATE, DELETE access only on that table. I want to restrict a specific programmer on ...
- 31
3
votes
1
answer
4k
views
Deny dbo schema permission
I'm trying to deny all permissions on the dbo schema for a particular user, as I only want that user to access a specific schema. When I try the following (using sa):
DENY CONTROL, ALTER ON SCHEMA :: ...
- 173
0
votes
1
answer
1k
views
sysadmin user can't connect to explicitly specified database
I noticed some Failure Audit errors, in the Windows Application event log of the server that my SQL Server runs on, stating:
Event ID: 18456
Login failed for user 'DOMAIN\username'.
Reason: ...
- 111
3
votes
1
answer
562
views
SQL Server displayed 'Grantor' vs system tables result
When I select server properties -> permissions, SQL server appears to display the 'grantor' as the (sa-level) login of the person who created the login. But when I select from the system tables (see ...
- 71
3
votes
2
answers
608
views
Cross database reference select access with disabled login for DB user
We have two databases (DB1, DB2) with the following view structure.
DB1:
T1, T2, T3, ..... Tn (Tables)
V1 (View consuming {T1, T2, T3 ..... Tn })
Inside DB2 we have to create a view V2 which will ...
- 504
1
vote
0
answers
106
views
Login token does not acquire correct principal_id for some AD group server principals
The title reflects what I believe is the problem based on my research up to this point. Nonetheless, I will include back information in case it's relevant.
Back info:
Server:
SQL Server 2008 R2
...
- 111
2
votes
1
answer
4k
views
Permissions problem with BULK INSERT
We are getting the following error when trying to use BULK INSERT.
Msg 4861, Level 16, State 1, Line 10 Cannot bulk load because the file
"\filer\share\loadfile.txt" could not be opened. ...
- 24.3k
3
votes
1
answer
373
views
db_owner does not have access to Windows folders [duplicate]
This is MSSQL Server 2008 R2 running on Windows Server 2008 Enterprise. Service Instance is running as 'Network Service'.
I have two logins, UserA and UserB. UserA is a sysadmin. UserB is a db_owner ...
- 41
2
votes
3
answers
174
views
Restrictions on user-defined queries
I have a client that wants me to implement a webpage that allows users to enter custom queries in a text box so they can directly query the SQL Server database. I am concerned about users entering ...
- 21
7
votes
1
answer
306
views
Login cannot view definition even when granted the permission
I have an AD login which is in the below AD groups.
DOMAIN\GlobalGroup1
DOMAIN\GlobalGroup2
DOMAIN\GlobalGroup3
DOMAIN\UniversalGroup1
DOMAIN\UniversalGroup1 has been granted VIEW ANY DEFINITION on ...
- 925
10
votes
2
answers
58k
views
The INSERT/UPDATE/DELETE permission was denied on the object 'TheTable', database 'TheDb', schema 'dbo' [closed]
I have two SQL Server user accounts on a database that behave differently on INSERT / UPDATE / DELETE. SQL Server version is 2008 R2 (10.50.4042, 64 bits).
UserA (SQL Login): can do these commands (on ...
- 201
4
votes
1
answer
1k
views
Is it possible to deny RESTORE permissions to login with db_owner role membership?
Is it possible to deny RESTORE permissions to a login with db_owner role membership?
Is dbcreator required to RESTORE over an existing DB with a BACKUP of that DB? Or is that only needed when ...
- 41
10
votes
2
answers
17k
views
Permissions on Trigger Problem
I am working with a database in MS-SQL 2008 R2, and I'm running into a problem.
The basic setup is this: There are two tables of interest: dbo.Assesments and audit.Assesments. dbo.Assesments is the ...
- 203
1
vote
1
answer
6k
views
Hide Schemas and Tables
I have the following structure:
Views
dbo.vSales
Sec.vSales
Tables
dbo.Security
Sec.Products
Sec.Countries
Sec.Users
I have 2 groups accessing my Database:
Administrators
Standard_User
The users ...
- 530
2
votes
2
answers
8k
views
What is the minimum privilege required to create a full text index?
I just created a full text catalog on my database. I now have a developer who needs the ability to create full text indexes on the tables in this database.
The suggestion given in a previous ...
- 559
1
vote
1
answer
369
views
Permissions to execute a procedure on database a that reads a table on database b
A user in database a has execute permission on a stored procedurte in database a, that stored procedure reads table 1 in database b however when the user executes that procedure they get the following ...
- 423
5
votes
1
answer
2k
views
Select Permissions Across Multiple Schemas Within the Same Database
I have been asked by a client to implement some views for reporting purposes, the views will be accessed via PowerBI, Excel and SSRS. The designated user will only have access to the view and no ...
- 53
0
votes
1
answer
1k
views
Grant reference on type
What does GRANT REFERENCES ON TYPE::dbo.myType TO public exactly mean?
I found this statement together with every single user defined type we have. I wonder if this is useful or not. AFAIK this ...
- 4,807
5
votes
1
answer
743
views
Monitor table permission changes
I need a solution to monitor table permission changes:
how can I implement monitoring of every changes to granted permissions for a specific or any user on a specific table in SQL Server 2008 R2?
- 4,807
15
votes
3
answers
66k
views
what AD groups logins my user belongs to?
I am not sure if I have chosen the right title for this question. what
I am really after is, given a individual windows AD user, I would like to
find out the list of the windows AD groups (logins) ...
- 16.6k
0
votes
1
answer
862
views
Delete/remove orphaned users and their schema from server/database [duplicate]
I'm new to the role of being a SQL Server DBA for SQL Server 2008 R2.
I need help on how to find out orphaned users in SQL Server and how to delete/remove the orphaned users and their corresponding ...
6
votes
1
answer
12k
views
SQL Server - SQL Agent - Allow a Group to own a job
Scenario
There are three separate groups of users in our organisations (development, support, implementation). As it stands everyone is using a single login for everything. We are starting to move ...
- 2,101
0
votes
2
answers
7k
views
What is the alternate to SA account in SQL Server? [closed]
My client is currently using SQL Server for their app. At this time they are using SA account everywhere. For security reasons we need to disable the SA account. The solution I've is create another ...
- 1
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
1
vote
1
answer
1k
views
Active Directory group as a schema in SQL Server
In SQL Server 2008 R2 we have a database that is being maintained by a team of developers.
Each of them should have permissions to perform DDL operations such as adding a column or creating a table.
...
- 305
3
votes
2
answers
3k
views
DDL trigger permission Error
I have a two users, user1 and user2.
User1 has sysadmin rights and I have created a database level trigger through sa.
Now I am trying to create a table through user2 who has db-ddladmin rights and ...
- 53
0
votes
1
answer
299
views
How to grant a user the rights to access to all new (future) databases? [duplicate]
On my development machine, I often create new web instances that all run as NT AUTHORITY\NETWORK SERVICE. I want to grant this account rights to access all new databases that I create in the future in ...
17
votes
5
answers
58k
views
Property Size is not available for database
I recently restored a database to the same instance it was backed up from (SQL Server 2008 R2 Enterprise) and found that I couldn't access the database properties.
I have done the following:
Checked ...
- 1,490
3
votes
1
answer
3k
views
Granting access to the Job Activity Monitor
I'm trying to grant access to the Job Activity Monitor. On my model office server I can create a new login and add it to the SQLAgentReaderRole role in msdb, and the id has access to the monitor. ...
- 24.3k
5
votes
2
answers
9k
views
List all databases of a certain user ( SQL Server 2008 R2 )
I'm really looking for this answer for some time. I bet there is a post here but, I really can't find ( even on google ).
I think it's a simple task.
I want to list all databases for a specific user....
- 7,484
0
votes
1
answer
2k
views
Implication of owned schemas for a user having db_owner privilege
Importance of "Schema owned by the user"?
The user is db_owner for the db. Is it required to select owned schemas?, or else it will own all the schemas as it is a db_owner? Please provide me ...
- 31