Tag Info

New answers tagged

3

The documentation about ALTER DEFAULT PRIVILEGES tells you a possible reason - it is not that clearly described, though. Let's see, what is said: You can change default privileges only for objects that will be created by yourself or by roles that you are a member of. This means that the default privileges defined by this statement applies only to ...


0

We use below query to look for the logins mapped as user to specific databases. Hope it helps. CREATE TABLE #tempww ( LoginName nvarchar(max), DBname nvarchar(max), Username nvarchar(max), AliasName nvarchar(max) ) INSERT INTO #tempww EXEC master..sp_msloginmappings -- display results SELECT * FROM #tempww ORDER BY dbname, username ...


0

The query below will return the schema owner information. You can change the JOIN to sys.database_principals to a LEFT JOIN to also return schema owners that are not logins, such as database roles and users without a login. SELECT sp.name AS OwningLogin , dp.name AS OwningUser , s.name AS SchemaName FROM sys.schemas AS s JOIN ...


0

Has to be a PROXY situation. As MySQL's documentation says: When authentication to the MySQL server occurs by means of an authentication plugin, the plugin may request that the connecting (external) user be treated as a different user for privilege-checking purposes. This enables the external user to be a proxy for the second user; that is, to ...


2

That is not part of the definition of SQLAgentOperatorRole permissions. The login being an owner of database "test" does not influence how SQL Agent jobs are managed. Being a db_owner of the database means that a job with that owner can take almost any action within the "test" database, but it says little about operating the SQL Agent job itself The ...


2

According to the SQL Server documentation, a user with a SQLAgentOperatorRole can only modify jobs that it created itself.


2

I am not sure of any other alternative but i believe user needs to be there in MSDB or should have the admin rights to execute or perform any required action on the job. There is a connect item about this as well, SQL2005 SQLAgent MSDB security roles Or you can use an option as mentioned here: You could create a stored procedure that runs the job. You ...


3

By default domain admins don't have access to a SQL 2012 instance, because by default builtin\administrator (which are the local admins on the machine) are not a login in SQL Server itself. The default has changed starting 2008 By default, the local Windows Group BUILTIN\Administrator is no longer included in the SQL Server sysadmin fixed server role ...


2

Off guess you are missing the connect permission. I'd always thought that was created with the user but there may be conditions where it isn't added. Try running USE [MyDatabase]; GO GRANT CONNECT TO [MyDomain\MyAccount]; You could also try changing the default schema of [MyDomain\MyAccount] ALTER USER [MyDomain\MyAccount] WITH DEFAULT_SCHEMA = ...


5

By default, no direct access is permitted to either tables or stored procedures. One must explicitly grant access in order for non-privileged users to have access. The best practice to simplify security administration is to grant permissions to roles, and control access via role membership. If you want to restrict access only via stored procedures, take a ...


1

It'll involve 1) removing the user from any database fixed roles like db_datareader (only public will remain), 2) creating a custom role in the DB, 3) granting the new role specific permissions on the one table, and 4) adding the user as a member of the new role. You'll also want to make sure public has no grants to user objects as all users in a DB are a ...


0

I ran into a similar problem and while this wasn't what happened here, this answer may help others running into this. Make sure the OS group mygroup is all lowercase. DB2 on AIX does not recognize uppercase letters in the group name and will fail to validate a user belongs to a group if that group name has uppercase letters. eg: # id useraccounthere ...


4

This has nothing to do with SQL Server, is just basic Windows authentication. In a workgroup you can only authenticate using NTLM mirrored accounts. Mirrored accounts are local accounts that have identical name and and password on different machines. So to use Windows authentication between a process running on machine A and a process running on machine B, ...


0

If you are trying to prevent the owner of the instance or any Windows or SQL Server administrators from being able to access your database, the answer is no. Essentially you should assume that any administrator on the box owns and can gain access to everything on the box, including your database. You may be able to protect the actual data in some cases ...


0

I figured it out myself. First, I created an account that ONLY had permissions to certain columns (not all). I then created a view for those columns and used the user as the definer. Then, I created another test account and ONLY gave them permissions to that view. So the test user could not see the underlying table but only the view and columns. Works ...


0

I was getting the same error, i was using a proxy account with my own database security credentials, and the password had changed. Once i changed it in the security ->credentials, the package started working..


0

The solution was that job needed to has an access to the default folder (on the server) C:\Users\Default\AppData\Local\Microsoft\SQL Server\Smo My colleage solve it and according to him it is some kind of bug which is patched in newer version of SQL Server.


1

Backing up a step -- to answer the question behind the question: How many rows in the table? PARTITIONing is rarely of any benefit on tables with fewer than a million rows. If your goal is to make use of multiple drives, RAID striping is better and any manual spreading of data between drives.


0

Figured it out. A bad script granted database-wide SELECT permissions to the user. Found it using this script: WITH perms_cte as ( select USER_NAME(p.grantee_principal_id) AS principal_name, dp.principal_id, dp.type_desc AS principal_type_desc, p.class_desc, OBJECT_NAME(p.major_id) ...


0

Martin Smith's comment ("Does the subquery itself work?") was ultimately the answer and highlighted my insufficient forensic work! ;-) The subquery was returning ROW COUNTS from tables: ... INNER JOIN sys.dm_db_partition_stats AS ddps ON i.OBJECT_ID = ddps.OBJECT_ID ... The fixed roles I was using don't give VIEW DATABASE STATE permission (which is ...


1

An owner of a database object has full rights on it automatically and unrevokably. But of course you can give an user all rights to a schema without making him the owner.



Top 50 recent answers are included