I have around 600 databases in my server, a user need select access of all the databases. will i have to go one by one in all the dbs and create that user and give datareader role to him. or is thr any shorter way to do so????
View 8 RepliesIn 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?
The requirements are:
1. the user has read-only permissions to dbo tales.
2. the user can do everything within the rpt schema, which contains all objects analyzing dbo tables.
3. the user does not have any permission outside rpt schema, except permissions in #1.
The current solutions are:
1. grant the user select only on dbo tables.
2. make the user the owner of rpt schema.
3. Grant the user database permission on create table/create procedure/create view/create function.
My question is - in step 3, should I just grant "Alter" database permission to the user? Granting Alter seems to be cleaner and simpler. According to MSDN,
"Alter" confers the ability to change the properties, except ownership, of a particular securable. When granted on a scope, ALTER also bestows the ability to alter, create, or drop any securable that is contained within that scope.
How do I grant a specific access to a schema. I have a request to grant a ddl access to a schema called Business_Banking.
View 1 Replies View RelatedMy sa account can not see the tables in the db. but can connect to the server
View 2 Replies View RelatedI have 3 servers taking part in an AlwaysOn AG.
I want to grant a user READ access to only one replica, and certainly not to the principle.
How do I go about doing this?
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
Hello together,
can anybody help me. I'm looking for an easy way to grant permissions to a user in all user databases. I already have a script which grants permission to all views and userdefined tables within one database, but since I have to run it in about 100 databases it's still quite timeconsuming.
Is there a way to execute that script in all user databases at once ???
Markus
I have some questions :
1.Is it OK to shrink Master, model ? the transaction log for those databases are full .
2.Is it OK to set the Recovery model of Database MODEL as SIMPLE ? at the moment it is in FULL recovery model and the transaction log is Full too
Script to find a value in all databases ? for example I want to find a value contain word “ SQL” in all databases so I will get the information in which database and which table it exists.
My script is only for finding a value in one database.
I have log shipping enabled on databases(primary and secondary) and works fine. I need to implement TDE on the database. I have experience on implementing TDE on databases which are not used for log-shipping.
What are the steps needed to setup TDE which are involved with log-shipping.
I would like to migrate around 15 databases in production server to the new production server. The biggest database is 80 GB .
Wondering is there any fastest way to do that with very low downtime ?
What I can think about is shrink databases files and perform attach – detach .
What are the potential risks of keeping data , log , tempdb backups etc in the same drive although in different folders ?
View 4 Replies View RelatedI have created a table with ..
username, password, grant
username and password are nvarchar
grant is a bit
I want to check the availability of the username and password and then check if it is granted to access or not ..
so I made a selection query for getting the grant when username ==x and password = y
x=data come from another page
y=data come from another page
then put the selected grant or deny ( true or false) in a data set ..
and then check if it is grant ( true) to complete what I want >>>
else ( deny ....( false )) to return a reponse of deny username or password...
but everytime the dataset is empty ..
this is my code ...
sqlConnection1.Open();
SqlDataAdapter da=new SqlDataAdapter("select grant_deny from users where username='"+Request.QueryString["username"]+"' and password='"+Request.QueryString["password"]+"'",sqlConnection1);
da.Fill(ds,"users");
sqlConnection1.Close()
if (ds.Tables[0].Rows[0]["grant_deny"].ToString()=="true")
{
.
.
.
.
}
else
{
Response.write("error");
}
what is wrong ..??
and if there is anyone has another solution for what I want .. I appriciate any sample code,...
thanks in advance..
Find unused databases in a instance or when last used or accessed?
I'm on SQL SERVER 2008 R2 64bit -enterprize
I need to find when the databse is last accessed.
I have a new problem with doing a restore of a number of databases using powershell. The script I'm using is based mainly on this one (Part 2 in particular): [URL] .....
The problem I'm having is around the RedgateGetDatabaseName function. My hunch is that its down to the different version of red gate and how sqlbackup works. Basically when the call is made to the function it is returning both the Database Name and the number of row's that the SQL command in the function has ran. I've tried to include a SET NOCOUNT ON at the start of the SQL command in the function but its still returning the now count.
Currently our database size is around 350G. It will grow up to 1.5 TB
We have the
Auto create statistics option :True,
auto update statistics option :True,
auto update statistics asynchronously option : False
at database level
we have a weekly job, update statistics running very long time. It is created through maintenance plan using the option full scan.
Previously they tested with sampling but instead of full scan running with the sampling effected the queries.
Is there option to avoid the long time job duration.
If we didn't run the statistics manually what will happen? How do you maintain statistics with large databases
I need to generate data models of all databases for an instance. How can I accomplish this?
View 3 Replies View RelatedI have 2 databases: one for our intranet & another for our internet website.In the intranet database, we have a table called "Clients". A new client can be added in one of 2 ways:
1) One of our employees manually adds the client via the intranet
2) A new customer subscribes to our services via our website, thus becoming a new "client" (when subscribing online, we also add a record to an "Accounts" table located in our internet website database, but we'd also like to add a record to our intranet's Clients table as well).
In the client table is a field called "CreatedBy" which expects the UserId of whoever created the account. Again, this UserId can belong to either an employee via the intranet or a new customer via our internet website. how do I distinguish where the UserId in the dbo.Clients.CreatedBy field is coming from?
My 1st thought was to append a negative to the CreatedBy UserId# if it came from a customer via the website, but that just seemed too quirky (i.e. if an employee added a client and his UserId was '10', the Clients.CreatedBy would be '10'. If a customer subscribed via the website, the Clients.CreatedBy would be '-10'. Where customers can once again sign up online. So, I need a way to keep these databases separate but keep track of which db a user's coming from when we create a new Client record.
I am working on a task. Currently we are taking a database backup and keeping that backups in a folder. The backups doesn't have time stamp on it. My task is need to get the latest backup and copy that backups into some other server and then restore the database from there.I am planning to create SSIS package.Do we need script task for this task.How to get the .bak with latest create or moidified date. For now we doesn't have timestamp so need to go based on modified date?
View 9 Replies View RelatedWe have two databases with same schema and tables (same table names, basically main DB and a copy of the main DB). following is example of table names from 2 DBs.
CREATE TABLE #SourceDatabase (SourceColumn1 VARCHAR(50))
INSERT INTO #SourceDatabase VALUES('TABLE1') , ('TABLE2'),('TABLE3') , ('TABLE4'),('TABLE5') , ('TABLE6')
SELECT * FROM #SourceDatabase
DROP TABLE #SourceDatabase
CREATE TABLE #ArchiveDatabase (SourceColumn2 VARCHAR(50))
INSERT INTO #ArchiveDatabase VALUES('TABLE1') , ('TABLE2'),('TABLE3') , ('TABLE4'),('TABLE5') , ('TABLE6')
SELECT * FROM #ArchiveDatabase
DROP TABLE #ArchiveDatabase
We need a T_SQL statement that can create one view for each table from both the databases(assuming both databases have same number of tables and same table names). so that we can run the T_SQL on a thrid database and the third DB has all the views (one view for each table from the 2 DBs). and the name of the view should be same as the tables name. and all 3 DBs are on the same server.
the 2 temp tables are just examples, DBs have around 1700 tables each. so we ned something like following for each table.
CREATE VIEW DBO.TABLE1 AS SELECT * FROM [SourceDatabase].[dbo].[TABLE1] UNION ALL SELECT * FROM [ArchiveDatabase].[dbo].[TABLE1]
CREATE VIEW DBO.TABLE2 AS SELECT * FROM [SourceDatabase].[dbo].[TABLE2] UNION ALL SELECT * FROM [ArchiveDatabase].[dbo].[TABLE2]
CREATE VIEW DBO.TABLE3 AS SELECT * FROM [SourceDatabase].[dbo].[TABLE3] UNION ALL SELECT * FROM [ArchiveDatabase].[dbo].[TABLE3]
CREATE VIEW DBO.TABLE4 AS SELECT * FROM [SourceDatabase].[dbo].[TABLE4] UNION ALL SELECT * FROM [ArchiveDatabase].[dbo].[TABLE4]
CREATE VIEW DBO.TABLE5 AS SELECT * FROM [SourceDatabase].[dbo].[TABLE5] UNION ALL SELECT * FROM [ArchiveDatabase].[dbo].[TABLE5]
CREATE VIEW DBO.TABLE6 AS SELECT * FROM [SourceDatabase].[dbo].[TABLE6] UNION ALL SELECT * FROM [ArchiveDatabase].[dbo].[TABLE6]
im trying to measure the amount of CPU used in a backup and restore, using the SQL Server Databases: Backup Restore counter in Perfmon.
I can select The counter, but it does not let me select an instance database to monitor.
see image:
I have 3 instances on my machine, 2 sql 2005 and 1 sql 2008. why do these instances not appear? i dont know how to set up this monitoring.
if i type LOCALHOST or 127.0.0.1 in the "Select counters from computer" text box i can see all databases under my sql 2005 instances, but not the ones under the 2008 instances. I have disabled all sql server 2005 services and can confirm none of the sql 2008 instance databases are showing up.
this is a huge problem, especially for when i start stress testing sql server 2008 in preperation for upgrading.
anyone any ideas on why i cannot see sql server 2008 instances in perfmon? I have tried from a remote pc also with the same results.
hello all,
I would like to grant the MACHINENAMEASPNET user all acccess to my MSSQL database (i.e tables and stored procedures. I can do this through enterprise manager but id rather do it programatically so i can add it to my database creation script. What is the syntax for this?
I thought it would be along the lines of:
Grant all on databasename to localhostASPNET.
But obviously its not! Please help!
thanks
Tom
Hi Listers,
Can anyone help me on users Access rights. The problem is I have created users called finance and sales. Sales have default database called Salesdb. Similarly finance have default database called Financedb.
The user sales created a table called 'daily_report' in salesdb and he is also belongs to the database role of db_ddladmin. The user finance doesn't have any access rights to this salesdb database and he didnot belong to any database roles in financedb itself. But I don't know, this finance user he is able access all the rights of 'daily_rep' table of salesdb database. Even the user sales has revoked all the access rights from the finance user, he still ables to modify/insert/select/delete the records of 'daily_rep' table of Salesdb database.
Can anyone help me please.
tks in advance,
Sam
i am working on a project using asp.net and microsoft sql server, i am now working on my home pc. so now the problem is whenever i try to access the database through asp a error message such as :
[OleDbException (0x80040e4d): Login failed for user 'JYHENGASPNET'.]
System.Data.OleDb.OleDbConnection.ProcessResults(I nt32 hr) +20
System.Data.OleDb.OleDbConnection.InitializeProvid er() +57
System.Data.OleDb.OleDbConnection.Open() +203
JYHeng.USRRegistration.Page_Load(Object sender, EventArgs e) in C:Documents and SettingsHeng Jang Yang.JYHENGFYPFYPCacheJYHENGJYHengUSRRegistra tion.aspx.vb:63
System.Web.UI.Control.OnLoad(EventArgs e) +67
System.Web.UI.Control.LoadRecursive() +35
System.Web.UI.Page.ProcessRequestMain() +731
will appear.
my connectionstring used is :
objCn.ConnectionString = "Provider=SQLOLEDB; Data Source=(local);Initial Catalog=fyp;Integrated Security=SSPI;"
i am told that i must check if my aspnet user have been granted access to the database. however i am not sure on how to check the access, not to mention granting accsss. How am i supposed to grant my aspnet user access to my sql database?
Can someone please help me ? i am really lost. thank you
I am setting up report services after I installed the report server add-ins
on sharepoint 2007.
at first step, Reporting services integration, I put
report server web service URL -- http://ssrsdev1/reportserver
authentication mode -- windows authentication
it went OK.
at second step, Grant Database Access,
I put ssrsdev1 for server name (default instance) and then click OK and then
I put the user name (a member of local admin group on ssrsdev1) and password,
but I always got errors says:
Unable to log on with the given username and password.
what wrong here? what should I check? I am sure the user and password is
right and I can log on to ssrsdev1 physically with admin right without any
problem.
thanks for any thought on this.
zwp
Is there a query or sp that I can pull all databases on the server along with their logical file names and physical file names?
View 5 Replies View RelatedI was contacted by the SAN team to test backup/restore of larger databases using a split-mirror backup (BCV) or clone that is taken from production db server and copied to another sql box. They want to use this process once a week. I see the mounted drives with the data/log files. All looks good. Initially I attempted to attach the databases and received (Unable to open the physical file db.mdf Operating System Error 5 Access is denied). I manually granting SQLServerMSSQLUser$<computer_name>$<instance_name> on all of the physical files 20 total. That worked.
Since this will be weekly, the SAN team performed the copy again and now none of the databases can communicate with the newly copied files. NTFS permissions need to be set again. I'm getting (Operating System error 21: the device is not ready). Is there something that I'm missing in this process how the vendor BCV clones the data and SQL communicates with the copied files as I was thinking it would be more automated process?
Is there an easy way to compare the contents of objects between 2 different databases? For example, say I had 2 databases, My_DB_1 and My_DB_2, each with a SEC_User table. Say I wanted to do an object-to-object comparison between databases to see if there were any differences. Here's some sample SQL:
use My_DB_1
select * from sys.dm_sql_referencing_entities('dbo.sec_user', 'object')
use My_DB_2
select * from sys.dm_sql_referencing_entities('dbo.sec_user', 'object')
Say that the result sets above both returned a SEC_GetUser sproc object ref. Is there a way to write SQL that will compare the SEC_GetUser sprocs (and other objects in the above rowsets) from both databases? For example, if My_DB_1.SEC_GetUser returns an extra column in the result set than My_DB_2.SEC_GetUser then I'd like my comparison SQL to return a single column "IsDifferent" with SEC_GetUser as a row....
So here's the situation: I'm creating a front-end application to an SQL Server 2000 database and I need to create a new login to the database for the app.
Is it possible (or even; is it a good idea...) to create a logon that only has access to a single table?
Actually, I know it's possible - but I don't want to have to go through the hundreds of tables and deny access to them all! So I guess the question is;
Is there a quick and easy way of granting a login permissions to a single table only?
I need an example of how to grant access to a SQL user to a DB. For the life of me I can't seem to get my syntax correct. My database name is TEST and my username is LEMME_IN and I want to grant the user "Public" access to the db with db_datareader, db_datawriter database role membership.
Thanks
Hi!I have a user on my database that has only "select" access(db_datareader).Problem is, I also want him to also be able to create/update extendedproperties on tables or views, but without modifying the tables'schema.I played around with GRANT but apparently, a member of "db_datareader"cannot create/modify extended properties on an object if he's not theowner of this object. I tried making this user a member of"db_datawriter", but it didn't work.Nothing short of making him member of "db_ddladmin" worked... but thenthis is too much, the user can now alter to delete tables: i DON'T wantthat!Any ideas anyone? Cheers!Ben
View 3 Replies View RelatedI am build an application that able to update insert delete an entries from my sql server, i create a new account in sql server management studio express so my app can connect to the database thru SQL Server Aurthentication. Delete & update method work well but my insert dont.
I change to the connection string to localhost ( WindowsAurthentication) and it works so i conclude that the problem lies on the sql user access. However I had already ticks everything on object explorer > security > logins > my user properties > Server Roles & User Mapping. What should i do inorder to grant insert/create database access to my sql user? Thanks.