If a user is mapped to "master", (in login properties, user mapping) are they able to access all dbs, even though "master" is the only one with the check mark?
I need to create a new login with SELECT rights so the users can view all tables with no UPDATE, DELETE, OR INSERT rights. But this user needs to be able to CREATE VIEWS. I have assigned the user to the Public role and gone in and modified Securables for the Database to be able to CREATE VIEW. When I connect using my new user and try to create a view, I get the error message: CREATE VIEW permissions denied in database 'test01'.
- restore a backup of a 3rd party database onto one of our servers - this has no users that I can use - there is some ETL processing so we're using Control-M to manage the process - create a database user and grant it db_reader.
I'd like to do this without granting any users elevated privileges if possible.
What I've done so far is grant the Control-M user (this is a domain user) dbcreator rights and made it owner of our copy of the database that is being refreshed.
The refresh is completing, but Control-M is not able to log onto the database to create the user.
What is the best way to accomplish this task without granting the control-m user sysadmin rights?
Would I be able to do it if I used a SQL Agent job for the restore and user creation?
I want to create a view to get records from multiple tables. I have a UserID in all the tables. When I pass UserID to view it should get records from multiple tables. I have a table
UserInfo with as data as UserID=1, FName = John, LName=Abraham and Industry = 2. I have a Industry table with data as ID=1 and Name= Sports, ID =2 and Name= Film.
When I query view where UserID=1 it should return record as
I wonder if anyone can help. I'm trying to create a new user/login to a sql server 2000 box which will automatically have access to all the user databases on the instance. The user can't be a SA or anything however. It seems this can be done as the application which uses all these database has created such a user, however I need another which I will then make read-only.
I have been struggling with this one for awhile now.I have a domain group which only must view the steps and history of all agent jobs.I have added the group to the sqlagentreadergroup.I have created a new role and denied this role,add job,update job,delete job etc execute permissions.But the user still can change ,delete or create a new job.
All the groups and users in th new role,does not have sysadmin rights.
we have sql 2012 enterprise version
What else can i try.I need this for audit purposes.
I have two databases DB1 and DB2 DB1 has a source table named 'Source' I have created a login 'Test_user' in DB2 with Public access. I have also created a view named 'Test_view' in DB2 which references data from DB1.dbo.Source
I have two databases DB1 and DB2 DB1 has a source table named 'Source' I have created a login 'Test_user' in DB2 with Public access. I have also created a view named 'Test_view' in DB2 which references data from DB1.dbo.Source
Hi I am using sql express to automatically generate users and logins from t-sql. I seem to be having a problem when it comes to restricting their access though. I only want them to be able to select on views and execute stored procedures. at the moment I have created a new database role, schema, login, and user, then added the user to the role but how do I restrict access to the above areas? do the restrictions go on the role, schema, or user? I must confess I find the whole schema and role issue a little confusing and just when I think I've got it, it turns out I haven't :( thanks
In SQL Server 2005 SP2 I want to grant the ability to create views to a user but in order to do this it requires that the users has the ability to grant alter on a schema.
Is there any way to grant this privilage without granting alter on schema also?
Write a CREATE VIEW statement that defines a view named Invoice Basic that returns three columns: VendorName, InvoiceNumber, and InvoiceTotal. Then, write a SELECT statement that returns all of the columns in the view, sorted by VendorName, where the first letter of the vendor name is N, O, or P.
This is what I have so far,
CREATE VIEW InvoiceBasic AS SELECT VendorName, InvoiceNumber, InvoiceTotal From Vendors JOIN Invoices ON Vendors.VendorID = Invoices.VendorID
Is there a way to access your views in x database from the master database?
I'm getting this error
Invalid object name 'v_StatisticsScalars'. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Invalid object name 'v_StatisticsScalars'.
I have a webpage that should display the database names from the master.sysdatabases in a dropdownlist. The code for the stored procedure works fine in Query Analyzer and returns the list of all database names under that instance of SQL-Server 2000. The code: select name from master.dbo.sysdatabases order by 1doesn't return the sysdatabase names to the ASP.NET webpage when executing the stored procedure. It returns: System.Data.DataRowViewWhy does it do this and how can I fix it?Tx
I'm trying to create a proc for granting permission for developer, but I tried many times, still couldn't get successful, someone can help me? The original statement is:
At some point in time, when I release my code from developemnt to production, somebody will onvoke the SQL Script containg my certificates and symettric keys based on my master key. Unfortunately this seems a bit of a weekness as my SQL SCRIPT contains the CREATE MASTER KEY ENCRYPTION BY PASSWORD stement which has the password itself in clear. (script gets invoked from a command line in a batch script which is all under documeny mangement control). Obviously I would not like my password to be be in clear anywhere - i.e. not in document control nor viewable from whoever invokes the script. What is best pracrice to adopt on this? - encrypt the script file?
Is there any way I can create a login for a database while the database context is master or not the particular database. I am trying to do something like this....... USE [master] GO
DECLARE @STRSQL VARCHAR(2000), @DATABASE_NAME VARCHAR(50), @DB_PATH VARCHAR(200), @DB_USER VARCHAR(25) SET @DATABASE_NAME='TDB4x' SET @DB_PATH='D:Database' SET @DB_USER='TDB4Test'
SET @SQL_DB='USE '+@DATABASE_NAME SET @STRSQL1=(' IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name='''+@DB_USER+''')') SET @STRSQL2=(' BEGIN CREATE LOGIN '+@DB_USER+' WITH PASSWORD=N'''+@DB_USER+''', DEFAULT_DATABASE=['+@DATABASE_NAME+'], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF END') SET @STRSQL3=(' EXEC sys.sp_addsrvrolemember @loginame = N'''+@DB_USER+''', @rolename = N''sysadmin''') SET @STRSQL4=(' ALTER LOGIN ['+@DB_USER+'] DISABLE') EXEC (@SQL_DB+@STRSQL1+@STRSQL2+@STRSQL3+@STRSQL4)
The login is successfully created, but I cannot find the user in the specific database. Is there any other way this can be accomplished?
I had given one of our developers create view permissions, but he wants to also modify views that are not owned by him, they are owned by dbo.
I ran a profiler trace and determined that when he tries to modify a view using query designer in SQLem or right clicks in SQLem on the view and goes to properties, it is performing a ALTER VIEW. It does the same for dbo in a trace (an ALTER View). He gets a call failed and a permission error that he doesn't have create view permissions, object is owned by dbo, using both methods.
If it is doing an alter view how can I set permissions for that and why does it give a create view error when its really doing an alter view? Very confusing.
I have around 10 packages for dim and fact Table load. Using deployment Utility i create setup and put the packages in to test environment. Now my requirement is call all the packages in certain order from single package. Using Execute Package Task i could call only one package. how to achive this in deployment?
I am using ASP.NET and a normal ODBC stringconnectionDriver={SQL Server};Server=(local);MyBase;Uid=;Pwd=;Trusted_Co nnection=;when trying to CREATE a TABLE (with vb.net code) I get an error because the TABLE are written in Master !! and not in MyBasei am using windows authenticationwhat can be wrong ?thank you
I need some clarification regarding the security inside of the master db.
I have a user stored procedure in master. I would like to be able to execute that stored procedure from an internal web app. Would I give execute permission on the stored procedure to the "public" or "guest" role? The web app would be using a userid/pw for an application database.
Also, is it a good idea to have user stored procedures inside of master? Could someone point me to where I can find a good article on master db best practices?
I am using content and master pages. The content page has to query the database to get the master page name (among other things), which is done in ‘Page_PreInit’, and then the master page has to query the database to get some layout options, done in ‘Page_Load’.
Is it possible for the master page to use the existing open connection, or am I forced to close the connection in the content page and open up a new connection in the master page? I’ve tried various things without success. The second question is, does it matter? Will opening and closing two connections be much slower than opening and closing one?
I have a login associated with a single user on a database (not master). I want that user to be able to only see what I've specified in the securables.
Now I've created a ODBC connection using that login. The problem is that the user can also see the master db info. I was expecting to see just the one view I created and granted the user to view. How do I get rid of all the master db stuff?
I am struggling trying to clean some data and identify duplicate records. I used fuzzy grouping in SSIS and provided back a series of groups. The issue is some of the individual records can appear in multiple groups (so in reality the groups should be combined). This is best explained with an example:
Original Data key1 key2 647942600014 647942285437 2324662490640 2324662285437 2324662066128 2222 2285437 2222 1111111 9999 1111111 9999 2222222
I only choose 2222 as the surviving key because it was the smallest number. I really do not care which number remains as long as it is the same across.
I tried playing with self joins between the tables but have had no success.
I am using Sql Server 2008 and the number of records could 500K to 1MM.
