All Questions
Tagged with users sql-server
114 questions
0
votes
1
answer
1k
views
SQL Server Job running as different user
A year or so ago I started working on a project to set up a job to run a stored procedure. I've now come back to this to finish it off. When I run the job I get an error that he job is running under ...
- 123
0
votes
0
answers
232
views
what permissions does user need to have to use linked server and its default remote user mapping
i have some linkedserver and for it created default maping that mapping list is empty
and all goes thrue 'Be made using this seciurity context'
and this works fine - i can querry this linked from any ...
- 103
0
votes
1
answer
3k
views
Why SQL Server Job fails with "EXECUTE AS LOGIN failed for the requested login"?
I created a SQL Server job, in which:
My user is set as the owner of the job
No user is specified for run as at any step.
The job failed with the error:
'EXECUTE AS LOGIN' failed for the requested ...
- 457
-1
votes
1
answer
445
views
SQL Server can't login in any user by SQL Authentication
So I just installed SQL Server Developer and SQL Management Studio in my personal computer.
I have worked with SQL Server before but I never had to install and configure one so I was quite lost.
The ...
0
votes
1
answer
802
views
What is login token and user token in SQL Server?
I came across these two SQL Server catalog views today: sys.login_token and sys.user_token. The document states that they:
Returns one row for every server principal that is part of the login token.
...
- 1,219
2
votes
2
answers
2k
views
DACPAC drops existing users and permissions from database when upgrading through SSMS
I'm developing my database through a database project in visual studio, and whenever I need to upgrade the schema of my database I like to use SSMS to do this using a DACPAC.
But whenever I use my ...
2
votes
1
answer
907
views
Error trying to add a login to Azure SQL Special Role 'dbmanager'
Upon running these commands on an Azure SQL instance:
CREATE LOGIN test WITH PASSWORD=N'Passw0rd'
GO
ALTER ROLE dbmanager ADD MEMBER test
GO
... I'm getting an error:
Cannot add the server principal ...
- 559
1
vote
0
answers
107
views
Can you synchronize user accounts with specific DB permissions with records found in a table?
Background / Current Setup
I have a database in a SQL server that uses row level security to determine what records a user has access to.
Users will have two methods to interact with the database. ...
- 113
13
votes
2
answers
32k
views
Is there a simple way to add a read-only user?
I have a hosted (shared) SQL Server database and I'd like to add a read-only login. (This login will be used to build Power BI data visualization.)
Looking into this, it's far more complex than I ...
- 347
0
votes
1
answer
2k
views
How to restrict Users to see only their databases where has access? [duplicate]
I need to restrict the list of database for our users in a SQL Instance
In order to move forward this
I revoked the view any database permission to the user desired.
But I figured out that only ...
- 21
1
vote
0
answers
2k
views
Create multi-user database schema with roles, permissions, and services
I am in the process of designing a user administration database and I am stuck in the creation of the tables and their relationships, in order to explain myself below I expose the requirements of the ...
- 11
0
votes
0
answers
112
views
AAD users lose access to database after import
I googled for the last few days but I got a bit confused and I do not know how to proceed further. Also, I'm not a DBA and I was just faced with this situation for the first time.
Using sqlpackage.exe ...
9
votes
2
answers
20k
views
Why and how and where to use user without login
I've been trying to find why, how and where to use Users Without Login and I didn't find my exact question or anything that gets closer.
Where I did my research (I also read other posts, saw Youtube ...
2
votes
5
answers
2k
views
CREATE Windows User in database for not existing domain
Is it possible to create a Windows User in the database when the domain is not accessible?
I am preparing database at my dev environment outside of the targeted domain.
Created user is not expected to ...
- 945
1
vote
0
answers
171
views
Is there any way to recklessly drop a principal leaving orphaned objects or should I assume only data corruption could cause it?
Given that SQL Server blocks me when I try to DROP a principal that owns an object in the database, if I see an object which the owner no longer exists, should I assume right away it was data ...
- 5,926
0
votes
0
answers
31
views
Why I cannot do this in SSMS directly? (assigning a LOGIN to a USER)
In SQL Server 2008 R2, I want to change the LOGIN for a USER in a database.
FROM THIS:
TO THIS:
Initially, the database has the [...] dots option to change this greyed-out (why?):
The only way to ...
- 295
-1
votes
1
answer
77
views
User having execute procedure permission in MS SQL
Dears,
Greetings,
How can I create a new user in Microsoft SQL Database, so that user must have only execute permission on only 1 procedure in one of Ms SQL Database.
Regards,
Salahuddin.
0
votes
1
answer
170
views
Database level access in Azure SQL managed instance
In my Azure SQL managed instance I have two databases, Database_A and Database_B.
User A_ABC has access only to Database_A and he is part of role db_owner.
When the tries to alter a table we are ...
- 131
0
votes
1
answer
331
views
Query for: view users and permissions (roles) from root security folder (MS SQL)
On the Internet I found a lot of scripts how to display users and their permissions under a particular database (Security folder under table) - for example sp_helpuser.
Currently, however, I need to ...
- 13
1
vote
1
answer
336
views
SQL server database Role management - Azure SQL MI
In my database I have multiple users with SQL server authentication login and AD login.
For ex: User 1 and user 2 are from same team(ABC), if I create a role db_abc
User 1 needs read access to table ...
- 131
2
votes
1
answer
245
views
User and Schema Recreated during Stored Procedure Creation
As mentioned in the subject, a new user and schema are being created when a developer I'm working with creates a stored procedure. Originally I made a user and login for that developer individually ...
- 23
2
votes
1
answer
7k
views
How do I force the delete of users from my database?
I am making a clean backup of our development database for easy fresh spin-ups of the database. This database has about 200 test users that should be deleted to make it truly clean, but the problem is ...
- 685
5
votes
1
answer
8k
views
Why can't I create a new schema with the owner set to dbo?
I created a test account for checking permissions against.
It currently has the db_datareader, db_datawriter, and db_ddladmin roles for the Northwind database.
But when it tries to create a new schema ...
- 39.5k
5
votes
1
answer
766
views
User Mapping on a DB that's part of an AG. DB is READ only? [duplicate]
I'm a sys admin being tossed in to a DBA role. I currently have two SQL Server boxes, 2016 Enterprise, in an AG. The DB has synchronized without any issues.
A third party vendor needs to have an ...
- 51
1
vote
1
answer
594
views
Add a DL as a login in azure sql managed instace
I am able to add individual AD logins as a login in azure sql managed instance, when i try to add a DL is the same way i am getting the following error.
error: Principal '' could not be found or this ...
- 131
0
votes
1
answer
125
views
Will an AD Account inherit all the User Mappings to databases that it's parent AD Group is mapped to?
If a Windows AD Group and an AD Account that belongs to that AD Group are both setup as Logins on a SQL server, will the AD Account Login inherit all the User Mappings that it's parent AD Group Login ...
- 39.5k
1
vote
0
answers
84
views
SQL Server Permissions for Intellisense for an Active Directory User? [duplicate]
I have created an Active Directory Group and created it as a user, we'll call it "AD SQL", in my DB. I can successfully login in using Active Directory Authentication and read/write.
However, ...
- 11
-2
votes
2
answers
4k
views
find date and time user last access database [closed]
I need to find the last time a user accessed the database. I am new to SQL and rarely have to use it. Details would help or links to learn those details.
1
vote
0
answers
62
views
How to Measure data consumed by an user in Azure sql server database?
For an Azure sql server MI, we have multiple external users consuming data,
I would like to know how much data an user is consuming/extracting from the DB?
Can this be checked from,
1.) From SSMS ...
- 131
1
vote
2
answers
130
views
Can a user with readonly access change SQL server settings
Database administration is not a strong point of mine, and I was wondering if a user with read only access can change the server settings.
In their query I can see this at the beginning:
SET ...
- 165
0
votes
1
answer
229
views
Orphaned users with their respective database name [duplicate]
I need to get a list of the orphaned users in each DB, together with the database name in which they are found in. I did the below which gives the orphaned users in each DB, but I cannot figure out ...
- 349
1
vote
1
answer
1k
views
Delete orphaned SQL Users
I have a Microsoft SQL Server that had a database called SQLDATABASE. It also had a user called SQLUSER. The SQLDATABASE was deleted but not SQLUSER. SQLUSER only belonged to SQLDATABASE. I want to ...
- 13
2
votes
2
answers
12k
views
Cannot open database "dbname" requested by login.The login failed.Login failed for user 'DOMAIN\Windowsusername'
I had a database which i dropped using the below command.
USE master;
GO
ALTER DATABASE dbname SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
DROP DATABASE dbname;
Now when i refresh the connection in ...
- 1,335
1
vote
1
answer
160
views
Granting permissions to a DB user - SQL Server
I need to grant select and update permissions to a DB user (TestUser) on TestDB database as per below.
When I press ok and re-check the permissions, they are not visible as can be shown below:
From ...
- 349
0
votes
2
answers
4k
views
User and login mapping after an SQL Server restore
I need to restore a DB from an SQL Server instance to another and I am well aware that the DB users will be transferred but the logins won't. I have created a script to be able to create the logins on ...
- 349
0
votes
0
answers
425
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
2
votes
2
answers
387
views
Permission on Database [closed]
I have a login user called TestUser created at the server level that is assigned to a read only role, now I need to create a user for the TestUser login on MyDb
USE [MyDB]
GO
CREATE USER [Test] FOR ...
- 45
1
vote
1
answer
1k
views
Assign Permissions to a SQL User
I have active directory on my company, I have some login users created at the level of the SQL Server (2014) that I need to propagate to all my databases on that server. I want to create a read only ...
- 45
0
votes
1
answer
128
views
How to check Auditing in Azure MSSQL MI?
I know that auditing is currently enabled in my azure MSSQL server, but how can I access these log files?
Could it be done through SSMS?
I need to know details about user activity in my databaase.
- 131
3
votes
1
answer
33k
views
Find queries executed by an user in sql server
I want to find all the queries executed by an particular user in a database over a period of time.
I learnt about trace and XEvents, but these are not setup yet, Will i be still able to get the ...
- 131
2
votes
1
answer
3k
views
Naming Convention for Login, User and Roles? [closed]
What are the Naming Conventions for a Login, A database User and a Database role?
Is there any best practice to follow?
- 131
1
vote
1
answer
96
views
Privileges on SQL Server
I come from Oracle Database and now I am trying learn SQL Server and I don't undestand why may I viewer others databases, like the master for example, with a common user without privileges in this ...
- 801
1
vote
1
answer
381
views
Microsoft SQL Server v13 bulk add users from active directory
I would like to import all users from Active Directory into Security/Users.
What I do is the following:
Go to Security -> Users
Right click Users -> New User...
User type: Windows user, click on '......
- 13
1
vote
3
answers
21k
views
List all users on SQL Server for auditing
I'm using SQL Server Management Studio 17 and I'm trying to list all the users for auditing in an organized way with mentioning their database name.
I tried the query below but it's showing the ...
- 11
0
votes
1
answer
1k
views
How do I access a user's application username when that application connects to the DB?
I'd like to audit changes to a table in SQLServer using a trigger and an audit table to do so. End-users connect to the database through an application. If I use SYSTEM_USER to obtain the end user's ...
- 1,645
0
votes
0
answers
72
views
Setting up a Database for Multiple User Access
I am working on revamping the database at the company I work at. I've been making decent progress getting various parts set up, but something occurred to me today that I hadn't really thought to ...
- 391
38
votes
3
answers
66k
views
What does a RED X on a database user mean?
I created two new AD groups and added them as Users of a database, but their icons show with a red X.
What does this mean?
user18718
1
vote
1
answer
136
views
Create domain user offline
Can I add domain users to a database, when Sql Server A (the development server) is not part of the domain and has absolutely no access to the domain?
The domain users are of course not expected to ...
- 361
1
vote
1
answer
542
views
Object Level Security
I have a database that will be shared across multiple related types of applications for different users. I want to be able to restrict each database user to essentially have full control over their ...
- 39.5k
4
votes
2
answers
6k
views
SQL Server Service Accounts
We have a new Windows 2016 server and I have to install SQL Server 2016 standard edition. Once I install and configure the SQL server I will move my existing production database to the new db server.
...
- 157