Tag Info

Hot answers tagged

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 ...


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, ...


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 ...


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

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 ...


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 = ...


1

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 ...


1

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 ...


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 ...


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.



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