Tag Info

Hot answers tagged

8

Kendra here (the author of sp_BlitzIndex) First, thanks for being interested in the procedure and trying it out. Azure doesn't expose all the dynamic management views that we get in the boxed product. I do actually reference sys.dm_db_partition_stats, but there's other info I need to get from sys.partitions for other users. (Is it using compression? What ...


6

You can impersonate that login and use the HAS_DBACCESS built-in function. USE master; GO EXECUTE AS LOGIN = N'your_login_name'; GO SELECT name, HAS_DBACCESS(name) FROM sys.databases; GO REVERT; If you need to find the user mapping in each database (e.g. the user name in the database is not always the same as the login name), you can use dynamic SQL, ...


5

Have you looked under Advanced Security Settings? The arrows point to the modifications I've often had to specifically apply when installing SQL Server on Windows 2012 R2. For the SQL Server Root Directory, I'll give full control to the SQL Server Process account. Related official documentation: Configure File System Permissions for Database Engine ...


4

You're right: system administrators can gain sysadmin access, but you will find entries in the default trace for that action. Moreover, it requires restarting the service at least twice, which would hardly go unnoticed. Not granting sysadmin privileges to BUILTIN\Administrators is a change introduced in SQL Server 2008. In my opinion it's a good idea: it ...


4

A sysadmin can't demote themselves temporarily / most of the time / etc. What you want to do is create a SQL auth login that you use most of the time, and it only has the privileges to do the limited things you want to do. Then when you want to perform DDL operations, log in as your Windows login. There are probably ways to do this if your primary login is ...


3

You can use System Stored Procedure sp_msloginmappings which show account mapping information in all databases -- Create Temp Table CREATE TABLE #loginmappings ( LoginName nvarchar(max), DBName nvarchar(max), Username nvarchar(max), AliasName nvarchar(max) ) -- Execute System Stored Procedure INSERT INTO #loginmappings EXEC ...


3

Please RUN the following: select user(); select current_user(); The former is who you are attempting to login as; the second is what the database sees as connected. You will need to run grant_priv to user. Here is how to verify: mysql> SELECT host,user,password,Grant_priv,Super_priv FROM mysql.user If the grant_priv is set to 'N' for your user, ...


3

GRANT is a DDL statement. You can not run DDL statements directly in PL/SQL, but you can use execute immediate: CREATE OR REPLACE PROCEDURE "setUserPermissions" (u VARCHAR2, l INTEGER, tbl VARCHAR2) AS BEGIN DELETE FROM "userLvl" WHERE "user" = u; INSERT INTO "userLvl" VALUES(u,l); IF l>0 AND l<6 THEN execute immediate 'GRANT ...


3

On a clean install, just do: SELECT role_name = pr.name, member_name = m.name FROM sys.server_role_members AS r INNER JOIN sys.server_principals AS m ON r.member_principal_id = m.principal_id INNER JOIN sys.server_principals AS pr ON r.role_principal_id = pr.principal_id; Here's what I get: sysadmin sa sysadmin CANUCK\aaronbertrand -- Administrator ...


2

In order to give some privileged users access to xp_sqlagent_enum_jobs we did the following. Create a Database Role in master named something like SQLAgentJobManager. Granted that role EXECUTE rights on xp_sqlagent_enum_jobs. Then via Logins or a Domain group, granted users a membership in the SQLAgentJobManager role in master. EDIT: The way we ...


2

Unless NT Auth\System has server level access that covers all of the databases (for example sysadmin (not recommended)) then why would you think it should have access to all of the databases? Permissions for the most part are handled on a database by database level. You can look at it through the GUI (security dropdown under the DB in the object explorer ...


2

Using the local administrators group has been the default for a long time. But mainly in large enterprise environments you have an operations team for windows and a seperate operations team for SQL Server. Allowing everone to easily(!) gain access can be considered as security problem. Allowing only the current user can be explained with "secure by default". ...


2

The problem here is that I am invoking mysql as user danielsank, but that user does not have privileges to make new tables, nor to see the mysql database! The issue becomes clear by invoking mysql as root $ mysql -u root mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql ...


2

Don't grant ssis_admin if the dev shouldn't be sysadmins: Members of the db_ssisadmin role and the dc_admin role may be able to elevate their privileges to sysadmin. Source: https://msdn.microsoft.com/en-us/library/ms141053.aspx Personally I would never let the devs create jobs with their accounts. As soon as they leave the company and someone ...


2

Check the server property BuiltinAdminsAreServerAdmins. Till SQL Server 2014 it defaults to true. Not sure if it will be changed in 2016 (hopefully). Link: https://msdn.microsoft.com/en-us/library/ms174561(v=sql.120).aspx


1

Just to add on, the removal of BUILTIN\Administrators and NT AUTHORITY\SYSTEM from being default sysadmin was for the change in security with Microsoft products; going to the secure by default methodology. I am sure some portion of compliance had influence in Microsoft and SQL Server team choosing to do this as well. Which actually, the main one that ...


1

I got a response through their help email. The full text is below: Hi! sp_blitz doesn't work in Azure. The value of Azure is letting someone else be your DBA. ;-) I guess the answer to my question is be bold or find another tool... NOTE: They mentioned "sp_blitz" instead of "sp_blitzindex" in their email response. That might be a simple typo, but I ...


1

There is one simple enough scenario where I could see permissions being REVOKEd/DENYed for SELECT and GRANTing INSERT/UPDATE/DELETE makes sense. Suppose one works for an institution wheire confidentiality is crucial and any large scale publication and/or loss of user data would be disastrous. Say, a bank or a medical insurance company? You take the agents ...



Only top voted, non community-wiki answers of a minimum length are eligible