SQL Security :: Giving Access To A User Using DOS-Batch Script

Oct 20, 2015

Is it possible for a batch command to give access or add a new role for the user to database? Basically:

USE my_Database
CREATE USER [User1] FOR LOGIN [User1] WITH DEFAULT_SCHEMA=
[dbo];
EXEC sp_addrolemember 'Application1', 'User1'
EXEC sp_addrolemember 'SQL_Read_Only', 'User1'

If User1 was already created then ignore that and run the exec sp_addrolemember. Next, 

EXEC sys.sp_change_users_login
@Action = 'Update_One',
@UserNamePattern = 'User1',
@LoginName = 'User1',
@Password = NULL

to link the orphan user within database.

View 3 Replies


ADVERTISEMENT

Giving Access To User

Apr 8, 2008

Hi All,
I need to give access to one user only to truncate a particular table. I am not able to frame exact query for this. However i can user EM and do it.
But i wanted to know the query for this.


Thanks in advance.
-- Chetan

View 1 Replies View Related

SQL Security :: How To Grant Two Tables (select Only) Access To New User

May 5, 2015

i want to create new sql user and grant him two tables access. we have several databases created on same server so we want to allow only two table in ABC database. user should not be able to see other databases and their tables. And user also should not be able to access any other tables in ABC database except two tables.

is there any query to deny all tables in schema for all clauses (Select, Update, Insert) then grant two tables to user with select clause?

View 17 Replies View Related

User Is Not Able To Access The Database Model Under The Current Security Context

Feb 7, 2008

I have a restriced user on SQL Server that is only permitted to creat a new database and manage it only. All other database are hidden to that user.

when that user login to SQL Server and create a database and try to change the default folder path for data and log files, gets an error,

The Server principal "User" is not able to access the database "model" under the current security context. (MSSQL Server, Error: 916)

Any idea???

Thanks,

View 7 Replies View Related

SQL Security :: How To Find Who Alter Database User Access Changes In Server

Oct 6, 2015

someone was alter user  databases owner access.

how to find  who alter evoke  the database user access changes in sql server.

View 5 Replies View Related

SQL Security :: Adding A Windows Login Did Not Permit Access To End User Databases

Oct 24, 2015

Deleting a Login from a server instance and adding it back did not show that the login was still mapped to databases. In SQL Server 2008, adding a Windows Login did not permit access to end user databases until the Windows Login was mapped to various databases. In SQL Server 2012, once a Windows Login is added to SQL Server Security, it may access ANY end user databaseWe use the following to circumvent this problem, Windows Login by Windows Login: DENY VIEW ANY DATABASE TO [TESTTest1]

View 6 Replies View Related

SQL Security :: How To Disable User Access To The Critical And System Stored Procedures

Sep 22, 2015

disable the following system stored procedure (xp_grantlogin and xp_revokelogin)?

View 5 Replies View Related

Giving User Permissions Tp Columns

Oct 24, 2006

Hi

I have created a user and given select permissions on a table, I want to go deeper and just give select on a few columns within the table but unable to do so. Can someone tell me how I can do this.

Thanks

View 4 Replies View Related

Giving A User Permissions On Objects In A Schema

Nov 22, 2006

Hi,

SQL Server Security is not my strong point so forgive me for asking stupid questions.

 

I have a bunch of tables and sprocs within a schema 'MySchema'. I have a user 'MyUser' defined in the database.

I would like to give MyUser permission to SELECT from tables and EXECUTE all sprocs in MySchema. What is the simplest way of doing that? Will the following:

GRANT EXECUTE ON SCHEMA::[MySchema] TO [MyUser] WITH GRANT OPTION AS [db_owner]
GRANT SELECT ON SCHEMA::[MySchema] TO [MyUser] WITH GRANT OPTION

accomplish that? (I can't test it out at the moment because our DBA isn't around and I don't have permission)

 

With best practices in mind - is what I am doing here considered "ok". Any suggestions/comments are welcome.

-Jamie

 

P.S. Can anyone recommend any documentation that talks about what best practices should be in the use of schemas. BOL is a bit sparse. Thanks.

 

View 5 Replies View Related

Creating User And Giving Permission To All Databases

Feb 27, 2008



Hi,

Any body please give me some TSQL Stored procedure for

creating an SQL user and to give db access permission to all the databases available on a the server.

Mujeeb

View 5 Replies View Related

Getting Problem In Giving Database Permission To Group User

Jun 18, 2007

hi ,

I am getting some problem in using group in sql server login.

i have two NT groups in windows.i) developer and tester

I added a user Lalit in both gruop. I want to give db_owner permission on a particular database(Employee) to developer group and data_denyreader and deny_writer on same database(Employee) to tester group.



Now when i logging in to sql server using Lalit(windows authentication) iam not able to access the database(Employee).



Now my concern is that Lalit should have full access to the employee database when entered as developer user and limited access to employee database when entered as tester user.



how can i achieve this set up????



Please do help.

Thanks a lot in advance !!!!!!!!!!!!!!!!!!

View 1 Replies View Related

VIEW On MS SQL Giving Different Result That Query On ACCESS

Mar 13, 2008

Good morning,
I'm trying to create a view on SQL Server based on a query that i use on Access. The query returns detailed data from a table based on ID's selected and saved into another table.

the thing is, on access it returns back 5 records<which is a correct output> , while on SQL. its returning only one record. what could be the issue here ?

thank you,

View 3 Replies View Related

Security Concern - How To Avoid Hardcoding Passwords In Batch?

Feb 24, 2003

I would like to eliminate all hardcoded login ids and passwords and would like to know what other companies are doing to address this issue. I know a job scheduling software package is the ideal solution, but until dollars become available I need a short-term solution. We've tossed around the idea of using system variables.

Any recommendations?

Thanks, Dave

View 3 Replies View Related

Giving Someone Read-only Access To A Database -- Beginner Question

Jun 29, 2007

Sorry for the simple question, but I'm not having any luck getting this to work: I need to give someone read-only access to everything in a database.



If someone could help me by giving me (or linking me to) a step-by-step, idiot-proof recipe for giving Bob in my office read-only access to the whole database, I'd be very grateful. And remember: I'm good at db programming, but completely ignorant on the subject of db admin.

View 3 Replies View Related

Profiler Does Not Comment Out Security Related Commands When There Is An Error In The Batch

Aug 1, 2006

i am testing some encryption
scenarios ,in profiler the statements like "OPEN KEY" and all "Encrypt"
and "Decrypt" functions are removed automaticly from the trace
and replaced with a comment ,create a trace and try the code i attached ,
you will see in profiler trace that that all encryption related commands
are commented out ,this is what expected.

but now go to the batch and comment out the "SELECT @rrr' statment,
and run the batch ,this batch will fail beacuse "@rrr" is not declared,
now go back to profiler and you will see that for the failed batch
all the encryption command are NOT COMMENTED OUT !!!
esspecially important is the visibility of the password of the open key command.


seems like a very dangerous bug to me!!!

CREATE CERTIFICATE test1
ENCRYPTION BY PASSWORD = 'pGFD4bb925DGvbd2439587y'
WITH SUBJECT = 'Sammamish Shipping Records',
EXPIRY_DATE = '10/31/2009';
GO

CREATE SYMMETRIC KEY Key09 WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY CERTIFICATE test1;
GO
declare @Str nvarchar(100)
declare @Enc varbinary(max)
set @Str = 'encrypt this'
OPEN SYMMETRIC KEY Key09
decryption by CERTIFICATE test1 WITH PASSWORD = 'pGFD4bb925DGvbd2439587y'

SET @Enc
= EncryptByKey(Key_GUID('Key09'), @Str);
---select @rrr
select CONVERT(nvarchar(100), DecryptByKey(@Enc))
go




View 1 Replies View Related

Code Access Security Across Multiple Assembly Security Extension

Oct 14, 2005

Hello there I have trying to figure out for days how to enable FullTrust for my Reporting Services security extension.

View 9 Replies View Related

Reporting Services 2005 - Giving Access To All AD-users For A Specific Folder

Jan 21, 2008

I am in a project where we are using a vanillla Reporting Services 2005 with the builtin report portal. No sharepoint integration yet.

We have successful deployments where we limit access to different folders based on AD-accounts and groups. In this particular case I have a folder for which I would like to allow access to all AD-users within the entire company.


So basically I know how to limit access but I don't know how to enable access for everyone. Is there a simple way to do it? I have googled and search mshelp but I couldn't find anything. I will admit to the search being quite quick but as usual time is short.

View 1 Replies View Related

SQL Security :: How To Copy Permission Of A User To Newly Created User

Oct 24, 2015

How can i assign permissions to a newly created users as of an existing user?

View 3 Replies View Related

SQL Server 2005: User Security Management: User Ch

Jun 25, 2007

How do you handle user level security with SQL Server 2005?

Say I have an HR database.

In Active Directory I have two groups: Managers, Employees.

Now in this HR Database I want to setup permissions in such a way that Managers can see all employees under them (but not other managers) and the employees can only see themselves.

(I'd have various levels of management defined in a table somewhere, so that each employee has a manager ID that links to another employee so that the CEO would be manager of everyone by working down the chain).

What I'm trying to understand is the best way to handle the permissions.

I'm not entirely clear on how to deal with that.

Would I use user chaining to do that, I wouldn't need impersonation (that's just for instances where you want dynamic SQL and it won't execute with user chaining, correct?)

Anyway, just looking for some general direction on this (obviously I need to get a good book it would seem).


Would I create a stored procedure that runs with EXECUTE AS permissions so that I'd have a non-interactive login it uses that has table access then all the other users have permission to execute the sproc?

So that sproc runs, pulls back a SELECT * FROM tbl_HRINFO and using a WHERE constraint limits who is returned WHERE SupervisorID = CurrentLoggedInEmployeeID ?

Also: How can I determine who is logged in and running the procedure, would the sproc use the SELECT USER_NAME command to see who was running it?

As you can see, I'm working from square one on all of this.
Not sure if my posting entirely made sense, but hopefully someone can get me pointed in the right direction, thanks!

View 3 Replies View Related

How To Assign Execute Permission To Batch Of Stored Procs To A User/role

Mar 25, 2008

Just wondering if anyone knows of a useful command to assign execute permissions to a batch of stored procs to a user/role. I've got too many stored procs to manually go thru the steps of browsing for them and scrolling thru each one and clicking "execute" for each one.

Also, would like to know if its possible to update a batch of stored procs that begin with a prefix like "spSomething_".

Any info would be helpful! TIA.

View 3 Replies View Related

Best Way To Return Data From SQL Database, Without Giving Access To Database

May 8, 2008

Hello,

I have a quick question regarding getting data from a SQL database but I am slightly confused as to the best way to handle it.

Basically on of the projects I am working on I need to send data to another company, there are several calls required to the database to bring back various options for changing the questions asked on the front end.

Up to now all that has happened is there has been a mutual agreement between myself and this other company and they have just had access to the database to call a series of stored procs which I have written for them to access the data. Recently however the situation has changed and my client wants me stop them accessing the database however they still need to recieve the information from the database they recieve now.

What will be the best way to handle this? My knowledge of SQL is farily limited presently and I am only ust getting into learning a lot more about it.

Any help and advice would be greatly appreciated.

Kind regards,

Lee

View 5 Replies View Related

SQL Server 2012 :: Query To Find User Who Last Modified User Roles / Access?

Dec 6, 2013

I would like to know if there is a way to find out who changed a users roles/access WITHOUT using the audit function. For example, if a user account was created and given SA access then changed to read only, how can I find out who made that change? I tried searching for an answer, but kept getting no results. I'm thinking this may tie into the sys.sysusers view?

View 3 Replies View Related

How To Setup The User Access Right To A BackupOperator User In SQL2005.. Thanks

Nov 21, 2007

I would like the Backup SQL user A can backup and restore DATABASE_a, what access right I need to grant to him. Although he can backup the database_a to the c:Program Files.....Database_a.BAK but he cannot restore it with some message said ''not enough security privieges..

Please quote some example right assigment so that I can replicate to our environment

Many Thanks...

View 6 Replies View Related

Multi-user Access Through A Data-access Layer/remoting Server

Oct 30, 2007

Hi guys,

I've been developing desktop client-server and web apps and have used Access and SQL Server Standard most of the time.
I'm looking into using SQL CE, and had a few questions that I can't seem to get a clear picture on:

- The documentation for CE says that it supports 256 simultaneous connections and offers the Isolation levels, Transactions, Locking, etc with a 4GB DB. But most people say that CE is strictly a single-user DB and should not be used as a DB Server.
Could CE be extended for use as a multi-user DB Server by creating a custom server such as a .NET Remoting Server hosted through a Windows Service (or any other custom host) on a machine whereby the CE DB would run in-process with this server on the machine which would then be accessed by multiple users from multiple machines??
Clients PCs -> Server PC hosting Remoting Service -> ADO.NET -> SQL CE

- and further more can we use Enterprise Services (Serviced Components) to connect to SQL CE and further extend this model to offer a pure high-quality DB Server?
Clients PCs -> Server PC hosting Remoting Service -> Enterprise Services -> ADO.NET -> SQL CE

Seems quite doable to me, but I may be wrong..please let me know either ways

Thanks,
CP

View 3 Replies View Related

How To: Determine If Current Windows User Has Login Access, Database Access And If They Are A Member Of A Specific DB Role.

Mar 25, 2008


I need to determine the following about the current authenticated Windows domain user who is trying to access a SQL Server via a trusted connection.

1 Has the current user been granted login access to the trusted SQL Server?

2 Has the current user been granted access to a specific database?

3 Is the current user a member of a specific database role such as (DB_ROLE_ADMINISTRATORS)?

Thanks,
Sean

View 6 Replies View Related

Reporting Services :: Check Access Fails To Grant Access To Report Item For Current User

Sep 10, 2015

Is there any way to get more information for when IAuthorizationExtension::CheckAccess fails to grant access to a report item for the current user? Specifically, it would be useful to know:

1. URL of attempted report
2. IP address of user agent
3. Identity of current user
4. Date/Time of the failed attempt

ssrs2014

View 7 Replies View Related

User Security

Mar 14, 2008

Hi all,



I want to create a user with the following criteria:


The user can able to "CREATE","ALTER" the stored proceudres but not "DROP" them.


So for this, I did like this:
--Schema Level

grant control on SCHEMA::dbo TO username


--Procudure level

GRANT CREATE PROCEDURE TO username


--Denying on schema level

Deny alter on schema::dbo TO username --------- But, if i do this, then the user cannot able to create anything in the schema.


Can anyone give the workaround so that the user can able to "Create" as well "Alter" the stored procedure but not able to "Drop" the stored procedure.


Thanks.

View 3 Replies View Related

DTS And NT Integrated Security User?

Dec 2, 1999

We have a a DTS package set up to run against another SQL Server. Using an integrated login is there a way to map an NT Authenticated users is
the sql server login id mapping to this attached server. The DB we are going against only uses NT authentication to attach to.

View 3 Replies View Related

Modifying User Security

Jul 20, 2005

I have an asp page that currently is creating a database and a userlogin for that database. After everything successfully (I thought)executed, I tried to change my connection properties for the serverand then login as this new user. It wouldn't allow me to, so I loggedback in as the administrator and looked at the properties for the newlogin. On the general tab, it had the user's default databasespecified as the new database that I had created in the asp page, butwhen I went to the database access tab, the database was not selected.So, I'm not sure how to set that in my script. I've done somesearching in BOL, but I can't figure it out. Also, if there's a wayto do this in a query, or stored procedure, will it also specify whattype of role the user has (public, db_owner, etc.)? Thanks.

View 5 Replies View Related

User Dependent Security

May 10, 2007

I was just analysing the security which can be given to different users to access respective databases. So i tried, on my local server, to deny permission to myself to access Model database. After this i am not able to connect to my local server at all. Error: Permission denied. I am using windows authentication mode.

I have also deleted the local server registration, and re-registered it, but still the condition is same. Do i need to uninstall SQL Server completly to get rid of this prob.? I also registered a new data server, and there every thing is going fine. So now what do i do to get connected to my local server.

View 5 Replies View Related

SQL Security :: Schema Name Same As User Name

Jun 28, 2015

In some our dotabases I can see Schemas created with the same name as Domain User name (domainusername). Schema owner for those schemas is not dbo but the same user as in schema name. How this happens? Is any way to prevent or prohibit this?

View 9 Replies View Related

User Security Audit

Jan 16, 2008

What is the easiest way to find out what objects a security login has mapped to it? Something that would show all the explicit grants a specific user has.

View 6 Replies View Related

SQL 2012 :: User For Row Level Security

Feb 19, 2014

In database we are planning to implement row level security.For this we need to create users or we need create login for each one?

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved