All Questions
Tagged with sql-server-2017 permissions
17 questions
1
vote
0
answers
176
views
Altering the column without sysadmin or db_owner roles in published tables
I am using replication between two MS SQL Servers (2017 14.0.3421.10).
Unfortunately, when replication publishing is switched on, my user for database schema changes is not able to ALTER any columns. (...
- 111
0
votes
2
answers
186
views
What are the Minimum Permissions to Create an MSSQL Database and Take Ownership of it?
I would like a less privileged user (KINGDOM\joker) to be able to create, manage, and drop databases on an MSSQL 2017 server [14.0.2027.2 (X64)]. KINGDOM\joker should only be able to affect the ...
0
votes
2
answers
1k
views
Cannot find role db_datareader
I am logged in as sa (double checked it in my session with select suser_name()).
I have created a login cards_login_generator, and the same-named user on the database [cards]. I want to permit this ...
- 447
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
1
vote
3
answers
7k
views
Are there any things to consider before granting VIEW SERVER STATE and VIEW DEFINITION to Developers?
I would like to grant below to permissions to Developers group, on Production SQL Server:
VIEW SERVER STATE
VIEW DEFINITION (server level)
This is done to make them able to query some of dynamic ...
- 5,964
0
votes
1
answer
2k
views
How to test user permissions
I have two server users, lets say admin and notadmin. I need to test notadmin's permissions. I need set and test VIEW SERVER STATE permission on notadmin, but usually I have access only to database ...
- 103
3
votes
3
answers
776
views
Error 297, Severity 19 trying to create new table as sysadmin
I am completely scratching my head right now. I have two database instances that I am trying to add a table to, and both give me the same error:
The user does not have permission to perform this ...
- 93
0
votes
1
answer
768
views
User cannot query cross-database, despite having permissions for both databases
SQL Server 2017 Enterprise (14.0.3356.20), running on Windows Server 2016.
I have a situation where an application's user needs to join on a table in a separate database in the same instance, but I'm ...
- 399
0
votes
0
answers
14
views
db_datawriter and db_ddladmin on a server-level? [duplicate]
Ok, since SQL Server 2016 we have new server-level permissions that allow to grant connect and select to/on any exising or future databases on the server instance
use master
grant connect any ...
- 5,964
0
votes
1
answer
1k
views
SQL Server - Add signature to stored procedure by certificate does not work
I have a stored procedure in Database 1 that updates couple of tables in Database 1 and Database 2
To avoid granting calling login/user permissions to do table updates, I would like to use code ...
- 5,964
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
2
votes
1
answer
2k
views
Msg 15404, Level 16, State 10, Procedure xp_logininfo - missing permissions [duplicate]
I have a server based in the USA, where I am not a domain admin, and my permissions are limited to sql server only - where I am sysadmin.
after a migration of a few databases to a new server, I ...
- 16.6k
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
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
votes
2
answers
130
views
What permissions should be added to public users sql [closed]
I have a SQL Server and am looking at making it public.
What I mean by this is: I can create a DB and give access to myself and friends etc.
I need to create a login which can only view databases it ...
- 101
2
votes
3
answers
17k
views
SQL Server Agent Server Login Account
What SQL Server login does the SQL server agent use to log in to the database instance?
I am running the SQL server agent as the built in local system account.
I have created a SQL agent job which ...
- 2,083
0
votes
1
answer
4k
views
Create user with only SELECT and INSERT permissions
I'm working on a project that requires a database user to have only SELECT and INSERT permissions for all tables in a database.
The following question provides a solution but I'd have to run the ...
- 111